PL/SQL LOOP

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

PL/SQL LOOP syntax

The PL/SQL LOOP statement has the following structure:

<<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 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 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.

Nested loops

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 3
Code 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.