Loops

You may use a loop to run one or more statements multiple times. There are three types of loops in PL/SQL:

Simple loops Run until you explicitly end the loop
WHILE loops Run until a specified condition occurs
FOR loops Run a predetermined number of times

You ll learn about these loops in the following sections.

Simple Loops

A simple loop runs until you explicitly end the loop. The syntax for a simple loop is as follows :

 LOOP  statements  END LOOP;

To end the loop, you use either an EXIT or EXIT WHEN statement. The EXIT statement ends a loop immediately, and the EXIT WHEN statement ends a loop when a specified condition occurs.

The following example shows a simple loop. A variable named counter is initialized to 0 prior to the beginning of the loop, and the loop adds 1 to counter . The loop exits when counter is equal to 5 using an EXIT WHEN statement.

 counter := 0; LOOP   counter := counter + 1;   EXIT WHEN counter = 5; END LOOP;

Note	The EXIT WHEN statement can appear anywhere in the loop code.

WHILE Loops

A WHILE loop runs until a specified condition occurs. The syntax for a WHILE loop is as follows:

 WHILE  condition  LOOP  statements  END LOOP;

The following example shows a WHILE loop that executes while the counter variable is less than 6:

 counter := 0; WHILE counter < 6 LOOP   counter := counter + 1; END LOOP;

FOR Loops

A FOR loop runs a predetermined number of times; you determine the number of times the loop runs by specifying the lower and upper bounds for a loop variable. The loop variable is then incremented (or decremented) each time around the loop. The syntax for a FOR loop is as follows:

 FOR  loop_variable  IN [REVERSE]  lower_bound..upper_bound  LOOP  statements  END LOOP;

where

loop_variable specifies the loop variable. You can use a variable that already exists for the loop variable, or you can just have the loop create one for you (this occurs if the variable specified doesn t exist). The loop variable value is increased (or decreased if you use the REVERSE keyword) by 1 each time through the loop.
REVERSE specifies that the loop variable value is to be decremented each time through the loop. The loop variable is initialized to the upper bound and is decremented by 1 until the loop variable reaches the lower bound. You must still specify the lower bound before the upper bound.
lower_bound specifies the loop s lower bound. The loop variable is initialized to this lower bound as long as REVERSE is not used.
upper_bound specifies the loop s upper bound. If REVERSE is used, the loop variable is initialized to this upper bound.

The following example shows a FOR loop. Notice that the variable count2 isn t explicitly declared ”the FOR loop automatically creates an INTEGER variable in this case:

 FOR count2 IN 1..5 LOOP   DBMS_OUTPUT.PUT_LINE(count2); END LOOP;

If REVERSE was used in this example, the loop variable counter would start at 5, be decremented by 1 each time through the loop, and end at 1.