# 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](../course-plan.md), [Chapter 4 — In-Memory Lookup](../course-plan.md) · Appendix entry: [CURSOR FOR LOOP](../appendix.md#cursor-for-loop) · Related: [BULK COLLECT](bulk-collect.md), [FORALL](forall.md), [FOR UPDATE](for-update.md), [OPEN FOR](open-for.md)

---

## 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](bulk-collect.md) + [FORALL](forall.md).

## 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](open-for.md)).

## 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](../course-plan.md)'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](for-update.md).

## 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](../course-plan.md). 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

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

## 6. Related

- [BULK COLLECT](bulk-collect.md) / [LIMIT](limit.md) — explicit batching when you need control.
- [FORALL](forall.md) — the write side.
- [FOR UPDATE](for-update.md) — locking batches without `WHERE CURRENT OF`.
- [OPEN FOR](open-for.md) — ref cursors, which this loop cannot iterate.