7.6.1.1 Benefits of loop labels

The loop label is useful in two particular situations:

• When you have written a loop whose code length exceeds a single page, use a loop label to tie the end of the loop back explicitly to its start. This visual tag will make it easier for a developer to maintain and debug the program. Without the loop label, it can be very difficult to keep track of which LOOP goes with which END LOOP.

• When you have nested loops, you can use the label to both improve readability and increase control over the execution of your loops. This capability is explored in the next section.

7.6.1.2 Loop termination using labels

You can affect loop execution by adding a loop label after the EXIT keyword in the EXIT statement of a loop, as follows:

EXIT loop_label;
EXIT loop_label WHEN condition;

When you specify a loop label with the EXIT statement, PL/SQL terminates the specified loop.

Consider the last example with nested year and month loops. You might encounter a condition in which both loops should be immediately terminated. The usual, unlabeled EXIT statement inside the month loop would simply halt the month processing for the current year. The year loop would, however, continue its iterations. If the EXIT statement includes the year_loop label, both loops will halt execution:

<<year_loop>>
WHILE year_number <= 1995
LOOP

   <<month_loop>>
   FOR month_number IN 1 .. 12
   LOOP
      calc_totals
         (year_number, month_number, termination_condition);

      /* If the termination condition is TRUE exit ALL loops. */
      EXIT year_loop WHEN termination_condition;

   END LOOP month_loop;

END LOOP year_loop;

In this way, the loop labels offer you added control. Nevertheless, don't use this variation of the EXIT WHEN statement unless absolutely necessary. This kind of EXIT is very unstructured, which makes it hard to test, debug, and maintain. If your loops do have exception conditions, you should instead code them into the boundary of the loop or allow the exception section to handle them.

In other words, if you need to conditionally terminate your loop, then you should not use a FOR loop.

7.6.2.1 Scope in FOR loops

In both numeric and cursor FOR loops, the scope of the loop index is restricted to the body of the loop. You cannot make reference to this implicitly declared variable in code before or after the loop. If you declare a variable of the same name as the loop index, PL/SQL considers that to be a different variable. It will not be used within the loop.

The loop index always takes precedence over a variable of the same name declared outside the scope of the loop. Suppose you have the following code:

PROCEDURE calc_revenue (year_in IN NUMBER)
IS
   month_number NUMBER (2) := 6;
BEGIN
   FOR month_number IN 1 .. 12
   LOOP
      calc_rev_for_month (month_number);
   END LOOP;
END;

The assignment of 6 to month_number in the declaration section has no impact whatsoever on the loop. Within the FOR loop, any reference to month_number is evaluated according to the current value of the loop index.

If you insist on declaring a variable whose name is the same as that of a loop index, you can use dot notation to qualify your references to these variables. In the following example I have a duplicate use of the month_number identifier:

PROCEDURE calc_revenue (year_in IN NUMBER)
IS
   month_number NUMBER (2) := 6;
BEGIN
   FOR month_number IN 1 .. 12
   LOOP
      IF calc_revenue.month_number < 6
      THEN
         ...
      END IF;
      calc_rev_for_month (month_number);
   END LOOP;
END;

Inside the loop, my first reference to month_number is qualified by the procedure name (calc_revenue.month_number). As a result, the compiler can obtain the right value for that month_number (6), while also using the loop index value in the call to calc_rev_for_month.

Of course, you can and should avoid this kind of confusion by using distinct names for your variables and loop indexes.

7.6.2.2 Scope with labels

If you define a label for a loop, then this label can be used to qualify the name of identifiers (loop indexes and locally-declared variables) inside the loop.

Once the loop has terminated, you cannot use the loop label to qualify identifiers. The scope of that label, in other words, is the boundary and body of the loop.

In the following example, I created two nested loops, both of which use a loop index named date_number. (Warning! Do not try this at home. Although it will compile, it can be dangerous to your sanity.)

<<year_loop>>
FOR date_number IN 1994 .. 1999
LOOP

   <<month_loop>>
   FOR date_number IN 1 .. 12
   LOOP

      IF year_loop.date_number = 1994 AND
         date_number = 1
      THEN
         first_month_processing;
      END IF;

   END LOOP month_loop;

END LOOP year_loop;

The IF statement references the date_number loop index of both the outer and inner loops by prefixing the outer loop's name to the first reference to date_number, I tell the compiler which variable I want it to use.

Again, you would be much better off simply changing the name of one or both of the loop indexes; date_number is much too vague a name for either of these loops.