Loop control

Loop statements are used to repeatedly execute a set of operations.

The LOOP statement has the following four forms:

• Basic LOOP

• FOR LOOP

• FOR LOOP for cursors

• WHILE LOOP

The following two statements are used to exit the entire loop:

• EXIT

• EXIT WHEN

The following two statements are used to exit the current iteration of the 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 in the loop. We recommend that you 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. You can add an optional WHEN clause to the EXIT and CONTINUE statements. In the WHEN clause, you can specify a condition.

If a loop contains multiple nested loops, we recommend that you label the loops to improve 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 ];

In the LOOP statement, the statements clause specifies the statements to be executed repeatedly. To avoid an infinite loop, the LOOP and END LOOP statements must contain at least one EXIT statement. Otherwise, the LOOP statement will execute repeatedly without stopping.

The EXIT statement can have an optional WHEN clause. When the condition in the WHEN clause 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 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. That is, the code from the CONTINUE statement to the end of the loop is skipped, and the loop continues from the beginning of the next iteration.

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 also used to exit the current iteration of the loop. However, the CONTINUE WHEN statement is executed only when the condition 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 is used to repeatedly execute a set 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, the index variable starts from the lower_bound value and increments to the upper_bound value when the REVERSE clause is not specified. 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 variable local to the loop. The statements in 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 undefined. The index variable is also called the loop counter.

The lower and upper bounds of the FOR LOOP statement can be a numeric literal, a numeric variable, or a numeric expression. If the bounds do not have numeric values, a predefined exception VALUE_ERROR is raised by PL.

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 is TRUE. If the condition is FALSE, the loop is exited. The syntax is as follows:

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

The loop continues as long as the condition is TRUE, until the condition is 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