PL/SQL FOR LOOP

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

Introduction to PL/SQL FOR LOOP statement

PL/SQL FOR LOOP executes a sequence of statements a specified number of times. The PL/SQL FOR LOOP statement has the following structure:

FOR index IN lower_bound .. upper_bound
LOOP 
    statements; 
END LOOP;Code language: SQL (Structured Query Language) (sql)

The index is an implicit variable. It is local to the FOR LOOP statement. In other words, you cannot reference it outside the loop.

Inside the loop, you can reference index but you cannot change its value. After the FOR LOOP statement executes, the index becomes undefined.

Both lower_bound and upper_bound are numbers or expressions that evaluate numbers. The lower_bound and upper_bound are evaluated once when the FOR LOOP statement starts. Their values are stored as temporary PLS_INTEGER values. The results of lower_bound and upper_bound are rounded to the nearest integer if necessary.

If you modify the values of lower_bound or upper_bound inside the loop, the change will have no effect because they are evaluated once only before the first loop iteration starts.

Typically, lower_bound is less than upper_bound. In this case, index is set to lower_bound, the statements execute, and control returns to the top of the loop, where index is compared to upper_bound. If index is less than upper_bound, index is incremented by one, the statements execute, and control again returns to the top of the loop. When index is greater than upper_bound, the loop terminates, and control transfers to the statement after the FOR LOOP statement.

If lower_bound is equal to upper_bound, the statements execute only once. When lower_bound is greater than upper_bound, the statements do not execute at all.

PL/SQL FOR LOOP examples

Let’s take some examples of using the FOR LOOP statement to understand how it works.

A) Simple PL/SQL FOR LOOP example

In this example, the loop index is l_counter, lower_bound is one, and upper_bound is five. The loop shows a list of integers from 1 to 5.

BEGIN
  FOR l_counter IN 1..5
  LOOP
    DBMS_OUTPUT.PUT_LINE( l_counter );
  END LOOP;
END;
Code language: SQL (Structured Query Language) (sql)

Here is the result:

1
2
3
4
5
Code language: SQL (Structured Query Language) (sql)

B) Simulating STEP clause in FOR LOOP statement

The loop index is increased by one after each loop iteration and you cannot change the increment e.g., two, three, and four. However, you can use an additional variable to simulate the increment by two, three, four, etc., as shown in the example below:

DECLARE
  l_step  PLS_INTEGER := 2;
BEGIN
  FOR l_counter IN 1..5 LOOP
    dbms_output.put_line (l_counter*l_step);
  END LOOP;
END;Code language: SQL (Structured Query Language) (sql)

Result:

2
4
6
8
10
Code language: SQL (Structured Query Language) (sql)

The result shows that, after each loop iteration, the output number is incremented by two instead of one.

C) Referencing variable with the same name as the loop index

Consider the following example:

DECLARE
  l_counter PLS_INTEGER := 10;
BEGIN
  FOR l_counter IN 1.. 5 loop
    DBMS_OUTPUT.PUT_LINE (l_counter);
  end loop;
  -- after the loop
  DBMS_OUTPUT.PUT_LINE (l_counter);
END; 
Code language: SQL (Structured Query Language) (sql)

Here is the result:

1
2
3
4
5
10Code language: SQL (Structured Query Language) (sql)

In this example, we had a variable named l_counter, which is also the name of the index. The result shows that l_counter in the FOR loop hides the variable l_counter declared in the enclosing block.

To reference the variable l_counter inside the loop, you must qualify it using a block label as shown below:

<<outer>>
DECLARE
  l_counter PLS_INTEGER := 10;
BEGIN
  FOR l_counter IN 1.. 5 loop
    DBMS_OUTPUT.PUT_LINE ('Local counter:' ||  l_counter);
    outer.l_counter := l_counter;
  end loop;
  -- after the loop
  DBMS_OUTPUT.PUT_LINE ('Global counter' || l_counter);
END outer;
Code language: SQL (Structured Query Language) (sql)

D) Referencing loop index outside the FOR LOOP

The following example causes an error because it references the loop index, which is undefined, outside the FOR LOOP statement.

BEGIN
  FOR l_index IN 1..3 loop
    DBMS_OUTPUT.PUT_LINE (l_index);
  END LOOP;
  -- referencing index after the loop
  DBMS_OUTPUT.PUT_LINE (l_index);
END;
Code language: SQL (Structured Query Language) (sql)

Oracle issued the following error:

PLS-00201: identifier 'L_INDEX' must be declared
Code language: SQL (Structured Query Language) (sql)

FOR LOOP with REVERSE keyword

The following shows the structure of the FOR LOOP statement with REVERSE keyword:

FOR index IN REVERSE lower_bound .. upper_bound
    LOOP 
    statements; 
END LOOP;
Code language: SQL (Structured Query Language) (sql)

With the REVERSE keyword, the index is set to upper_bound and decreased by one in each loop iteration until it reaches lower_bound.

See the following example:

BEGIN
  FOR l_counter IN REVERSE 1..3
  LOOP
    DBMS_OUTPUT.PUT_LINE( l_counter );
  END LOOP;
END;
Code language: SQL (Structured Query Language) (sql)

Result:

3
2
1
Code language: SQL (Structured Query Language) (sql)

Without the REVERSE keyword, the output will be:

1
2
3
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the PL/SQL FOR LOOP statement to execute a sequence of statements a specified number of times.