Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: 7.2 The Simple LoopChapter 7
Next: 7.4 The Cursor FOR Loop

7.3 The Numeric FOR Loop

There are two kinds of PL/SQL FOR loops: the numeric FOR loop and the cursor FOR loop. The numeric FOR loop is the traditional and familiar "counted" loop. The number of iterations of the FOR loop is known when the loop starts; it is specified in the loop boundary's range scheme. The number of times the loop executes is actually determined by the range scheme found between the FOR and LOOP keywords in the boundary.

The range scheme implicitly declares the loop index (if it has not already been declared), specifies the start and end points of the range, and optionally dictates the order in which the loop index proceeds (from lowest to highest or highest to lowest).

Here is the general syntax of the numeric FOR loop:

FOR <loop index> IN [REVERSE] <lowest number> .. <highest number>
   <executable statement(s)>

You must have at least one executable statement between the LOOP and END LOOP key words.

The following table summarizes the properties of the numeric FOR loop:



How the loop is terminated

The numeric FOR loop terminates unconditionally when the number of times specified in its range scheme has been satisfied. You can also terminate the loop with an EXIT statement, but this is not recommended.

When the test for termination takes place

After each execution of the loop body, PL/SQL checks the value of the loop index. When it exceeds the difference between the upper and lower bounds of the range scheme, the loop terminates. If the lower bound is greater than the upper bound of the range scheme, the loop never executes its body.

Reason to use this loop

Use the numeric FOR loop when you want to execute a body of code a fixed number of times, and you do not want to halt that looping prematurely.

7.3.1 Rules for Numeric FOR Loops

7.3.2 Examples of Numeric FOR Loops

The following examples demonstrate some variations of the numeric FOR loop syntax:

FOR loop_counter IN REVERSE 1 .. 10
   ... executable statements ...

7.3.3 Handling Nontrivial Increments

PL/SQL does not provide a "step" syntax, whereby you can specify that the loop index increment. In all variations of the PL/SQL numeric FOR loop, the loop index is always incremented or decremented by one.

If you have a loop body which you want executed for a nontrivial (different from one) increment, you will have to write some cute code. For example, what if you want your loop to execute only for all even numbers between 1 and 100? You can make use of the numeric MOD function, as follows:

FOR loop_index IN 1 .. 100
   IF MOD (loop_index, 2) = 0
      /* We have an even number, so perform calculation */
      calc_values (loop_index);
   END IF;

Or you can use simple multiplication inside a loop with half the iterations:

FOR even_number IN 1 .. 50
   calc_values (even_number*2);

In both cases, the calc_values procedure executes only for even numbers. In the first example, the FOR loop executes 100 times. In the second example, the FOR loop executes only 50 times.

Whichever approach you decide to take, be sure to document this kind of technique clearly. You are, in essence, manipulating the numeric FOR loop to do something for which it is not designed. Comments would be very helpful for the maintenance programmer who has to understand why you would code something like that.

Previous: 7.2 The Simple LoopOracle PL/SQL Programming, 2nd EditionNext: 7.4 The Cursor FOR Loop
7.2 The Simple LoopBook Index7.4 The Cursor FOR Loop

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference