Iterative Processing

Contact Us or call 1-877-932-8228
Iterative Processing

Iterative Processing

PL/SQL includes several iterative control structures. The basic LOOP, WHILE LOOP, FOR LOOP and CURSOR FOR LOOP provide the ability to iterate. Several commands can be used to interrupt a loop (EXIT WHEN, GOTO, executing a RETURN from the subprogram, raising an exception). Code can become difficult to read if you have deeply nested loops. Loops (as well as PL/SQL blocks) can be labeled using an undeclared identifier enclosed by double angle brackets.

The LOOP Statement

A simple, or indefinite, loop will continue until an EXIT or RETURN is encountered or an EXIT WHEN statement is evaluated to TRUE.

Code Sample:

Within-the-Block/Demos/loop.sql
DECLARE
  v_number NUMBER := 1;
BEGIN
  dbms_output.put_line('Starting for_loop.sql...');
  
  LOOP
    EXIT WHEN v_number > 10; 
    dbms_output.put_line('...' || v_number);     
    v_number := v_number + 1;    
    
  END LOOP;
 
  dbms_output.put_line('Ending...');
END;

Code Explanation

In this example the loop continues until the variable v_number is evaluated to be equal to ten. At that time the control passes to the DBMS_OUTPUT.PUT_LINE statement just before the END keyword.

The WHILE Statement

A WHILE loop continues until the Boolean expression specified beside the WHILE keyword evaluates to false.

Code Sample:

Within-the-Block/Demos/while_loop.sql
DECLARE
  -- Change the value of v_number to see the execution path change
  v_number NUMBER := 1;
BEGIN
  dbms_output.put_line('Starting while_loop.sql...');
   
  WHILE v_number <= 10 LOOP
    dbms_output.put_line('...' || v_number);  
    v_number := v_number + 1;
  END LOOP;
   
  dbms_output.put_line('Ending...');
END;

Code Explanation

In this example, the WHILE loop ceases when v_number is evaluated to be greater than ten.

The FOR Statement

A FOR loop iterates over an integer range specified by the FOR keyword. By default the loop proceeds upward. It can be modified to iterate downward using the REVERSE keyword.

Code Sample:

Within-the-Block/Demos/for_loop.sql
declare
  i number := 100;
BEGIN
  dbms_output.put_line('Starting for_loop.sql...');

  FOR i IN 1..10 LOOP
    dbms_output.put_line('...' || i);
  END LOOP;

  dbms_output.put_line('i = ' || i);
  dbms_output.put_line('Ending...');
END;

Code Explanation

The local variable (i) that increments through each loop iterations implicitly declared and therefore cannot appear in the DECLARE clause. It also cannot be used as the target of an explicit assignment within the loop (or an error will occur). And of course it cannot be referenced outside of the loop.

A Cursor For Loop is used to retrieve each row of a result set returned by a query into a record. It is discussed in a separate lesson.

Next