Lab 8.4 Nested Loops

[ Team LiB ]

Lab 8.4 Nested Loops Lab Objectives After this Lab, you will be able to: Use 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

DECLARE
   v_counter1 INTEGER := 0;
   v_counter2 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 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. It is important to note that v_counter2 is not initialized at the time of the declaration. The simple loop is placed inside the body of the WHILE loop, and the value of v_counter2 must be initialized every time before control is passed to the simple loop.

Once control is passed 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 the EXIT WHEN condition evaluates to FALSE, control is passed back to the top of the simple loop. If the EXIT WHEN condition evaluates to TRUE, control is passed to the first executable statement outside of the loop. In our case, control is passed back to the outer loop, and 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

PL/SQL procedure successfully completed.

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

Loop Labels

Earlier in the book, you read about labeling of PL/SQL blocks. Loops can be labeled in a similar manner, as follows:

<<label_name>>
FOR LOOP_COUNTER IN LOWER_LIMIT..UPPER_LIMIT LOOP
   STATEMENT 1;
   ...
   STATEMENT N;
END LOOP label_name;

The label must appear right before the beginning of the loop. This syntax example shows that the label can be optionally used at the end of the loop statement. It is very helpful to label nested loops because labels improve readability. Consider the following example.

FOR EXAMPLE

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.

FOR EXAMPLE

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. In order 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

PL/SQL procedure successfully completed.

Your program 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 is passed to the inner loop, the value of v_counter from the outer loop is unavailable. When control is passed back to the outer loop, the value of v_counter becomes available again.

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.

[ Team LiB ]