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

From: Michael Moore <michaeljmoore_at_gmail.com>
Date: Wed, 9 Feb 2011 13:45:20 -0800
Message-ID: <AANLkTikkNRmJNSP780h+c1bDZqemek_L85aQZ0JEaes9_at_mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 09 2011 - 15:45:20 CST