Looping Statements

This section discusses one form of looping by using the FOR statement. Looping allows you to execute a block of code repeatedly until some condition occurs. Day 15, "Exploring Advanced Topics," demonstrates a similar use with recursion; however, recursion calls the same function repeatedly until some condition occurs.

The Syntax for FOR Loops

FOR loop_index IN [REVERSE] low_value..high_value LOOP
     Statements to execute
END LOOP;

The loop_index is defined by Oracle as a local variable of type INTEGER. REVERSE allows you to execute the loop in reverse order. The low_value..high_value is the range to execute the loop. These can be constants, or they can be variables. The line must be terminated with LOOP with no semicolon at the end of this line. You can list the statements to be executed until the LOOP is evaluated to false.

Go ahead and enter and then execute the code in Listing 5.13 for your first FOR loop.

INPUT
Listing 5.13. Your first FOR loop.

set echo on
BEGIN
     FOR v_loopcounter IN 1..5 LOOP
          DBMS_OUTPUT.PUT_LINE(`Loop counter is ` || v_loopcounter);
     END LOOP;
END;
/

OUTPUT
When you execute the preceding loop, your output should be

Loop counter is 1
Loop counter is 2
Loop counter is 3
Loop counter is 4
Loop counter is 5

You can now start to get a little more complex by nesting FOR loops. When you nest FOR loops, the outer loop is executed once, then the inner loop is executed for as many times as the range indicates, and then control is returned to the outer loop until its range expires. Go ahead and type in and then execute the loop from Listing 5.14.

INPUT
Listing 5.14. Nesting FOR loops.

BEGIN
     FOR v_outerloopcounter IN 1..2 LOOP
          FOR v_innerloopcounter IN 1..4 LOOP
               DBMS_OUTPUT.PUT_LINE(`Outer Loop counter is ` ||
                    v_outerloopcounter ||
                    ` Inner Loop counter is ` || v_innerloopcounter);
          END LOOP;
     END LOOP;
END;
/

OUTPUT
When you execute the preceding code, your output will look like

Outer Loop counter is 1 Inner Loop counter is 1
Outer Loop counter is 1 Inner Loop counter is 2
Outer Loop counter is 1 Inner Loop counter is 3
Outer Loop counter is 1 Inner Loop counter is 4
Outer Loop counter is 2 Inner Loop counter is 1
Outer Loop counter is 2 Inner Loop counter is 2
Outer Loop counter is 2 Inner Loop counter is 3
Outer Loop counter is 2 Inner Loop counter is 4

Again, the order of nested loops is important depending upon what you are trying to accomplish.

Reversing the Loop

By adding the keyword REVERSE after IN, you tell Oracle to process the loop in reverse. You still must list the range from low to high values, otherwise the loop will not execute. The test of REVERSE also demonstrates using variables instead of fixed constants as shown in Listing 5.15.

INPUT
Listing 5.15. Reversing the loop.

DECLARE
     v_Start Integer := 1;
BEGIN
     FOR v_loopcounter IN REVERSE v_Start..5 LOOP
          DBMS_OUTPUT.PUT_LINE(`Loop counter is ` || v_loopcounter);
     END LOOP;
END;
/

OUTPUT
Your output should appear as follows:

Loop counter is 5
Loop counter is 4
Loop counter is 3
Loop counter is 2
Loop counter is 1

Different Incrementing Through a Loop

As you can see, Oracle provides no option to step through a loop with an increment other than one. You can write loops that will execute with a different increment by executing statements only if a certain condition is true. The example from Listing 5.16 demonstrates how to increment by a value of 2.

INPUT
Listing 5.16. Changing the loop increment.

BEGIN
     FOR v_loopcounter IN 1..6 LOOP
          IF MOD(v_loopcounter,2) = 0 THEN
               DBMS_OUTPUT.PUT_LINE(`Loop counter is ` || v_loopcounter);
          END IF; -- End execution of statements for even counter
     END LOOP;
END;
/

OUTPUT
After the loop has executed, your output should appear as

Loop counter is 2
Loop counter is 4
Loop counter is 6

This is just one of many ways in which you could increment a loop. The MOD function in this case simply tests to make sure that the number is divisible evenly by a value of 2. You could easily change this to 3, 5, or whatever you want to increment. To decrement, simply add the keyword REVERSE.

Summary

You accomplished a lot in Day 5! First, you took a closer look at how to create your own functions. Just the reduction of code and ease of use are two major reasons to write functions. If you work in a corporate environment and must share code, creating functions is a must.

NULL statements are simply that. They do nothing, but act as a placeholder.

IF statements can take many forms. IF statements are always evaluated from left to right (unless overridden by parentheses), and if one of the conditions becomes false, then the whole statement is invalid. This is important to know because the largest room for error is not syntax, but rather the logical errors, which are much harder to debug. Formatting the IF statement blocks is important not only from a coding perspective, but also from a readability perspective. Regular IF and nested IF statements operate like AND; whereas IF...ELSIF statements act like an OR, which allows you to create the equivalent of CASE statements as long as none of the conditions overlap!

Finally, loops allow you to repeat a series of PL/SQL code either until a condition is met, or you break out of the loop using EXIT. It is important to know the order of execution of the loops to reduce logic errors. You also saw a neat trick on how to increment loops by values other than one, an option which is not provided by Oracle.