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

Don't confuse me with logic. I want to hear the best way to break out of the 
loop.

Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com






On Feb 9, 2011, at 5:05 PM, Stephane Faroult wrote:

> What about
> 
>     for cur1 in (SELECT blablabla where != something_I_dont_like)
>     loop
>       code
>    end loop
> 
> 
> And if your code contains several inserts, I would take a look at multi table 
> inserts.
> The fewer loops, the better.
> 
> Stephane Faroult
> RoughSea Ltd
> Konagora
> RoughSea Channel on Youtube
> 
> On 02/09/2011 10:45 PM, Michael Moore wrote:
>> 
>> 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

Other related posts: