Day 6

Implementing Loops
and GOTOs

by Timothy Atwood

Day 5, "Using Functions, IF Statements, and Loops," demonstrates ways to change the order of execution with PL/SQL. Today's lesson covers several additional methods of changing the order of execution. Today's material covers the following topics:

• Statement labels
• The GOTO statement
• The WHILE loop
• The simple LOOP
• Emulating a REPEAT...UNTIL loop

Labels and the GOTO Statement

The GOTO statement allows for unconditional branching to a statement label. You will first learn about statement labels, which are required before you can even use the GOTO statement.

Statement Labels

Statement labels are identifiers of a block of code, similar to a function, but they are not actual PL/SQL statements. These labels can be accessed directly by the GOTO statement. In addition, these labels can be accessed by loops, which are covered in the section "The EXIT and EXIT WHEN Statements." The format of the label is

<<label_name>>

You will notice two things immediately about the label:

• The label is surrounded by double brackets << >>.
• The label must not have a semicolon after the label name.

The label name does not contain a semicolon because it is not a PL/SQL statement, but rather an identifier of a block of PL/SQL code.

The GOTO Statement

The GOTO statement enables you to immediately transfer control to another labeled PL/SQL block without the need for conditional checking. As soon as the GOTO statement is encountered, all control is then transferred to the code underneath the matching label_name.

The Syntax for the GOTO Statement

GOTO label_name;

label_name is the matching label_name that must be contained within the same PL/SQL block of code.

Scoping Rules for the GOTO Statement

The GOTO destination must be in the same block, at the same level as or higher than the GOTO statement itself. This means that the label must be within the same scope as the GOTO statement itself. Conditions that would cause Oracle to not compile the PL/SQL code include

• Jumping into a lower level block
• Jumping into a loop
• Jumping into an IF statement
• Using GOTO to jump from one part of an IF statement to another
• Jumping from an exception handler back to a current block of PL/SQL code

The error message you'll encounter if you do not follow the proper coding of GOTO statements and their labels is

PLS-00375:  illegal GOTO statement; this GOTO cannot branch to label

Again, if you want a more global approach, stored functions would be one appropriate method.

Jumping into a Lower Level Block You can't jump from an outer block of PL/SQL code back to an inner block of PL/SQL code. Listing 6.1 is an example of an illegal GOTO call.

INPUT
Listing 6.1. Illegal GOTO call to an inner block.

 1: DECLARE
 2:      v_Emergency_Warning VARCHAR2(50);
 3:      v_Status NUMBER = 0;
 4: BEGIN
 5:      GOTO Emergency_Check;
 6:      BEGIN
 7:           <<Emergency_Check>>
 8:                IF v_Status = 1 THEN
 9:                    PANIC();
10:                END IF;
11:      END;
12: END;

In the code in Listing 6.1, you see the first block of PL/SQL code as noted by the BEGIN statement. The block then calls the PL/SQL GOTO statement, which then attempts to transfer control to the <<Emergency Check>> label. Because the label is within a separate block of
PL/SQL code as noted by the BEGIN statement, it is out of the required scope of the GOTO statement. If, instead of the keyword BEGIN, there was an <<Emergency_Check>> label within the first block, everything would have compiled and executed properly (barring errors in the logic of the code).

Jumping into a Loop The scope of the loop is not complete until the entire range of the loop has completed. Therefore, attempting to jump into the middle of the loop is illegal. Listing 6.2 shows an attempt to make an illegal call into a FOR loop.

INPUT
Listing 6.2. Illegal GOTO call to a loop.

1: BEGIN
2: GOTO insideloop;
3:      FOR v_loopcounter IN 1..5 LOOP
4:           <<insideloop>
5:           DBMS_OUTPUT.PUT_LINE(`Loop counter is ` || v_loopcounter);
6:      END LOOP;
7: END;

As you can see, although the loop and the GOTO statement are within the same block of
PL/SQL code, Oracle would not know how to handle the jump inside the loop. The obvious question would be: What is the value of the loop counter? Because there is no answer, any attempt to implement this logic would result in a compile error.

Jumping into an IF Statement
Another illegal attempt to use the GOTO statement would be to jump inside an IF statement. Listing 6.3 provides an example of another illegal call.