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.

Introduction to PL/SQL WHILE loop statement #

PL/SQL WHILE loop is a control structure that repeatedly executes a code block if a specific condition remains true.

Here’s the syntax for the WHILE loop statement:

WHILE condition
LOOP
    statements;
END LOOP;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, the condition 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 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. If the condition 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.

Basic PL/SQL WHILE loop example #

The following example uses a WHILE loop statement to display five numbers from 1 to 5:

SET SERVEROUTPUT ON;

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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

Counter : 1
Counter : 2
Counter : 3
Counter : 4
Counter : 5Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example:

• First, initialize the n_counter to one.
• Second, check if n_counter is less than 5 before entering the loop.
• Third, increment the n_counter in each iteration inside the loop body. After five iterations, the n_counter is 5. Hence, the condition n_counter < 5 become false, which terminates the loop.

Using a WHILE loop with an EXIT WHEN statement #

The following example uses the EXIT WHEN statement to terminate a WHILE loop:

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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following is the output:

Counter : 1
Counter : 2Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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

Summary #

• Use the WHILE loop statement to execute a sequence of statements as long as a specified condition is TRUE.
• Use the EXIT WHEN statement to prematurely terminate the loop.