Oracle Scratchpad

October 24, 2022

PL/SQL Labels

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 9:06 am BST Oct 24,2022

A tweet from Franck Pachot [July 2021] about fully qualified names in Postgres prompted me to highlight a note I wrote a few years ago about using the label mechanism in Oracle’s PL/SQL to avoid collisions between variable names and table names. This led to a brief twitter exchange about labels and goto, an associated feature that I had completely forgotten about until I read a very sketchy comment in the scripts I’d used to demonstrate the use of labels to fully qualify variable names.

The comment – which I hadn’t included in the published note – was as follows:

rem     Using labels as targets
rem             goto label_name
rem                     unconditional jump
rem             exit [label_name] when {condition}
rem                     exit to line after labelled loop
rem             continue [label_name] when {condition}
rem                     start next iteration of labelled loop

Having just rediscovered the original blog note and associated script I found that I’d written up a demo of using the three code control mechanisms at the time of the discussion but not got around to publishing it, so here it is now:

rem
rem     Script:         plsql_block_names_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Aug 2021
rem     Purpose:        
rem
rem     Last tested 
rem             19.11.0.0
rem

create or replace procedure demo_labels(p_control number)
as
begin
        if demo_labels.p_control = 0 then
                goto early_exit;
        end if;

        <<outer_loop>>
        for i in 1..10 loop
                dbms_output.put_line('Starting Inner');
                <<inner_loop>>
                for i in 1..4 loop
                        exit inner_loop when outer_loop.i > 3;
                        dbms_output.put_line(inner_loop.i);
                        continue inner_loop when demo_labels.p_control = 1;
                        dbms_output.put_line('Control != 1');
                        continue outer_loop when demo_labels.p_control = 2;
                end loop inner_loop;
                dbms_output.put_line('Ended inner');
        end loop outer_loop;

        <<normal_exit>>
        dbms_output.put_line('===========');
        dbms_output.put_line('Normal Exit');
        dbms_output.put_line('===========');
        goto terminate;

        <<early_exit>>
        dbms_output.put_line('==========');
        dbms_output.put_line('Early Exit');
        dbms_output.put_line('==========');

        <<Terminate>>
        null;

end;
/

I’ve created a procedure called demo_labels, and you can see at line 14 an example of qualifying a variable name with the name of the procedure. In fact I’ve used this example to show that you can (and should) qualify the names of the formal parameters to the procedure.

Inside this procedure I’ve created labels for the two loops, <<outer_loop>> and <<inner_loop>> and, again, you can see cases (lines 23 and 24) where I’ve used the loop names to qualify the names of variables declared for the loop. You’ll notice that I (deliberately) used the same index variable name for both loops – this type of thing is usually an error waiting to happen but, by qualifying the variables at every use, I’ve pre-empted the possible “capture” problem of one use of the variable name hiding another use of the same name.

I’ve also shown the use of an exit inner_loop, and a continue with both inner_loop and outer_loop; and I’ve also used the loop names to identify clearly which loop is ending on an end loop.

Finally I’ve created three further labels as potential targets for transferring execution, of which I’ve only used early_exit and terminate.

Here’s a little script I’ve then run to show the effects – you might like to work out what’s going to happen before scrolling down to the comments and output:

set feedback off
set serveroutput on

prompt  ==================== 0 ==================== 
execute demo_labels(0)

prompt  ==================== 1 ==================== 
execute demo_labels(1)

prompt  ==================== 2 ==================== 
execute demo_labels(2)

With zero as the input the procedure immediately jumps to the early_exit label, runs through the next 4 commands and returns.

==================== 0 ====================
==========
Early Exit
==========

With 1 as the input we start the outer loop, then start the inner loop and print the inner loop counter for the first time, but because we meet the requirements of the continue inner_loop at line 25 we drop to the end of the inner loop and go round again (you may prefer to think of this as going back to the top of the loop, I just happen to find it more natural to think of ending the cycle and starting a new one) for a total of 4 times, then print “Ended Inner” and go round the outer loop a second and third time doing exactly the same thing.

When we go round the outer loop for the 4th and subsequent cycles we will immediately exit inner_loop (line 23), which means we print “Ended Inner” and go round the outer again. Finally we’ll complete 10 cycles of the outer loop, work through the normal_exit and goto terminate.

==================== 1 ====================
Starting Inner
1
2
3
4
Ended inner
Starting Inner
1
2
3
4
Ended inner
Starting Inner
1
2
3
4
Ended inner
Starting Inner
Ended inner
Starting Inner
Ended inner
Starting Inner
Ended inner
Starting Inner
Ended inner
Starting Inner
Ended inner
Starting Inner
Ended inner
Starting Inner
Ended inner
===========
Normal Exit
===========

With 2 as the input we start the outer loop, then start the inner loop and print “Starting Inner”, then print “1”, but at line 25 we don’t jump to the end of the inner loop, we fall through to the line 26, print “Control != 1”, then continue to the end of the outer loop, and cycle round again. Repeating the three lines of output 3 times, then (as with input 1) line 23 makes us jump to the end of the inner loop for the next 7 cycles of the outer loop before we pass through the normal exit.

==================== 2 ====================
Starting Inner
1
Control != 1
Starting Inner
1
Control != 1
Starting Inner
1
Control != 1
Starting Inner
Ended inner
Starting Inner
Ended inner
Starting Inner
Ended inner
Starting Inner
Ended inner
Starting Inner
Ended inner
Starting Inner
Ended inner
Starting Inner
Ended inner
===========
Normal Exit
===========

Playing around with other input values is left as an exercise.

1 Comment »

  1. […] PL/SQL Labels (Oct 2022): Using labels as targets for goto, exit, and continue […]

    Pingback by Troubleshooting catalogue | Oracle Scratchpad — October 24, 2022 @ 9:18 am BST Oct 24,2022 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

%d bloggers like this: