Oracle PL/SQL by Example, Fifth Edition (2015)

---

Using Nested Loops

You have explored three types of loops: simple loops, WHILE loops, and numeric FOR loops. Any of these three types of loops can be nested inside one another. For example, a simple loop can be nested inside a WHILE loop, and vice versa. Consider the following example:

For Example ch07_2a.sql

DECLARE
v_counter1 BINARY_INTEGER := 0;
v_counter2 BINARY_INTEGER;
BEGIN
WHILE v_counter1 < 3
LOOP
DBMS_OUTPUT.PUT_LINE ('v_counter1: '||v_counter1);
v_counter2 := 0;
LOOP
DBMS_OUTPUT.PUT_LINE (' v_counter2: '||v_counter2);
v_counter2 := v_counter2 + 1;
EXIT WHEN v_counter2 >= 2;
END LOOP;
v_counter1 := v_counter1 + 1;
END LOOP;
END;

In this example, the WHILE loop is called an outer loop because it encompasses the simple loop. The simple loop (highlighted in bold) is called an inner loop because it is enclosed by the body of the WHILE loop.

The outer loop is controlled by the loop counter, v_counter1, and it will execute providing the value of v_counter1 is less than 3. With each iteration of the loop, the value of v_counter1 is displayed on the screen. Next, the value of v_counter2 is initialized to 0. Note thatv_counter2 is not initialized at the time of the declaration. The simple loop is placed inside the body of the WHILE loop, so the value of v_counter2 must be initialized every time before control passes to the simple loop.

Once control passes to the inner loop, the value of v_counter2 is displayed on the screen and incremented by 1. Next, the exit when condition is evaluated. If this condition evaluates to FALSE, control passes back to the top of the simple loop. If it evaluates to TRUE, control passes to the first executable statement outside the loop. In our case, control passes back to the outer loop, the value of v_counter1 is incremented by 1, and the test condition of the WHILE loop is evaluated again.

This logic is demonstrated by the output produced by the example:

v_counter1: 0
v_counter2: 0
v_counter2: 1
v_counter1: 1
v_counter2: 0
v_counter2: 1
v_counter1: 2
v_counter2: 0
v_counter2: 1

Notice that for each value of v_counter1, two values of v_counter2 are displayed. For the first iteration of the outer loop, the value of v_counter1 is equal to 0. Once control passes to the inner loop, the value of v_counter2 is displayed on the screen twice, and so forth.

Using Loop Labels

Earlier in the book, you learned about labeling of PL/SQL blocks. Loops can be labeled in a similar manner, as illustrated in Listing 7.3.

Listing 7.3 Loop Labels

<<label_name>>
FOR loop_counter IN lower_limit..upper_limit
LOOP
STATEMENT 1;
...
STATEMENT N;
END LOOP label_name;

The label must appear immediately before the beginning of the loop. The preceding syntax shows that the label can be optionally used at the end of the loop statement. It is very helpful to label nested loops—such labels improve the script’s readability. Consider the following example:

For Example ch07_3a.sql

BEGIN
<<outer_loop>>
FOR i IN 1..3
LOOP
DBMS_OUTPUT.PUT_LINE ('i = '||i);
<<inner_loop>>
FOR j IN 1..2
LOOP
DBMS_OUTPUT.PUT_LINE ('j = '||j);
END LOOP inner_loop;
END LOOP outer_loop;
END;

For both outer and inner loops, the statement END LOOP must be used. If the loop label is added to each END LOOP statement, it becomes easier to understand which loop is being terminated.

Loop labels can also be used when referencing loop counters, as shown in the following example:

For Example ch07_4a.sql

BEGIN
<<outer>>
FOR v_counter IN 1..3
LOOP
<<inner>>
FOR v_counter IN 1..2
LOOP
DBMS_OUTPUT.PUT_LINE ('outer.v_counter '||outer.v_counter);
DBMS_OUTPUT.PUT_LINE ('inner.v_counter '||inner.v_counter);
END LOOP inner;
END LOOP outer;
END;

In this example, both the inner and outer loops use the same loop counter, v_counter. To reference both the outer and inner values of v_counter, loop labels are used. This example produces the following output:

outer.v_counter 1
inner.v_counter 1
outer.v_counter 1
inner.v_counter 2
outer.v_counter 2
inner.v_counter 1
outer.v_counter 2
inner.v_counter 2
outer.v_counter 3
inner.v_counter 1
outer.v_counter 3
inner.v_counter 2

Note that the script is able to differentiate between two variables having the same name because loop labels are used when the variables are referenced. If no loop labels are used when v_counter is referenced, the output produced by this script will change significantly. Basically, once control passes to the inner loop, the value of v_counter from the outer loop is unavailable. When control passes back to the outer loop, the value of v_counter becomes available again, as shown in the following example (affected statements are shown in bold):

For Example ch07_4b.sql

BEGIN
<<outer>>
FOR v_counter IN 1..3
LOOP
DBMS_OUTPUT.PUT_LINE ('outer.v_counter '|| v_counter);
<<inner>>
FOR v_counter IN 1..2
LOOP
DBMS_OUTPUT.PUT_LINE (' outer.v_counter '||v_counter);
DBMS_OUTPUT.PUT_LINE (' inner.v_counter '||v_counter);
END LOOP inner;
END LOOP outer;
END;

To highlight the loop behavior, a new DBMS_OUTPUT.PUT_LINE statement has been added to the outer loop, and the loop labels have been removed when referencing the variable v_counter. When executed, this version of the script produces the following output:

outer.v_counter 1
outer.v_counter 1
inner.v_counter 1
outer.v_counter 2
inner.v_counter 2
outer.v_counter 2
outer.v_counter 1
inner.v_counter 1
outer.v_counter 2
inner.v_counter 2
outer.v_counter 3
outer.v_counter 1
inner.v_counter 1
outer.v_counter 2
inner.v_counter 2

As you can see, inside the inner loop, the value of the v_counter from the outer loop is not available when it is referenced without the loop label. In this example, the same name for two different loop counters is used to demonstrate another use of loop labels. However, it is not considered a good programming practice to use the same name for different variables.

Summary

In Chapter 6, you began exploring the various types of loops supported in PL/SQL. In this chapter, you continued this exploration by learning about additional loop features introduced in Oracle 11g. You also discovered about how various loop types may be nested inside one another. Finally, you learned how loop labels may be used to improve code readability and maintainability when working with nested loops.