Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: what is the SQL equivalent of 'continue' and 'break' in C ?
On Aug 29, 10:33 pm, web..._at_polaris.net (Ubiquitous) wrote:
> damor..._at_psoug.org wrote:
> >happya..._at_gmail.com wrote:
> >> DA Morgan wrote:
> >>> happya..._at_gmail.com wrote:
> >>>> Scott wrote:
> >>>>> happya..._at_gmail.com wrote:
> >>>>>> what is the SQL equivalent of 'continue' and 'break' in C ?
>
> >>>>>> like can I do this...
>
> >>>>>> for counter in 1..10
> >>>>>> if(something something)
> >>>>>> ( if (something)
> >>>>>> ( if(something)
> >>>>>> then continue;
>
> >>>>>> //Rest of the for loop
>
> >>>>>> end loop;
>
> >>>>>> Would it start the next iteration without processing the rest of the
> >>>>>> loop ?
>
> >>>>>> thanks
> >>>>>> - Ardy
> >>>>> If I understand the question, something like this in PL/SQL
>
> >>>>> loop
>
> >>>>> loop
> >>>>> if something
> >>>>> then
> >>>>> exit; -- continue, i.e. go on with the rest of the main loop
> >>>>> end if;
>
> >>>>> end loop;
>
> >>>>> if something_else
> >>>>> then
> >>>>> exit; -- break, i.e. get out of the main loop
> >>>>> end if;
> >>>>> -- rest of the for loop
>
> >>>>> end loop;
> >>>> Scott,
> >>>> I am a little confused about if this works the way, 'continue' works in
> >>>> C language. I mean in the middle of a for loop if I have a "continue;"
> >>>> in C language, the control just goes to the top of the loop and starts
> >>>> a new iteration.
> >>>> In your code here, 'exit'' would break me out of the inner loop and the
> >>>> control would go to first statement(if any) that is outside the inner
> >>>> loop. I have a bunch of statements in my code after the inner loop.
> >>>> What I want to happen is that the control goes directly to the next
> >>>> iteration without trying to execute any of the remaining code outside
> >>>> of the inner loop..
> >>>> Your code would hold good if it is the last piece of code in my outer
> >>>> loop(for loop). That means it would be good if as soon as my inner loop
> >>>> ends, my outer loop ends too.
>
> >>>> Not saying that your code is wrong but your code will go down and
> >>>> execute my remaining statements after the inner loop though I dont want
> >>>> it to. If I wanted to do that then I wouldnt want a 'continue' like
> >>>> working, now would I ?
>
> >>>> Please let me know if I am not understanding correctly and have
> >>>> misinterpreted anything.
> >>>> thanks & regards
> >>>> - Rahul
> >>> If you want GOTO capability in Oracle use a label.
>
> http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14261/g...
>
>
>
>
>
> >>> I don't have any demos in the library that I can recall as I
> >>> find them rather inelegant.
> >>> --
> >>> Daniel A. Morgan
> >>> University of Washington
> >>> damor..._at_x.washington.edu
> >>> (replace x with u to respond)
> >>> Puget Sound Oracle Users Group
> >>>www.psoug.org
>
> >> Daniel,
> >> I try to stay away from GOTO. GOTOs are not evils and are helpful
> >> sometimes but still I dont like to use them. I avoid them as much as
> >> possible.
> >> I was trying to find out if SQL has any keyword that is equivalent of
> >> 'continue' in C. C has GOTO too and I have always wanted to avoid that.
> >> thanks
> >> - Ardy
>
> >In answer to your question ... no there isn't. But also in your
> >situation there is no functional difference between the two.
>
> >Another option would be this though I don't like it much either:
>
> >DECLARE
> > break EXCEPTION;
> >BEGIN
> > LOOP
> > BEGIN
> > <your code here>
> > RAISE break;
> > <the rest of your code here>
> > EXCEPTION
> > WHEN break THEN
> > NULL;
> > END;
> > END LOOP;
> >END;
>
> It's been decades since I used C, but isn't the original premise flawed?
> I mean, using a FOR loop structure and then short circuiting it is kinda silly to me.
Anyway in 11g we now have CONTINUEs in PL/SQL, so instead of this (or any of the other equivalents posted earlier):
BEGIN
FOR i IN 1..5 LOOP
IF DBMS_RANDOM.VALUE(0,10) > 5 THEN GOTO continuepoint; END IF; DBMS_OUTPUT.PUT_LINE('Processing here(' || i || ')...'); <<continuepoint>> NULL;
BEGIN
FOR i IN 1..5 LOOP
IF DBMS_RANDOM.VALUE(0,10) > 5 THEN CONTINUE; END IF; DBMS_OUTPUT.PUT_LINE('Processing here(' || i || ')...');END LOOP;