PL/SQL WHILE Loop

Summary: in this tutorial, you will learn about PL/SQL WHILE loop statement to execute a sequence of statements as long as a specified condition is TRUE.

The WHILE loop syntax

Here is the syntax for the WHILE loop statement:

WHILE condition
LOOP
    statements;
END LOOP;
Code language: SQL (Structured Query Language) (sql)

The condition in the WHILE is a Boolean expression that evaluates to TRUE, FALSE or NULL.

The WHILE loop statement continues to execute the statements between the LOOP and END LOOP as long as the condition in the WHILE clause evaluates to TRUE.

PL/SQL evaluates the condition in the WHILE clause before each loop iteration. If the condition is TRUE, then the loop body executes. In case it is FALSE or NULL, the loop terminates.

If the condition is FALSE before entering the loop, the WHILE loop does not execute at all. This behavior is different from the LOOP statement whose loop body always executes once.

To terminate the loop prematurely, you use an EXIT or EXIT WHEN statement.

PL/SQL WHILE loop examples

Let’s take some examples of using the WHILE loop statement to see how it works.

A) Simple WHILE loop example

The following example illustrates how to use the WHILE loop statement:

DECLARE
  n_counter NUMBER := 1;
BEGIN
  WHILE n_counter <= 5
  LOOP
    DBMS_OUTPUT.PUT_LINE( 'Counter : ' || n_counter );
    n_counter := n_counter + 1;
  END LOOP;
END;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

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

In this example:

• First, the counter was initialized to one.
• Second, the condition in the WHILE clause was evaluated before each loop iteration.
• Third, inside the loop body, the counter was increased by one in each loop iteration. After five iterations, the condition was FALSE that caused the loop terminated.

B) WHILE loop example terminated by EXIT WHEN statement

The following example is the same as the one above except that it has an additional EXITWHEN statement.

DECLARE
   n_counter NUMBER := 1;
BEGIN
   WHILE n_counter <= 5
      LOOP
        DBMS_OUTPUT.PUT_LINE( 'Counter : ' || n_counter );
        n_counter := n_counter + 1;
        EXIT WHEN n_counter = 3;
      END LOOP;
   END;
Code language: SQL (Structured Query Language) (sql)

The following is the output:

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

The condition in the EXIT WHEN clause evaluated to true when the counter is three. Therefore, the loop body was only executed two times before termination.

In this tutorial, you have learned how to use the PL/SQL WHILE loop statement to execute a sequence of statements as long as a specified condition is TRUE.