Solved

Oracle procedure enhancement

Posted on 2014-04-07
9
790 Views
Last Modified: 2014-04-11
Im looking to rewrite this procedure. If anyone has a btter way of me doing this Id appreciate it...
Thanks

CREATE OR REPLACE PROCEDURE ANET.SP_OMNI_ENT_COLUMN_REALIGN
(iPAGEID IN  NUMBER)

IS
BEGIN
    DECLARE
        icolPREV NUMBER;
        icolNEXT NUMBER;
   
    cursor cur is
        select section_ordinal, section_table_cell
        from omni_ent_page_content where page_id=iPAGEID
        order by section_ordinal asc, section_table_cell asc;
     
    BEGIN    
        icolPREV := 0;  
     FOR rec in cur
        LOOP
                Case When rec.section_table_cell = 0 then
                    Case When icolPREV = 0 then
                        NULL; --donothing
                    when icolPrev = 1 then
                        UPDATE omni_ent_page_content set section_table_cell = 0 where page_id=iPAGEID and section_ordinal=rec.section_ordinal-1;
                    when icolPREV = 2 then
                        NULL; --donothing
                    when icolPREV = 3 then
                        NULL; --donothing
                    end case;
                When rec.section_table_cell = 1 then
                    Case When icolPREV = 0 then
                        NULL; --donothing
                    when icolPrev = 1 then
                        SELECT section_table_cell into icolNEXT from omni_ent_page_content where page_id=iPAGEID and section_ordinal = rec.section_ordinal + 1;
                        if icolNEXT is NULL then
                            NULL; --donothing
                        else
                            NULL; --case when icolNEXT = 0 then
                               
                        end if;
                       
                    when icolPREV = 2 then
                        NULL; --donothing
                    when icolPREV = 3 then
                        NULL; --donothing
                    end case;
                When rec.section_table_cell = 2 then
                    Case When icolPREV = 0 then
                        NULL; --donothing
                    when icolPrev = 1 then
                        UPDATE omni_ent_page_content set section_table_cell = 0 where page_id=iPAGEID and section_ordinal=rec.section_ordinal-1;
                    when icolPREV = 2 then
                        NULL; --donothing
                    when icolPREV = 3 then
                        NULL; --donothing
                    end case;
                When rec.section_table_cell = 3 then
                    Case When icolPREV = 0 then
                        NULL; --donothing
                    when icolPrev = 1 then
                        UPDATE omni_ent_page_content set section_table_cell = 0 where page_id=iPAGEID and section_ordinal=rec.section_ordinal-1;
                    when icolPREV = 2 then
                        NULL; --donothing
                    when icolPREV = 3 then
                        NULL; --donothing
                    end case;
                End case;
        END LOOP;
    END;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;


END SP_OMNI_ENT_COLUMN_REALIGN;
/
0
Comment
Question by:jknj72
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 39983370
How many times are you calling this procedure OR How may rows are you going to run above procedure for?
0
 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 167 total points
ID: 39983478
i dont see how icolprev and icolnext are getting values, more over you are doing nothing in most of the cases, so if you combine those case conditions you can get rid of nested case statements



CREATE PROCEDURE sp_omni_ent_column_realign (ipageid IN NUMBER)
IS
   icolprev   NUMBER;
   icolnext   NUMBER;

   CURSOR cur
   IS
      SELECT   section_ordinal, section_table_cell
          FROM omni_ent_page_content
         WHERE page_id = ipageid
      ORDER BY section_ordinal ASC, section_table_cell ASC;
BEGIN
   BEGIN
      icolprev := 0;

      FOR rec IN cur
      LOOP
         CASE
            WHEN rec.section_table_cell = 0 AND icolprev = 1
            THEN
               UPDATE omni_ent_page_content
                  SET section_table_cell = 0
                WHERE page_id = ipageid
                  AND section_ordinal = rec.section_ordinal - 1;
            WHEN rec.section_table_cell = 1 AND icolprev = 1
            THEN
               SELECT section_table_cell
                 INTO icolnext
                 FROM omni_ent_page_content
                WHERE page_id = ipageid
                  AND section_ordinal = rec.section_ordinal + 1;
            WHEN rec.section_table_cell = 2 AND icolprev = 1
            THEN
               UPDATE omni_ent_page_content
                  SET section_table_cell = 0
                WHERE page_id = ipageid
                  AND section_ordinal = rec.section_ordinal - 1;
            WHEN rec.section_table_cell = 3 AND icolprev = 1
            THEN
               UPDATE omni_ent_page_content
                  SET section_table_cell = 0
                WHERE page_id = ipageid
                  AND section_ordinal = rec.section_ordinal - 1;
         END CASE;
      END LOOP;
   END;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RAISE;
END sp_omni_ent_column_realign;
/
0
 
LVL 23

Assisted Solution

by:paquicuba
paquicuba earned 166 total points
ID: 39983548
I agree, try to remove as many nested CASE statements as possible. Normally, the best way to tune your code is to change the FOR LOOP to a FORALL, but in your case, I don't think is possible, so you'll have to stay with the row by row approach.

Something like this:

CURSOR cur
   IS
      SELECT   section_ordinal, section_table_cell
          FROM omni_ent_page_content
         WHERE page_id = ipageid
      ORDER BY section_ordinal ASC, section_table_cell ASC;
type nn is table of cur%rowtype;
l_nn nn;

BEGIN
   BEGIN
      icolprev := 0;

fetch cur bulk collect into l_nn;
forall i in l_nn.first..l_nn.last

    UPDATE omni_ent_page_content
                  SET section_table_cell = 0
                WHERE page_id = ipageid
                  AND section_ordinal = l_nn(i).section_ordinal - 1;
0
 Watch the Recording: Learning MySQL 5.7

MySQL 5.7 has a lot of new features. If you've dabbled with an older version of MySQL, it is definitely worth learning.

 

Author Comment

by:jknj72
ID: 39983657
Well I just found out that the proc is not complete(pretty obvious). The purpose of the proc is to set the section_ordinal position so when the app loads it will know the position to put the 'objects'. This is called when these objects are moved to the right or to the left on the page. so if we had 3 objects(image, links, etc...) on a page on the same row and I clicked to move the 2nd object to the left which means now it would be the 1st object and the 1st object becomes the 2nd object in the row of objects...
Thats the objective...
I hope it makes sense. Let me know if you have any questions.
Thanks all!!!

FYI, just to explain a little more...The app is letting the users create their own web pages. They can add objects(images, links, paragraphs, etc...) and we save the position of these objects so we know how to load the page with the positions where they were saved.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 167 total points
ID: 39983906
Any reason you left the MERGE examples from your previous questions:
http://www.experts-exchange.com/Database/Oracle/Q_27869212.html
http://www.experts-exchange.com/Database/Oracle/Q_27865679.html

I still think this can all be done in a single statement.

As I asked for in those questions and never received:
Need some sample data and expected results or some examples where the code no longer works and an explanation of why.
0
 

Author Comment

by:jknj72
ID: 39983993
I actually did use the Merge statement for a similar procedure and will see what I can do.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39984443
If you can dummy up some sample data and expected results, we can probably help.
0
 

Author Comment

by:jknj72
ID: 39994109
splitting points..... thanks for the help
0
 

Author Closing Comment

by:jknj72
ID: 39994110
thanks for the help
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question