Deep Dive — CURSOR FOR LOOP

The compiler already fetches 100 rows at a time behind this loop. That makes it fine for reading — and still row-by-row for writing.

Used in: Chapter 2 — Bulk Chapter 4 — In-Memory Lookup
Appendix entry: CURSOR FOR LOOP
Related: BULK COLLECT FORALL FOR UPDATE OPEN FOR

1. What It Is and When

FOR rec IN (query) LOOP ... END LOOP opens the cursor, fetches, declares the record, and closes the cursor — all implicitly, including on exceptions. Since 10g, with PLSQL_OPTIMIZE_LEVEL >= 2 (the default), the compiler rewrites the fetch as a BULK COLLECT ... LIMIT 100 under the hood: one context switch per 100 rows, not per row.

That makes it the right tool for read-only loops and for loops whose per-row work is PL/SQL-only. It is the wrong tool the moment the body contains DML — that DML is still one statement per row. Then it is BULK COLLECT + FORALL.

2. Syntax

SQL

-- inline query
FOR <rec> IN (<select_statement>) LOOP
  ... <rec>.<column> ...
END LOOP;

-- declared cursor, optionally with parameters
CURSOR <c> (<p1> <type>, ...) IS <select_statement>;
FOR <rec> IN <c>(<arg1>, ...) LOOP ... END LOOP;

<rec> is declared implicitly as <c>%ROWTYPE; it does not exist outside the loop.
EXIT / EXIT WHEN leave the loop and close the cursor. %NOTFOUND is not usable inside — the loop handles that.
Not available for ref cursors (SYS_REFCURSOR) — those need an explicit OPEN/FETCH/CLOSE (OPEN FOR).

3. Examples on the Course Schema

3a. Read-only — the right use

SQL

DECLARE
  TYPE t_tier_map IS TABLE OF customers.tier%TYPE INDEX BY PLS_INTEGER;
  l_tier t_tier_map;
BEGIN
  FOR r IN (SELECT customer_id, tier FROM customers WHERE tier = 'VIP') LOOP
    l_tier(r.customer_id) := r.tier;                 -- PL/SQL only, no SQL per row
  END LOOP;
END;

500K rows, ~5,000 fetches. Good enough; a manual BULK COLLECT LIMIT 10000 would save ~4,950 switches out of the job's millions — not where the time is.

3b. Parameterized cursor

SQL

DECLARE
  CURSOR c_orders (p_customer NUMBER, p_from DATE) IS
    SELECT order_id, amount FROM orders
    WHERE  customer_id = p_customer AND order_date >= p_from;
BEGIN
  FOR r IN c_orders(:id, ADD_MONTHS(SYSDATE, -12)) LOOP
    DBMS_OUTPUT.PUT_LINE(r.order_id || ' ' || r.amount);
  END LOOP;
END;

Cursor parameters are binds: the same cursor for every customer, one parse.

3c. Seeing the 100-row prefetch

SQL

BEGIN
  FOR r IN (SELECT /*+ prefetch_demo */ order_id FROM orders WHERE customer_id = :id) LOOP
    NULL;
  END LOOP;
END;
/
SELECT executions, fetches, rows_processed
FROM   v$sql
WHERE  sql_text LIKE 'SELECT /*+ prefetch_demo */%';
-- e.g. rows_processed = 2,340, fetches = 24  (≈ rows / 100)

Set PLSQL_OPTIMIZE_LEVEL = 1 and recompile, and fetches becomes 2,341.

3d. The wrong use — DML in the body

SQL

FOR r IN (SELECT order_id, amount FROM orders WHERE status = 'NEW') LOOP
  UPDATE orders SET amount = pricing_pkg.reprice(r.order_id, r.amount)   -- one UPDATE per row
  WHERE  order_id = r.order_id;
END LOOP;

The read side is bulk (100/fetch); the write side is 500M individual statements. This is exactly Chapter 2's ❌ example. Rewrite as BULK COLLECT ... LIMIT + FORALL.

3e. Nested cursor loops — the N × M trap

SQL

FOR c IN (SELECT customer_id FROM customers WHERE tier = 'VIP') LOOP          -- 500K
  FOR o IN (SELECT order_id FROM orders WHERE customer_id = c.customer_id) LOOP -- 500K queries
    ...
  END LOOP;
END LOOP;

The inner cursor is opened, parsed (soft), executed and closed 500K times. This is a join written in PL/SQL:

SQL

FOR r IN (SELECT c.customer_id, o.order_id
          FROM   customers c JOIN orders o ON o.customer_id = c.customer_id
          WHERE  c.tier = 'VIP') LOOP
  ...
END LOOP;

3f. WHERE CURRENT OF — and its COMMIT problem

SQL

DECLARE
  CURSOR c IS SELECT order_id FROM orders WHERE status = 'NEW' FOR UPDATE;
BEGIN
  FOR r IN c LOOP
    UPDATE orders SET status = 'DONE' WHERE CURRENT OF c;
    -- COMMIT here → ORA-01002: fetch out of sequence on the next iteration
  END LOOP;
  COMMIT;
END;

WHERE CURRENT OF needs FOR UPDATE, and FOR UPDATE cursors die on COMMIT. Batch processing with locks uses a fresh SELECT ... FOR UPDATE SKIP LOCKED per batch instead — FOR UPDATE.

4. What Breaks at Scale

Reads are fine, writes are not. The prefetch makes a read-only loop over 500M rows cost ~5M switches — acceptable. Any DML in the body puts 500M switches back.
No control over batch size or commit points. You cannot commit every N rows cleanly (the loop does not expose the batch), and you cannot tune the 100. When either matters, switch to an explicit cursor with LIMIT.
A query in the body (a SELECT INTO lookup) is a query per row — Chapter 4. The outer loop being bulk does not help the inner statement.
Memory is flat (100 rows) — that is the one advantage over a badly sized BULK COLLECT.

5. Pitfalls

DML inside the loop — the loop is bulk, the DML is not.
SELECT INTO inside the loop — N+1 queries; load a lookup once or join.
Nested cursor loops — a join spelled out in PL/SQL.
COMMIT inside a FOR UPDATE cursor loop — ORA-01002.
SELECT * in the cursor and code that depends on column positions — adding a column to the table changes r.
Assuming the prefetch is always on — PLSQL_OPTIMIZE_LEVEL < 2 (rare, but seen on old installs) makes it row-by-row.
Using the loop record after END LOOP — it does not exist there; copy what you need.