The WHILE Loop
The WHILE loop is a conditional loop that continues to execute as long as the Boolean condition defined in the loop boundary evaluates to TRUE. Because the WHILE loop execution depends on a condition and is not fixed, you should use a WHILE loop if you don't know in advance the number of times a loop must execute.
Here is the general syntax for the WHILE loop:
WHILE condition LOOP executable statement(s)END LOOP;where condition is a Boolean variable or an expression that evaluates to a Boolean value of TRUE, FALSE, or NULL. Each time an iteration of the loop's body is to be executed, the condition is checked. If it evaluates to TRUE, then the body is executed. If it evaluates to FALSE or NULL, then the loop terminates and control passes to the next executable statement following the END LOOP statement.
The following table summarizes the properties of the WHILE loop:
| Property | Description |
| How the loop is terminated | The WHILE loop terminates when the Boolean expression in its boundary evaluates to FALSE or NULL. |
| When the test for termination takes place | The test for termination of a WHILE loop takes place in the loop boundary. This evaluation occurs prior to the first and each subsequent execution of the body. The WHILE loop, therefore, is not guaranteed to execute its loop even a single time. |
| Reason to use this loop | Use the WHILE loop when: · You are not sure how many times you must execute the loop body, and · You will want to conditionally terminate the loop, and · You don't have to execute the body at least one time. |
The WHILE loop's condition is tested at the beginning of the loop's iteration, before the body of the loop is executed. There are two consequences to this pre-execution test:
· All the information needed to evaluate the condition must be set before the loop is executed for the first time.
· It is possible that the WHILE loop will not execute even a single time.
Here is an example of a WHILE loop from the datemgr.pkg file available on the O'Reilly site. It shows a boundary condition consisting of a complex Boolean expression. There are two reasons for the WHILE loop to stop: either I have run out of date masks to attempt a conversion, or I have successfully performed a conversion (and date_converted is now TRUE):
WHILE mask_index <= mask_count AND NOT date_convertedLOOP BEGIN /* Try to convert string using mask in table row */ retval := TO_DATE (value_in, fmts (mask_index)); date_converted := TRUE; EXCEPTION WHEN OTHERS THEN retval := NULL; mask_index:= mask_index+ 1; END;END LOOP;
5.4 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 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 numberLOOP executable statement(s)END LOOP;You must have at least one executable statement between the LOOP and END LOOP keywords.
The following table summarizes the properties of the numeric FOR loop: