Return in anonymous PL/SQL block

From: Rolf Unger <rolf.unger_at_ctilabs.de>
Date: 12 Mar 2004 06:20:16 -0800
Message-ID: <32fe19ad.0403120620.8e65ab1_at_posting.google.com>

Hi,

[Quoted] [Quoted] 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 ...

Thanks, Rolf. Received on Fri Mar 12 2004 - 15:20:16 CET