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

From: Kerry Osborne <kerry.osborne_at_enkitec.com>
Date: Wed, 9 Feb 2011 17:05:23 -0600
Message-Id: <F7E769E0-2FA9-4172-B212-058B31724521_at_enkitec.com>

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

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

Received on Wed Feb 09 2011 - 17:05:23 CST