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
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;
Note that there must be at least one executable statement between
END LOOP keywords. The sequence of statements is executed repeatedly until it reaches a loop exit. PL/SQL provides
EXIT-WHEN statements to allow you to terminate a loop.
EXITforces the loop halt execution unconditionally and passes control to the next statement after the
LOOPstatement . You typically use the
EXITstatement with the IF statement.
EXIT-WHENstatement allows the loop to terminate conditionally. When the
EXIT-WHENstatement is reached, the condition in the
WHENclause is checked. If the condition evaluates to
TRUE, the loop is terminated and control is passed to the next statement after keyword
END LOOP. If the condition evaluates to
FALSE, the loop will continue repeatedly until the condition is evaluate to
TRUE. Therefore if you don’t want to have an infinite loop, you must do something inside the loop to make condition becomes
The following illustrates the PL/SQL
LOOP statement with
LOOP ... EXIT; END LOOP;
LOOP ... EXIT WHEN condition; END LOOP;
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
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; /
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; /
As you see in two examples above, the
EXIT-WHEN can be used interchangeably. The
EXIT statement goes together with IF-THEN statement is equivalent to the
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;
The following example use 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; /
In this tutorial, you’ve learned how to use the PL/SQL
LOOP together with
EXIT-WHEN statements to construct an iterative statement in PL/SQL to execute a sequence of statements repeatedly.