|
Chapter 7 Loops |
|
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:
You can associate a label with a loop and use that label to increase your control over loop execution.
The loop boundary creates a scope similar to that of a PL/SQL block.
There is only one way to enter a loop, but a number of ways you can exit your loop.
You can associate a name with a loop by using a label. A loop label in PL/SQL has the following format:
<<label_name>>
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:
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.
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.
A PL/SQL block establishes a scope for all locally-declared variables. Outside of the block, those variables do not exist. A similar kind of scope is created in the body of a loop.
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.
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.
|
|
|
|
| 7.5 The WHILE Loop |
|
7.7 Tips for PL/SQL Loops |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.