PL/SQL LOOP

Summary: in this tutorial, you will learn how to use basic PL/SQL LOOP statement to execute a block of code repeatedly until a condition is met.

Introduction to PL/SQL LOOP statement

The PL/SQL LOOP statement is a control structure that repeatedly executes a block of code until a specific condition is met or until you manually exit the loop.

Here’s the syntax of the PL/SQL LOOP statement:

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

This structure is the most basic of all the loop constructs including FOR LOOP and WHILE LOOP. This basic LOOP statement consists of a LOOP keyword, a body of executable code, and the END LOOP keywords.

The LOOP statement executes the statements in its body and returns control to the top of the loop. Typically, the body of the loop contains at least one EXIT or EXIT WHEN statement for terminating the loop. Otherwise, the loop becomes an infinite loop.

The LOOP statement can have an optional label that appears at the beginning and the end of the statement.

It is a good practice to use the LOOP statement when:

• You want to execute the loop body at least once.
• You are not sure of the number of times you want the loop to execute.

EXIT statement

The EXIT statement allows you to unconditionally exit the current iteration of a loop.

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

Typically, you use the EXIT statement with an IF statement to terminate a loop when a condition is true:

LOOP
    IF condition THEN
        EXIT;
    END IF;
END LOOP;
Code language: SQL (Structured Query Language) (sql)

The following example illustrates how to use the LOOP statement to execute a sequence of code and EXIT statement to terminate the loop.

DECLARE
  l_counter NUMBER := 0;
BEGIN
  LOOP
    l_counter := l_counter + 1;
    IF l_counter > 3 THEN
      EXIT;
    END IF;
    dbms_output.put_line( 'Inside loop: ' || l_counter )  ;
  END LOOP;
  -- control resumes here after EXIT
  dbms_output.put_line( 'After loop: ' || l_counter );
END;Code language: SQL (Structured Query Language) (sql)

Here is the output:

Inside loop: 1
Inside loop: 2
Inside loop: 3
After loop: 4
Code language: SQL (Structured Query Language) (sql)

The following explains the logic of the code:

• First, declare and initialize a variable l_counter to zero.
• Second, increase the l_counter by one inside the loop and exit the loop if the l_counter is greater than three. If the l_counter is less than or equal to three, show the l_counter value. Because the initial value of  l_counter is zero, the code in the body of the loop executes three times before it is terminated.
• Third, display the value of the l_counter after the loop.

EXIT WHEN statement

The EXIT WHEN statement has the following syntax:

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

The EXIT WHEN statement exits the current iteration of a loop when the condition in the WHEN clause is TRUE. Essentially, the EXIT WHEN statement is a combination of an EXIT and an IF THEN statement.

Each time the control reaches the EXIT WHEN statement, the condition is evaluated. If the condition evaluates to TRUE, then the loop terminates. Otherwise, the EXIT WHEN clause does nothing. Inside the loop body, you must make the condition TRUE at some point to prevent an infinite loop.

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

DECLARE
  l_counter NUMBER := 0;
BEGIN
  LOOP
    l_counter := l_counter + 1;
    EXIT WHEN l_counter > 3;
    dbms_output.put_line( 'Inside loop: ' || l_counter ) ;
  END LOOP;

  -- control resumes here after EXIT
  dbms_output.put_line( 'After loop: ' || l_counter );
END;Code language: SQL (Structured Query Language) (sql)

Notice that this example is logically equivalent to the example that uses the EXIT statement above.

Constructing nested loops using PL/SQL LOOP statements

It is possible to nest a LOOP statement within another LOOP statement as shown in the following example:

DECLARE
  l_i NUMBER := 0;
  l_j NUMBER := 0;
BEGIN
  <<outer_loop>>
  LOOP
    l_i := l_i + 1;
    EXIT outer_loop WHEN l_i > 2;    
    dbms_output.put_line('Outer counter ' || l_i);
    -- reset inner counter
    l_j := 0;
      <<inner_loop>> LOOP
      l_j := l_j + 1;
      EXIT inner_loop WHEN l_j > 3;
      dbms_output.put_line(' Inner counter ' || l_j);
    END LOOP inner_loop;
  END LOOP outer_loop;
END;Code language: SQL (Structured Query Language) (sql)

Here is the output:

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

In this tutorial, you have learned how to use the PL/SQL LOOP statement to repeatedly execute a block of code until a condition is met.