PL/SQL Loops

A loop statement allows the execution of a statement or group of statements multiple times. There are three types of loop statement in PL/SQL.

• Basic loop – Allows for the repetition of a statement or group of statements with no condition. Termination of this type of loop is via the ‘EXIT’ or ‘EXIT-WHEN’ statements from within the loop.
• ‘FOR’ loop – Useful when it is known in advance the number of times the statements within the loop need to be executed.
• ‘WHILE’ loop – Repeats a statement or group of statements while an expression evaluates to true. There is a condition to get into the loop, so if the condition evaluates to true at the beginning then the statements inside the loop will never execute.

Basic Loop

This is the simplest of the loops in PL/SQL as there are no conditions to enter or exit the loop. In order to terminate this type of loop, there must be one or more ‘EXIT’ or ‘EXIT-WHEN’ statements within the loop. Without one of these statements an infinite loop would be created as there is no means of termination. The basic syntax for this type of loop is as follows.

LOOP
   -- Statement(s) to execute.
   EXIT WHEN condition;
END LOOP;

The example below initialises a variable to zero, outputs its value and adds one to it, whilst it is less than five.

DECLARE
   i NUMBER(1) := 0;
BEGIN
   LOOP
      DBMS_OUTPUT.PUT_LINE(i);
      i := i + 1;
      EXIT WHEN i >= 5;
   END LOOP;
END;

Output from the above example will be as follows.

0
1
2
3
4

‘FOR’ Loop

The ‘FOR’ loop takes the following form.

FOR counter IN counter_start..counter_end LOOP
   -- Statement(s) to execute.
END LOOP;

The ‘counter’ is the loop counter that automatically increments with each iteration of the loop. The ‘counter_start’ is the starting point of the ‘counter’, whilst the ‘counter_end’ is the point after which the loop terminates.

The following example outputs to the screen the value of the loop counter for each iteration of the loop. The output will be the same as for the Basic loop above.

BEGIN
   FOR i IN 0..4 LOOP
      DBMS_OUTPUT.PUT_LINE(i);
   END LOOP;
END;

‘WHILE’ Loop

As mentioned above, the ‘WHILE’ loop has a condition, which must evaluate to true before the statements within it can be executed.

WHILE condition LOOP
   -- Statement(s) to execute.
END LOOP;

In the below example, a variable is declared and initialised to zero, then the ‘WHILE’ loop checks to see that the variable is less than five before outputting it to the screen and incrementing the variable by one.

DECLARE
   i NUMBER(1) := 0;
BEGIN
   WHILE i < 5 LOOP
      DBMS_OUTPUT.PUT_LINE(i);
      i := i + 1;
   END LOOP;
END;

The output will be the same as in the Basic and ‘FOR’ loop examples above. Note that if the variable ‘i’ had been declared and initialised to five or above, then the statements within the loop would not be executed at all.