Re: Return in anonymous PL/SQL block

"Rolf Unger" <rolf.unger_at_ctilabs.de> schrieb im Newsbeitrag news:32fe19ad.0403120620.8e65ab1_at_posting.google.com...
> Hi,
>
> i'm dealing with the problem that i want to stop processing in
> an anonymous PL/SQL block.
> My first attempt was to define an exception with a "do-nothing"
> handler and to a raise this exception whenever I wanted to stop
> execution.
>
> DECLARE
> my_exception EXCEPTION;
> BEGIN
> ...
> IF ... THEN raise my exception; END IF;
> ...
> EXCEPTION
> WHEN my_exception THEN
> null;
> END;
>
> Then I started to think: "If this would be inside a function I
> would just issue a "return" at that point and get what I need.
> Return is probably working in a procedure, but would it also be
> working in an anonymous block?"
>
> I gave it a try and it seems to work, allthough I'm not sure
> why the ORACLE PL/SQL guys have done it like that.
>
> Here is what I've done in sqlplus:
>
> ,-------------------------------------
> | rolf_at_ORACLE> begin
> | dbms_output.put_line('Before return');
> | return;
> | dbms_output.put_line('after return');
> | end;
> | 2 3 4 5 6 /
> | Before return
> |
> | PL/SQL procedure successfully completed.
> '-------------------------------------
>
> Then I tried it in a nested block ....
>
> ,-------------------------------------
> | rolf_at_ORACLE> begin
> | dbms_output.put_line('Before inner block');
> | begin
> | dbms_output.put_line('Before return');
> | return;
> | dbms_output.put_line('after return');
> | end;
> | dbms_output.put_line('After inner block');
> | end;
> | 2 3 4 5 6 7 8 9 10 /
> | Before inner block
> | Before return
> |
> | PL/SQL procedure successfully completed.
> '-------------------------------------
>
> So it seems to bail out of all surrounding blocks.
> The same happens in a Trigger-block in Oracle-Forms.
>
> I have only experience with c-style programming languages as
> C, Java, Perl, etc. I have no clue about those that are similar
> to PL/SQL in there block oriented style like ADA.
>
> As in C and Java do not know a difference between functions and
> procedures, from my thinking I would have prohibited the usage of
> return in anonymous blocks.
>
> Hm, this was a quite long introduction to finally come to my
> question. As this behaviour of "return" seems strange to me,
> is it possible that it will change in future Versions of PL/SQL.
> I have the feeling that I use an undocumented feature/side effect
> if I execute a "return" to completely stop processing in all blocks.
> Maybe somebody around here with ADA experience can give me some
> input ...
Actually, C does the same thing:
int f(void)
{
int i;
i=i+1;
//whatever code

    {
    int j;
    return i;
    }
}
return returns from the function. Received on Fri Mar 12 2004 - 16:07:37 CET