How to Use the LOOP Statement in Oracle PL/SQL?

A LOOP statement executes a sequence of statements multiple times. PL/SQL provides the following loop statements:

• Basic loop
• WHILE loop
• FOR loop
• Cursor FOR loop

To exit a loop, PL/SQL provides the following statements:

• EXIT: Forces the loop completes immediately and control passes to the statement immediately after END LOOP.
• EXIT-WHEN: The condition in the WHEN clause is evaluated. If the condition is true, the loop completes and control passes to the statement immediately after END LOOP.

To exit the current iteration of a loop, PL/SQL provides the following statements:

• CONTINUE: Forces the current iteration of the loop completes immediately and control passes to the next iteration of the loop.
• CONTINUE-WHEN: The condition in the WHEN clause is evaluated. If the condition is true, the current iteration of the loop completes and control passes to the next iteration.

To complete a PL/SQL block before it reaches its normal end, use the RETURN statement.

Using the Basic LOOP Statement

The simplest LOOP statement is the basic loop, which encloses a sequence of statements between the keywords LOOP and END LOOP, as follows:

LOOP
  sequence_of_statements
END LOOP;

With each iteration of the loop, the sequence of statements is executed, then control resumes at the top of the loop.

You can use CONTINUE and CONTINUE-WHEN statements in a basic loop, but to prevent an infinite loop, you must use an EXIT or EXIT-WHEN statement.

DECLARE

  x NUMBER := 0;

BEGIN

  LOOP

    DBMS_OUTPUT.PUT_LINE ('Inside loop:  x = ' || TO_CHAR(x));
    x := x + 1; -- Change value of condition

    EXIT WHEN x > 3;

  END LOOP;

  -- After EXIT statement, control resumes here
  DBMS_OUTPUT.PUT_LINE ('After loop:  x = ' || TO_CHAR(x));

END;

Using the FOR-LOOP Statement

Simple FOR loops iterate over a specified range of integers. The number of iterations is known before the loop is entered. A double dot (..) serves as the range operator. The range is evaluated when the FOR loop is first entered and is never re-evaluated. If the lower bound equals the higher bound, the loop body is executed once.

BEGIN
  FOR i IN 1..3 LOOP  -- assign the values 1,2,3 to i
    DBMS_OUTPUT.PUT_LINE (TO_CHAR(i));
  END LOOP;
END;

By default, iteration proceeds upward from the lower bound to the higher bound. If you use the keyword REVERSE, iteration proceeds downward from the higher bound to the lower bound. After each iteration, the loop counter is decremented. You still write the range bounds in ascending (not descending) order.

BEGIN
  FOR i IN REVERSE 1..3 LOOP  -- assign the values 3,2,1 to i
    DBMS_OUTPUT.PUT_LINE (TO_CHAR(i));
  END LOOP;
END;

The EXIT statement lets a FOR loop complete early. In Example 4-20, the loop normally executes ten times, but as soon as the FETCH statement fails to return a row, the loop completes no matter how many times it has executed.

DECLARE
   v_employees employees%ROWTYPE;  -- declare record variable
   CURSOR c1 is SELECT * FROM employees;
BEGIN
  OPEN c1; -- open the cursor before fetching
-- An entire row is fetched into the v_employees record
  FOR i IN 1..10 LOOP
    FETCH c1 INTO v_employees;
    EXIT WHEN c1%NOTFOUND;
    -- process data here
  END LOOP;
  CLOSE c1;
END;