Re: PL/SQL best practices ? How to go to end of loop (i.e. next record)
- From: Kerry Osborne <kerry.osborne@xxxxxxxxxxx>
- To: sfaroult@xxxxxxxxxxxx
- Date: Wed, 9 Feb 2011 17:05:23 -0600
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:
