Re: what is the SQL equivalent of 'continue' and 'break' in C ?

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 10 Aug 2006 08:52:58 -0700
Message-ID: <1155225179.357744@bubbleator.drizzle.com>

happyardy_at_gmail.com wrote:

> DA Morgan wrote:
>> happyardy_at_gmail.com wrote:

>>> Scott wrote:

>>>> happyardy_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/goto_statement.htm#LNPLS01323
>>
>> 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
>> damorgan_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;

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Thu Aug 10 2006 - 10:52:58 CDT