PL/SQL FOR Loop

Summary: in this tutorial, you will learn how to use PL/SQL FOR loop to execute a sequence of statements specified number of times.

Introducing to PL/SQL FOR Loop

PL/SQL FOR loop is an iterative statement that allows you to execute a sequence of statements a fixed number of times. Unlike the PL/SQL WHILE loop, the number of iterations of the PL/SQL FOR loop is known before the loop starts.

The following illustrates the PL/SQL FOR loop statement syntax:

FOR loop_counter IN [REVERSE] lower_bound .. higher_bound
LOOP
   sequence_of_statements;
END LOOP;

Let’s examine the PL/SQL FOR loop syntax in greater detail:

• PL/SQL automatically creates a local variable loop_counter with INTEGER data type implicitly in the FOR loop so you don’t have to declare it. The scope of the loop_counter variable is within the loop itself so you cannot reference it outside the loop. After each iteration, PL/SQL increases loop_counter by 1 and checks if the loop_counter is still in the range of lower_bound and higher_bound to execute the next iteration. If the loop_counter is not in the range, the loop is terminated.
• The lower_bound..higher_bound is the range of integers that FOR loop iterates over. This range is known as iteration scheme. The range operator is specified by a double dot ( ..) between the lower_bound and higher_bound.
• The FOR loop evaluates the range when the loop first entered and never re-evaluated. The lower_bound has to be less than or equal to the higher_bound. If the lower_bound is equal to the higher_bound, the sequence of statements is executed once. If the lower_bound is larger than the higher_bound, the sequence within the loop will never execute. The lower_bound and higher_bound can be literals, variables, or expressions that evaluate to numbers. Otherwise, PL/SQL raises a VALUE_ERROR exception.
• By default, the loop iterates in the upward fashion from the lower_bound to the higher_bound. However, if you want to force the loop to iterate in a downward way from the higher_bound to the lower_bound, you can use the REVERSE keyword after the IN keyword.
• You must have at least one executable statement between LOOP and END LOOP keywords.

Examples of PL/SQL FOR LOOP

In the first example, we print integers from 1 to 10 by using PL/SQL FOR loop as the code below:

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
  n_times NUMBER := 10;
BEGIN
  FOR n_i IN 1..n_times LOOP
    DBMS_OUTPUT.PUT_LINE(n_i);
  END LOOP;
END;
/

PL/SQL FOR Loop – Upward interation

In the second example, we use the RESERVE keyword to print a list of integers in descending order.

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
  n_times NUMBER := 10;
BEGIN
  FOR n_i IN REVERSE 1..n_times LOOP
    DBMS_OUTPUT.PUT_LINE(n_i);
  END LOOP;
END;
/

PL/SQL FOR Loop – downward iteration

In this tutorial, you’ve learned how to use PL/SQL FOR loop statement to execute a statement specified number of times.