PL/SQL LOOP Statement

Summary: in this tutorial, you will learn how to use PL/SQL LOOP statement to execute a sequence of statements repeatedly.

Introduction to PL/SQL LOOP Statement

PL/SQL LOOP statement is an iterative control statement that allows you to execute a sequence of statements repeatedly like WHILE and FOR loop.

The simplest form of the LOOP statement consists of the LOOP keyword, a sequence of statements and the END LOOP keywords as shown below:

LOOP
   sequence_of_statements;
END LOOP;Code language: SQL (Structured Query Language) (sql)

Note that there must be at least one executable statement between LOOP and END LOOP keywords. The sequence of statements is executed repeatedly until it reaches a loop exit. PL/SQL provides EXIT and EXIT-WHEN statements to allow you to terminate a loop.

• The EXIT forces the loop halts execution unconditionally and passes control to the next statement after the LOOP statement. You typically use the EXIT statement with the IF statement.
• The EXIT-WHEN statement allows the loop to terminate conditionally. When the EXIT-WHEN statement is reached, the condition in the WHEN clause is checked. If the condition evaluates to TRUE, the loop is terminated and control  is passed to the next statement after the keyword END LOOP. If the condition evaluates to FALSE, the loop will continue repeatedly until the condition evaluates to TRUE. Therefore, if you don’t want to have an infinite loop, you must do something inside the loop to make condition becomes TRUE

The following illustrates the PL/SQL LOOP statement with EXIT and EXIT-WHEN statements:

LOOP
   ...
   EXIT;
END LOOP;Code language: SQL (Structured Query Language) (sql)

LOOP
   ...
   EXIT WHEN condition;
END LOOP;Code language: SQL (Structured Query Language) (sql)

Examples of PL/SQL LOOP Statement

Example of PL/SQL LOOP with EXIT Statement

In this example, we declare a counter. Inside the loop, we add 1 to the counter and display it. If the value of the counter is equal 5, we use the EXIT statement to terminate the loop.

The following is example of using PL/SQL LOOP statement with EXIT:

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE n_counter NUMBER := 0;
BEGIN
  LOOP
    n_counter := n_counter + 1;
    DBMS_OUTPUT.PUT_LINE(n_counter);
    IF n_counter = 5 THEN
      EXIT;
    END IF;
  END LOOP;
END;
/Code language: SQL (Structured Query Language) (sql)

Example of PL/SQL LOOP with EXIT-WHEN Statement

We’ll use the same counter example above. However, instead of using the IF-THEN and EXIT statements, we use EXIT-WHEN to terminate the loop. The code example is listed as follows:

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE n_counter NUMBER := 0;
BEGIN
  LOOP
    n_counter := n_counter + 1;
    DBMS_OUTPUT.PUT_LINE(n_counter);
    EXIT WHEN n_counter = 5;
  END LOOP;
END;
/Code language: SQL (Structured Query Language) (sql)

As you see in two examples above, the EXIT and EXIT-WHEN can be used interchangeably. The EXIT statement goes together with IF-THEN statement is equivalent to the EXIT-WHEN statement.

Loop Label

A loop can have an optional label that is an undeclared identifier enclosed by double angle brackets <<label>>. The loop label appears at the beginning and also at the end of the PL/SQL LOOP statement. A loop label is used to qualify the name of the loop counter variable when a loop is nested inside another loop.

The following illustrates the syntax of the PL/SQL loop statement with a label:

<<label>>
LOOP
   sequence_of_statements;
END LOOP label;Code language: SQL (Structured Query Language) (sql)

The following example uses loop label with a nested loop:

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
  n_i NUMBER := 0;
  n_j NUMBER := 0;
BEGIN
  << outer_loop >>
  LOOP
    n_i := n_i + 1;
    EXIT WHEN n_i = 2;
    << inner_loop >>
    LOOP
      n_j := n_j + 1;
      EXIT WHEN n_j = 5;
      DBMS_OUTPUT.PUT_LINE('Outer loop counter ' || n_i);
      DBMS_OUTPUT.PUT_LINE('Inner loop counter ' || n_j);
    END LOOP inner_loop;
  END LOOP outer_loop;
END;
/Code language: SQL (Structured Query Language) (sql)

In this tutorial, you’ve learned how to use the PL/SQL LOOP together with EXIT and EXIT-WHEN statements to construct an iterative statement in PL/SQL to execute a sequence of statements repeatedly.