Loops and Iteration

PL/SQL provides three loop constructs for repeating code. Choosing the right one depends on whether you know how many iterations are needed upfront.

Basic LOOP

The simplest loop — runs forever until you EXIT:

DECLARE
  v_counter NUMBER := 1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE('Count: ' || v_counter);
    v_counter := v_counter + 1;
    EXIT WHEN v_counter > 5;
  END LOOP;
END;
/

EXIT WHEN condition exits the loop when the condition becomes true. You can also use a plain EXIT; inside an IF block.

WHILE Loop

Tests the condition before each iteration. If the condition is false from the start, the body never executes:

DECLARE
  v_n NUMBER := 1;
BEGIN
  WHILE v_n <= 10 LOOP
    DBMS_OUTPUT.PUT_LINE(v_n);
    v_n := v_n + 1;
  END LOOP;
END;
/

FOR Loop (Numeric)

The cleanest loop when you know the range in advance. The loop variable (i) is declared implicitly — do not declare it in DECLARE:

BEGIN
  FOR i IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
  END LOOP;
END;
/

To iterate in reverse:

FOR i IN REVERSE 1..10 LOOP ...

CONTINUE Statement

Skip the rest of the current iteration and move to the next:

BEGIN
  FOR i IN 1..10 LOOP
    CONTINUE WHEN MOD(i, 2) = 0;  -- skip even numbers
    DBMS_OUTPUT.PUT_LINE(i);
  END LOOP;
END;
/

Nested Loops

Loops can be nested. Use labels to EXIT or CONTINUE from an outer loop:

BEGIN
  <<outer_loop>>
  FOR i IN 1..3 LOOP
    FOR j IN 1..3 LOOP
      EXIT outer_loop WHEN i = 2 AND j = 2;
      DBMS_OUTPUT.PUT_LINE(i || ',' || j);
    END LOOP;
  END LOOP;
END;
/

Cursor FOR Loop (Preview)

PL/SQL's most commonly used loop — automatically fetches each row from a query:

BEGIN
  FOR emp_rec IN (SELECT first_name, salary FROM employees) LOOP
    DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ': ' || emp_rec.salary);
  END LOOP;
END;
/

The loop variable (emp_rec) is implicitly typed as %ROWTYPE for the query. The cursor is opened, fetched, and closed automatically. You'll learn more about cursors in the next lesson.

Choosing the Right Loop