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.
LOOP statement has the following structure:
<<label>> LOOP statements; END LOOP loop_label;
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
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 WHEN statement for terminating the loop. Otherwise, the loop becomes an infinite loop.
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 allows you to unconditionally exit the current iteration of a loop.
LOOP EXIT; END LOOP;
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;
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;
Here is the output:
Inside loop: 1 Inside loop: 2 Inside loop: 3 After loop: 4
The following explains the logic of the code:
- First, declare and initialize a variable
- Second, increase the
l_counterby one inside the loop and exit the loop if the
l_counteris greater than three. If the
l_counteris less than or equal three, show the
l_countervalue. Because the initial value of
l_counteris zero, the code in the body of the loop executes three times before it is terminated.
- Third, display the value of the
l_counterafter the loop.
EXIT WHEN statement
EXIT WHEN statement has the following syntax:
EXIT WHEN condition;
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;
Notice that this example is logically equivalent to the example that uses the
EXIT statement above.
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;
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
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.