RE: PL/SQL best practices ? How to go to end of loop (i.e. next record)

I would never use a goto - that's a very messy way to write code - imho
is all.

 

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Michael Moore
Sent: Wednesday, February 09, 2011 4:45 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: PL/SQL best practices ? How to go to end of loop (i.e. next
record)

 

I can think of many ways to do this but is there a definitive best
practice?

I'm sure there must be articles on this somewhere, so links as well as
comments are appreciated. 

 

BEGIN

for cur1 in (SELECT bla bla bla)

    LOOP

 

     If something_I_dont_like then skip to next record in cur1;

 

     Lots and lots of code goes here. 

 

    END  LOOP;

END;

 

Some options:

1) You could set up a label and use a goto.

2) You could set up an inner block and use RAISE to break out:

 

3) You could do something like :

 

BEGIN

for cur1 in (SELECT bla bla bla)

    LOOP

         <<process_this_record>>

         FOR c2 IN (SELECT * FROM DUAL) -- creates an exitable one-time
loop

         LOOP

 

            If something then 

                   exit process_this_record;

 

              Lots and lots of code goes here. 

 

         END LOOP process_this_record;

    END  LOOP;

END;

 

4) You could just nest IF statements like:

 

BEGIN

for cur1 in (SELECT bla bla bla)

    LOOP

         IF keep_processing_this_transaction THEN

         BEGIN

               Lots and lots of code goes here.

         END IF;

    END  LOOP;

END;

 

So, what is the best way?

 

Regards,

Mike




Confidentiality Note: This electronic message transmission is intended only for 
the person or entity to which it is addressed and may contain information that 
is privileged, confidential or otherwise protected from disclosure. If you have 
received this transmission, but are not the intended recipient, you are hereby 
notified that any disclosure, copying, distribution or use of the contents of 
this information is strictly prohibited. If you have received this e-mail in 
error, please contact Continuum Performance Systems at {203.245.5000} and 
delete and destroy the original message and all copies.

Other related posts: