Loop Control
Use of the LOOP statement provides iterative processing based on logical choices. The basic construct for PL/SQL LOOPs is shown in the following example:
<
LOOP
(repetitive processing)
END LOOP loop_name;
To break out of a loop such as this, you must issue an EXIT or GOTO statement based on some processing condition. If you raise a user-defined exception, the LOOP also terminates. Now, examine three types of PL/SQL loops that expressly define LOOP termination conditions.
WHILE Loops
The WHILE loop checks the status of any PL/SQL expression that evaluates to TRUE, FALSE, or NULL at the start of each processing cycle. The following is an example of the use of WHILE loops:
WHILE (expression) LOOP
(loop processing)
END LOOP;
As stated, the program evaluates the expression at the start of each loop cycle. The program performs the loop processing if the expression evaluates to TRUE. A FALSE or NULL evaluation terminates the loop. Iterations through the loop are exclusively determined by the evaluation of the expression.
Numeric FOR Loops
You can control loop iterations with the use of numeric FOR loops. This mechanism enables the developer to establish a range of integers for which the loop will cycle. The following example from the order_total package illustrates numeric FOR loops:
<
FOR i in 1..g_line_counter LOOP
(loop processing)
END LOOP recip_list;
In this example, loop processing cycles over the range of integers 1 through the value of g_line_counter. The value of the loop index i is checked at the start of the loop and incremented at the end of the loop. When i is one greater than g_line_counter, the loop terminates.
Cursor FOR Loops
Cursor FOR loops combine cursor control and conditional control for manipulation of database information. The loop index, cursor OPEN, cursor FETCH, and cursor CLOSE are all implicit when using cursor FOR loops. Consider the following example:
CURSOR c_line_item IS
(sql statement)
BEGIN
FOR li_info IN c_line_item LOOP
(retrieved record processing)
END LOOP;
END;
As shown, the program explicitly declares the c_line_item cursor before its reference in the FOR loop. When the program enters the FOR loop, the code implicitly opens c_line_item and implicitly creates the li_info record as if the following declaration were made:
li_info c_line_item%ROWTYPE;
Once inside the loop, the program can reference the fields of the li_info record that are assigned values by the implicit FETCH inside the FOR loop. Fields of li_info mirror the row retrieved by the c_line_item cursor.
When data is exhausted for the FETCH, c_line_item is implicitly closed.
Iterative Control
The IF-THEN-ELSE structure provides alternative processing paths that depend on certain conditions. For example, consider merchandise orders with multiple-line items where a list of recipients is built. Using conditional and iterative control to build the recipient list, the code is as follows:
PROCEDURE
init_recip_list
IS
recipient_num NUMBER;
i BINARY_INTEGER;
j BINARY_INTEGER := 1;
k BINARY_INTEGER;
BEGIN
g_out_msg := 'init_recip_list';
<
FOR i in 1..g_line_counter LOOP
IF i = 1 THEN
g_recip_list(j) := g_recipient_num(i);
j := j + 1;
g_recip_list(j) := 0;
ELSE
FOR k in 1..j LOOP
IF g_recipient_num(i) = g_recip_list(k) THEN
exit;
ELSIF k = j THEN
g_recip_list(j) := g_recipient_num(i);
j := j + 1;
g_recip_list(j) := 0;
end IF;
end LOOP;
end IF;
end LOOP recip_list;
END;
In the order_total example, the subprogram init_recip_list builds a list of unique recipient numbers for calculating additional shipping charges. There is a controlling FOR loop that cycles through each recipient number found on a particular order. The g_recip_list array is initialized with the first recipient number, and subsequent numbers are checked against all unique numbers in g_recip_list until a unique list of all recipients is compiled.
Also illustrated in this example is the IF-THEN-ELSE extension ELSIF. This statement provides further conditional control with additional constraint checks within the IF-THEN-ELSE structure. Use of ELSIF also requires a THEN statement in executing logic control.
Another example of iterative control is the use of the EXIT-WHEN statement that allows completion of a LOOP once certain conditions are met. Consider the example of exiting a cursor fetch loop:
open c_line_item;
loop
fetch c_line_item
into li_info;
EXIT WHEN (c_line_item%NOTFOUND) or (c_line_item%NOTFOUND is NULL);
In this example, the LOOP is terminated when no more data is found to satisfy the select statement of cursor c_line_item.
