PL/SQL WHILE Loop

Introduction to PL/SQL WHILE loop statement

PL/SQL WHILE loop is a control structure that repeatedly executes a code block as long as 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.

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

Here is the 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 counter 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 to terminate.

2) 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: 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.

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.