I've explained how to construct the different kinds of PL/SQL loops. The topics in this section address the following nuances of loop execution:
where label_name is the name of the label. (By the way, this is the same format used for GOTO labels.) In order to associate a name with a loop, however, the loop label must appear just before the LOOP statement as shown below:
<<all_emps>> FOR emp_rec IN emp_cur LOOP ... END LOOP;
The label can also appear optionally after the END LOOP reserved words, as the following example demonstrates:
<<year_loop>> WHILE year_number <= 1995 LOOP <<month_loop>> FOR month_number IN 1 .. 12 LOOP ... END LOOP month_loop; END LOOP year_loop;
By providing a label for a loop, you give that loop a name. This allows you to use dot notation to refer to loop-related variables, such as the FOR loop index. In the following example of nested FOR loops, I qualify my reference to the year_number index with the loop name:
<<year_loop>> WHILE year_number <= 1995 LOOP <<month_loop>> FOR month_number IN 1 .. 12 LOOP IF year_loop.year_number = 1900 THEN ... END IF; END LOOP month_loop; END LOOP year_loop;
The loop label is useful in two particular situations:
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 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.
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.
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.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.