Re: PL/SQL best practices ? How to go to end of loop (i.e. next record)
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: michaeljmoore@xxxxxxxxx
- Date: Thu, 10 Feb 2011 00:05:09 +0100
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 <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
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:
