Return in anonymous PL/SQL block

[Rolf Unger]

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@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@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 ...

Thanks, Rolf.

[Volker Hetzer]

"Rolf Unger" <rolf.unger@ctilabs.de> schrieb im Newsbeitrag news:32fe19ad.0403120620.8e65ab1@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@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@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.

[Rolf Unger]

"Volker Hetzer" <volker.hetzer@ieee.org> wrote in message news:<c2sjnp$ful$1@nntp.fujitsu-siemens.com>... "Rolf Unger" <rolf.unger@ctilabs.de> schrieb im Newsbeitrag news:32fe19ad.0403120620.8e65ab1@posting.google.com... Hi, i'm dealing with the problem that i want to stop processing in an anonymous PL/SQL block. [ ... more lines "folded" ...] 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.

Yes, but in C you are always inside a function. So there always is
a clear context (and stack!) to which the "return" relates too.
And as I said I'm absolutely d'accord with the behaviour of "return"
inside of PL/SQL functions.

I still think that a return that is not surrounded by a function or a
procedure is not intuitive.

An anonymous block is compiled and immediately executed.
A function or a procedure are compiled and only executed if they
are referenced or called in some other context.

Maybe this discussion is just hypothetical and I have to change my
coding guidelines and follow the simple rule:

"Never put more than five lines of code in an anonymous block.
If you have more code, make it a function."

Then in 99 percent of the cases any "return" will be inside a
function, and my C-aligned conscience will shut up ;-)

Rolf.