Loop control

Loop statements are used to repeatedly execute a block of code.

LOOP statements are classified into the following four types:

• Basic LOOP

• FOR LOOP

• Cursor FOR LOOP

• WHILE LOOP

The following statements are available for exiting a loop:

• EXIT

• EXIT WHEN

The following statements are available for exiting the current iteration of a loop:

• CONTINUE

• CONTINUE WHEN

EXIT, EXIT WHEN, CONTINUE, and CONTINUE WHEN are used for exiting a loop and can appear anywhere inside a loop. We recommend that you use these statements instead of the GOTO statement to exit a loop. The EXIT statement transfers control to the end of the current LOOP. The CONTINUE statement exits the current iteration of a loop and transfers control to the next iteration of the current loop. Both the EXIT statement and the CONTINUE statement can have an optional WHEN clause where you can specify a commit.

If LOOP statements are nested, we recommend that you label the nested loops to improve readability.

Basic LOOP statement

A basic LOOP statement repeatedly executes a sequence of statements. The syntax of a basic LOOP statement is as follows:

[ label ] LOOP
    statements
END LOOP [ label ];

The statements specified for statements run repeatedly if you use a LOOP statement. To prevent an infinite loop, a sequence of statements between the keywords LOOP and END LOOP must contain at least one EXIT statement for terminating the loop. Otherwise, the LOOP statement will run infinitely.

The EXIT statement can have an optional WHEN clause where you can specify a condition. If the condition is TRUE, the EXIT statement is executed and control is passed to the statement after the END LOOP keyword of the loop.

EXIT statement

The EXIT statement exits the current loop unconditionally.

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 exits 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 exits the current iteration of a loop, that is, skips its following statements, and continues with the next iteration from the beginning 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 exits the current iteration of a loop 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 repeatedly runs a block of code when the loop index is in a specified range. The syntax is as follows:

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

As shown in the following example, if the REVERSE clause is not added, the value of index progressively increases from lower_bound to upper_bound. If the REVERSE clause is added, the value of index progressively decreases from upper_bound to lower_bound.

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 of the FOR LOOP statement is implicitly declared as a variable of the PLS_INTEGER type that is local to the loop. Statements inside the loop can read the value of the index, but cannot change it. Statements outside the loop cannot reference the index. After the FOR LOOP statement is executed, the index is in an undefined state. A loop index is sometimes called a loop counter.

The lower and upper bounds of a FOR LOOP statement can be numeric literals, numeric variables, or numeric expressions. If a bound does not have a numeric value, 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 when a condition is TRUE. If the condition is FALSE, the loop exits. The syntax is as follows:

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

If condition is TRUE, the loop continues until the value of condition is changed to 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