Loop control

Loop statements are mainly used to repeatedly execute a sequence of operations.

The LOOP statement has the following four forms:

• Basic LOOP

• FOR LOOP

• FOR LOOP with a cursor

• WHILE LOOP

The following two statements can be used to exit a loop:

• EXIT

• EXIT WHEN

The following two statements can be used to exit the current iteration of a loop:

• CONTINUE

• CONTINUE WHEN

The EXIT, EXIT WHEN, CONTINUE, and CONTINUE WHEN statements can be used to exit a loop. These statements can be placed anywhere within the loop. It is recommended to use these statements to exit a loop instead of the GOTO statement. The EXIT statement transfers control to the end of the LOOP statement. The CONTINUE statement exits the current iteration of the loop and transfers control to the start of the next iteration. Both EXIT and CONTINUE can be followed by an optional WHEN clause, which specifies a condition.

For nested loops, it is recommended to label the loops for better readability.

Basic LOOP

The basic LOOP statement repeatedly executes a sequence of statements. The basic LOOP statement has the following syntax:

[ label ] LOOP
    statements
END LOOP [ label ];

The statements in the LOOP statement are executed repeatedly. To avoid an infinite loop, at least one statement within the LOOP and END LOOP statements must contain the EXIT statement. Otherwise, the LOOP statement will continue indefinitely.

The EXIT statement can be followed by an optional WHEN clause, which specifies a condition. When the condition is TRUE, the EXIT statement is executed, and control is transferred to the statement after the END LOOP statement.

EXIT statement

The EXIT statement is used to unconditionally exit the current loop.

Here is an example:

obclient> DECLARE
    cnt NUMBER := 0;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE ('INSIDE: ' || TO_CHAR(cnt));
        cnt := cnt + 1;
        IF cnt > 5 THEN
         EXIT;
        END IF;
     END LOOP;
     DBMS_OUTPUT.PUT_LINE('OUTSIDE: ' || TO_CHAR(cnt));
END;
/
Query OK, 0 rows affected

INSIDE: 0
INSIDE: 1
INSIDE: 2
INSIDE: 3
INSIDE: 4
INSIDE: 5
OUTSIDE: 6

EXIT WHEN statement

The EXIT WHEN statement is used to exit the current loop when the condition specified in the WHEN clause is TRUE.

Here is an example:

obclient> DECLARE
    cnt NUMBER := 0;
BEGIN
    LOOP
       DBMS_OUTPUT.PUT_LINE ('INSIDE: ' || TO_CHAR(cnt));
       cnt := cnt + 1;
       EXIT WHEN cnt >5;
     END LOOP;
     DBMS_OUTPUT.PUT_LINE('OUTSIDE: ' || TO_CHAR(cnt));
END;
/
Query OK, 0 rows affected

INSIDE: 0
INSIDE: 1
INSIDE: 2
INSIDE: 3
INSIDE: 4
INSIDE: 5
OUTSIDE: 6

CONTINUE statement

The CONTINUE statement is used to exit the current iteration of the loop. It skips the code from the CONTINUE statement to the end of the loop and continues with the next iteration from the start of the loop.

obclient> DECLARE
   cnt NUMBER := 0;
BEGIN
    LOOP
       cnt := cnt + 1;
       IF cnt < 4 THEN
         CONTINUE;
       END IF;
    DBMS_OUTPUT.PUT_LINE ('INSIDE: ' || TO_CHAR(cnt));
       EXIT WHEN cnt >5;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('OUTSIDE: ' || TO_CHAR(cnt));
END;
/
Query OK, 0 rows affected

INSIDE: 4
INSIDE: 5
INSIDE: 6
OUTSIDE: 6

CONTINUE WHEN statement

The CONTINUE WHEN statement is used to exit the current iteration of the loop. It is executed only when the condition specified in the WHEN clause is TRUE.

Here is an example:

obclient>DECLARE
   cnt NUMBER := 0;
BEGIN
    LOOP
       cnt := cnt + 1;
       CONTINUE WHEN cnt < 4;
       DBMS_OUTPUT.PUT_LINE ('INSIDE: ' || TO_CHAR(cnt));
       EXIT WHEN cnt >5;
     END LOOP;
     DBMS_OUTPUT.PUT_LINE('OUTSIDE: ' || TO_CHAR(cnt));
END;
/
Query OK, 0 rows affected

INSIDE: 4
INSIDE: 5
INSIDE: 6
OUTSIDE: 6

FOR LOOP statement

The FOR LOOP statement repeatedly executes a sequence of statements for a range of index values. The syntax is as follows:

[ label ] FOR index IN [ REVERSE ] lower_bound..upper_bound LOOP
    statements
END LOOP [ label ];

In the following example, if the REVERSE clause is not specified, the index variable starts from the lower_bound value and increments to the upper_bound value. If the REVERSE clause is specified, the index variable starts from the upper_bound value and decrements to the lower_bound value.

obclient> BEGIN
    FOR i IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE (i);
    END LOOP;
END;
/
Query OK, 0 rows affected

1
2
3
4
5

obclient>BEGIN
   FOR i IN REVERSE 1..5  LOOP
      DBMS_OUTPUT.PUT_LINE (i);
   END LOOP;
END;
/
Query OK, 0 rows affected
5
4
3
2
1

The index variable in the FOR LOOP statement is implicitly declared as a PLS_INTEGER type variable local to the loop. The statements within the loop can read the value of the index variable but cannot modify it. Statements outside the loop cannot reference the index variable. After the FOR LOOP statement is executed, the index variable is in an undefined state. The loop index is also known as the loop counter.

The upper and lower bounds of the FOR LOOP statement can be numeric text, numeric variables, or numeric expressions. If the bounds do not have numeric values, PL raises the predefined exception VALUE_ERROR.

WHILE LOOP statement

The WHILE LOOP statement is another form of loop control. It repeatedly executes a sequence of statements as long as the condition specified in the WHILE clause is TRUE. If the condition is FALSE, the loop exits. The syntax is as follows:

[ label ] WHILE condition LOOP
    statements
END LOOP [ label ];

The loop continues as long as the contidion value is TRUE, until the contidion value becomes FALSE or NULL.

obclient> DECLARE
    cnt NUMBER := 0;
BEGIN
    WHILE cnt < 3 LOOP
       DBMS_OUTPUT.PUT_LINE (cnt);
       cnt :=  cnt + 1;
    END LOOP;
END;
/
Query OK, 0 rows affected

0
1
2