SQL 88 🛢️ Cursors and Iterative Processing - KandZ

⏱️ Estimated read: 18 min | |

SQL is a set-based language. A SELECT returns the whole result at once, and the UPDATE affects every matching row in a single statement. Some tasks do not fit that model. A batch process that calls an external API per row, a data migration that must process the rows in a specific order with the state carried between them, a recursive walk that builds the path as it descends. The cursor is the mechanism for the row-by-row processing, and it is also the anti-pattern that the database engine is designed to avoid. This chapter covers the cursor, the loop that drives it, and the discipline that keeps it out of the hot path.

Key point: A cursor is a pointer to a result set. The DECLARE defines the query, the OPEN executes it, the FETCH reads the next row, and the CLOSE releases it. The loop around the FETCH is the iteration. The whole structure is the procedural SQL’s way of saying “for each row.”

Why cursors exist

The set-based problem. The UPDATE ... WHERE is the set-based operation. It is fast, and the optimizer plans it. The task that cannot be expressed as a set is the exception, and the cursor is the fallback.

The external-call problem. The procedure must call the external API for each row. The SELECT returns the rows, and the loop calls the API. The cursor is the driver.

The stateful problem. The processing carries the state from the row to the row. The running total, the previous value, the path in the tree. The set-based query cannot carry the state, and the loop can.

The ordered problem. The rows must be processed in the specific order. The window function and the ORDER BY in the cursor give the order, and the loop respects it.

The batch problem. The rows must be processed in the chunks. The cursor reads the chunk, the loop processes it, and the transaction commits per chunk. The batch is the unit of the work.

The migration problem. The legacy data must be transformed and inserted. The cursor reads the legacy rows, and the loop applies the transformation. The one-off script is the classic use.

The recursive problem. The tree must be walked. The WITH RECURSIVE is the set-based way, and the cursor is the procedural way when the recursion is complex.

a. The cursor lifecycle

A cursor has four steps: the DECLARE, the OPEN, the FETCH, and the CLOSE. The DECLARE defines the query, the OPEN executes it and positions the pointer before the first row, the FETCH reads the next row, and the CLOSE releases the resources.

The PostgreSQL explicit cursor

CREATE OR REPLACE PROCEDURE process_pending_orders()
LANGUAGE plpgsql
AS $$
DECLARE
    order_cursor CURSOR FOR
        SELECT id, customer_id, total
        FROM orders
        WHERE status = 'pending'
        ORDER BY created_at;

    r RECORD;
BEGIN
    OPEN order_cursor;

    LOOP
        FETCH order_cursor INTO r;
        EXIT WHEN NOT FOUND;

        -- The processing.
        UPDATE orders SET status = 'processed' WHERE id = r.id;
    END LOOP;

    CLOSE order_cursor;
END;
$$;

The DECLARE ... CURSOR FOR defines the query, the OPEN executes it, the FETCH ... INTO reads the next row, and the EXIT WHEN NOT FOUND ends the loop.

The FETCH directions

The FETCH accepts the direction. The default is NEXT.

FETCH NEXT FROM order_cursor INTO r;
FETCH PRIOR FROM order_cursor INTO r;
FETCH FIRST FROM order_cursor INTO r;
FETCH LAST FROM order_cursor INTO r;
FETCH ABSOLUTE 5 FROM order_cursor INTO r;
FETCH RELATIVE 2 FROM order_cursor INTO r;
FETCH ALL FROM order_cursor INTO r;

The scrollable cursor supports the directions. The non-scrollable cursor supports only the NEXT.

The cursor attributes

The cursor has the attributes that report the state.

Attribute	Meaning
`FOUND`	The last `FETCH` returned a row
`NOT FOUND`	The last `FETCH` returned no row
`ROWCOUNT`	The number of the rows fetched
`ISOPEN`	The cursor is open

The EXIT WHEN NOT FOUND uses the FOUND attribute.

The SQL Server cursor

DECLARE order_cursor CURSOR FOR
    SELECT id, customer_id, total
    FROM orders
    WHERE status = 'pending'
    ORDER BY created_at;

DECLARE @id INT, @customer_id INT, @total DECIMAL(12,2);

OPEN order_cursor;

FETCH NEXT FROM order_cursor INTO @id, @customer_id, @total;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- The processing.
    UPDATE orders SET status = 'processed' WHERE id = @id;

    FETCH NEXT FROM order_cursor INTO @id, @customer_id, @total;
END;

CLOSE order_cursor;
DEALLOCATE order_cursor;

The @@FETCH_STATUS is the SQL Server attribute. The 0 means the success, and the -1 means the end.

The MySQL cursor

DELIMITER //

CREATE PROCEDURE process_pending_orders()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id INT;
    DECLARE v_total DECIMAL(12,2);

    DECLARE order_cursor CURSOR FOR
        SELECT id, total FROM orders WHERE status = 'pending';

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN order_cursor;

    read_loop: LOOP
        FETCH order_cursor INTO v_id, v_total;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- The processing.
        UPDATE orders SET status = 'processed' WHERE id = v_id;
    END LOOP;

    CLOSE order_cursor;
END //

DELIMITER ;

The CONTINUE HANDLER FOR NOT FOUND sets the done flag. The LEAVE exits the loop.

The cursor lifecycle table

Step	PostgreSQL	SQL Server	MySQL
`DECLARE`	`DECLARE c CURSOR FOR ...`	`DECLARE c CURSOR FOR ...`	`DECLARE c CURSOR FOR ...`
`OPEN`	`OPEN c`	`OPEN c`	`OPEN c`
`FETCH`	`FETCH c INTO r`	`FETCH NEXT FROM c INTO @v`	`FETCH c INTO v`
`CLOSE`	`CLOSE c`	`CLOSE c`	`CLOSE c`
The end check	`EXIT WHEN NOT FOUND`	`@@FETCH_STATUS = 0`	The handler

b. The cursor in the loop

The FOR ... IN SELECT loop is the implicit cursor. It is shorter, and it is the preferred form when the explicit control is not needed.

The PostgreSQL FOR loop

CREATE OR REPLACE PROCEDURE process_pending_orders()
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT id, total FROM orders WHERE status = 'pending' ORDER BY created_at
    LOOP
        UPDATE orders SET status = 'processed' WHERE id = r.id;
    END LOOP;
END;
$$;

The FOR r IN SELECT declares the cursor, opens it, fetches the rows, and closes it. The loop body runs per row.

The implicit versus the explicit

Aspect	The implicit (`FOR ... IN`)	The explicit (`CURSOR`)
The declaration	Inline	Separate
The open	Automatic	Manual
The close	Automatic	Manual
The control	Limited	Full
The re-open	No	Yes
The scroll	No	Yes
The performance	Same	Same

The implicit form is the common case. The explicit form is the rare case where the re-open or the scroll is needed.

The cursor with the parameters

The cursor query can reference the procedure’s parameters.

CREATE OR REPLACE PROCEDURE process_customer_orders(p_customer_id INT)
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT id FROM orders WHERE customer_id = p_customer_id
    LOOP
        -- The processing.
    END LOOP;
END;
$$;

The p_customer_id is the parameter, and the cursor query reads it.

The cursor with the bound parameters

The OPEN ... USING binds the parameters to the cursor’s placeholders.

DECLARE
    order_cursor CURSOR (p_status TEXT) FOR
        SELECT id FROM orders WHERE status = p_status;
BEGIN
    OPEN order_cursor('pending');
    -- ...
    CLOSE order_cursor;
END;

The cursor declares the parameters, and the OPEN supplies the values.

The cursor with the update

The WHERE CURRENT OF updates the current row of the cursor.

DECLARE
    order_cursor CURSOR FOR
        SELECT id, total FROM orders WHERE status = 'pending' FOR UPDATE;
BEGIN
    OPEN order_cursor;
    LOOP
        FETCH order_cursor INTO r;
        EXIT WHEN NOT FOUND;

        UPDATE orders SET status = 'processed' WHERE CURRENT OF order_cursor;
    END LOOP;
    CLOSE order_cursor;
END;

The FOR UPDATE locks the rows, and the WHERE CURRENT OF targets the current row.

The cursor for the batch

CREATE OR REPLACE PROCEDURE process_in_batches(p_batch_size INT DEFAULT 1000)
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
    v_count INT := 0;
BEGIN
    FOR r IN SELECT id FROM orders WHERE status = 'pending' LOOP
        UPDATE orders SET status = 'processed' WHERE id = r.id;
        v_count := v_count + 1;

        IF v_count % p_batch_size = 0 THEN
            COMMIT;
        END IF;
    END LOOP;

    COMMIT;
END;
$$;

The COMMIT per batch flushes the work, and the long-running transaction is avoided.

c. The performance and the anti-patterns

The cursor is the row-by-row processing, and the database is optimized for the set-based. The cursor is the anti-pattern when the set-based operation exists.

The RBAR problem

The RBAR is the “row by agonizing row.” The loop updates one row at a time, and the set-based UPDATE updates all the rows at once.

-- ❌ The RBAR.
FOR r IN SELECT id FROM orders WHERE status = 'pending' LOOP
    UPDATE orders SET status = 'processed' WHERE id = r.id;
END LOOP;

-- ✅ The set-based.
UPDATE orders SET status = 'processed' WHERE status = 'pending';

The set-based is the one statement, and the optimizer plans it. The RBAR is the N statements, and the overhead is per row.

The N+1 problem

The loop runs a query per iteration.

-- ❌ The N+1.
FOR r IN SELECT id, customer_id FROM orders LOOP
    SELECT name INTO customer_name FROM customers WHERE id = r.customer_id;
END LOOP;

-- ✅ The join.
FOR r IN SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id LOOP
    -- ...
END LOOP;

The join is the one query, and the loop reads the joined result.

The cursor for the bulk update

-- ❌ The cursor for the bulk update.
FOR r IN SELECT id FROM orders WHERE created_at < '2020-01-01' LOOP
    DELETE FROM orders WHERE id = r.id;
END LOOP;

-- ✅ The set-based delete.
DELETE FROM orders WHERE created_at < '2020-01-01';

The set-based delete is the one statement.

The forward-only versus the scroll

The forward-only cursor is the default and the fastest. The scroll cursor is the alternative, and it buffers the result set.

Type	The FETCH	The cost
Forward-only	`NEXT` only	The low
Scroll	Any direction	The buffered
Insensitive	The stable snapshot	The buffered
Sensitive	The live data	The locking

The forward-only is the default, and the scroll is the rare case.

The cursor for the reporting

The cursor is not the reporting tool. The window function is the set-based alternative.

-- ❌ The cursor for the running total.
FOR r IN SELECT id, amount FROM transactions ORDER BY created_at LOOP
    running_total := running_total + r.amount;
    INSERT INTO report (id, running_total) VALUES (r.id, running_total);
END LOOP;

-- ✅ The window function.
INSERT INTO report (id, running_total)
SELECT id, SUM(amount) OVER (ORDER BY created_at) FROM transactions;

The window function is the one statement, and the engine computes the running total.

The anti-pattern table

Anti-pattern	The set-based alternative
The RBAR update	The `UPDATE ... WHERE`
The N+1 query	The `JOIN`
The cursor delete	The `DELETE ... WHERE`
The cursor insert	The `INSERT ... SELECT`
The cursor running total	The window function
The cursor distinct	The `SELECT DISTINCT`

The cursor use cases

Use case	The reason
The external API call	The per-row call
The stateful batch	The state between the rows
The migration script	The one-off transform
The complex recursion	The procedural walk
The dynamic SQL	The per-row statement
The `WHERE CURRENT OF`	The positioned update

Complete Example Session

-- ============================================
-- PART 1: THE EXPLICIT CURSOR
-- ============================================

CREATE OR REPLACE PROCEDURE process_pending_orders()
LANGUAGE plpgsql
AS $$
DECLARE
    order_cursor CURSOR FOR
        SELECT id, customer_id, total
        FROM orders
        WHERE status = 'pending'
        ORDER BY created_at;

    r RECORD;
BEGIN
    OPEN order_cursor;

    LOOP
        FETCH order_cursor INTO r;
        EXIT WHEN NOT FOUND;

        UPDATE orders SET status = 'processed' WHERE id = r.id;
    END LOOP;

    CLOSE order_cursor;
END;
$$;

-- ============================================
-- PART 2: THE IMPLICIT CURSOR
-- ============================================

CREATE OR REPLACE PROCEDURE process_orders()
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT id FROM orders WHERE status = 'pending' LOOP
        UPDATE orders SET status = 'processed' WHERE id = r.id;
    END LOOP;
END;
$$;

-- ============================================
-- PART 3: THE SQL SERVER CURSOR
-- ============================================

DECLARE order_cursor CURSOR FOR
    SELECT id FROM orders WHERE status = 'pending';

DECLARE @id INT;

OPEN order_cursor;
FETCH NEXT FROM order_cursor INTO @id;

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE orders SET status = 'processed' WHERE id = @id;
    FETCH NEXT FROM order_cursor INTO @id;
END;

CLOSE order_cursor;
DEALLOCATE order_cursor;

-- ============================================
-- PART 4: THE MYSQL CURSOR
-- ============================================

DELIMITER //

CREATE PROCEDURE process_orders()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id INT;

    DECLARE order_cursor CURSOR FOR
        SELECT id FROM orders WHERE status = 'pending';

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN order_cursor;

    read_loop: LOOP
        FETCH order_cursor INTO v_id;
        IF done THEN LEAVE read_loop; END IF;
        UPDATE orders SET status = 'processed' WHERE id = v_id;
    END LOOP;

    CLOSE order_cursor;
END //

DELIMITER ;

-- ============================================
-- PART 5: THE WHERE CURRENT OF
-- ============================================

DECLARE
    order_cursor CURSOR FOR
        SELECT id FROM orders WHERE status = 'pending' FOR UPDATE;
BEGIN
    OPEN order_cursor;
    LOOP
        FETCH order_cursor INTO r;
        EXIT WHEN NOT FOUND;
        UPDATE orders SET status = 'processed' WHERE CURRENT OF order_cursor;
    END LOOP;
    CLOSE order_cursor;
END;

-- ============================================
-- PART 6: THE BATCH COMMIT
-- ============================================

DECLARE
    r RECORD;
    v_count INT := 0;
BEGIN
    FOR r IN SELECT id FROM orders WHERE status = 'pending' LOOP
        UPDATE orders SET status = 'processed' WHERE id = r.id;
        v_count := v_count + 1;

        IF v_count % 1000 = 0 THEN
            COMMIT;
        END IF;
    END LOOP;

    COMMIT;
END;

-- ============================================
-- PART 7: THE SCROLLABLE CURSOR
-- ============================================

DECLARE
    scroll_cursor SCROLL CURSOR FOR
        SELECT id FROM orders ORDER BY id;
BEGIN
    OPEN scroll_cursor;

    FETCH LAST FROM scroll_cursor INTO r;
    FETCH PRIOR FROM scroll_cursor INTO r;
    FETCH ABSOLUTE 5 FROM scroll_cursor INTO r;

    CLOSE scroll_cursor;
END;

-- ============================================
-- PART 8: THE BOUND PARAMETERS
-- ============================================

DECLARE
    order_cursor CURSOR (p_status TEXT) FOR
        SELECT id FROM orders WHERE status = p_status;
BEGIN
    OPEN order_cursor('pending');
    -- ...
    CLOSE order_cursor;
END;

-- ============================================
-- PART 9: THE SET-BASED ALTERNATIVE
-- ============================================

-- ❌ The RBAR.
FOR r IN SELECT id FROM orders WHERE status = 'pending' LOOP
    UPDATE orders SET status = 'processed' WHERE id = r.id;
END LOOP;

-- ✅ The set-based.
UPDATE orders SET status = 'processed' WHERE status = 'pending';

-- ============================================
-- PART 10: THE COMPLETE PATTERN
-- ============================================

-- 1. Use the cursor only when the set-based is impossible.
-- 2. Use the FOR ... IN SELECT for the implicit cursor.
-- 3. Use the explicit cursor for the re-open or the scroll.
-- 4. Use the FETCH ... INTO to read the row.
-- 5. Use the EXIT WHEN NOT FOUND to end the loop.
-- 6. Use the WHERE CURRENT OF for the positioned update.
-- 7. Use the batch commit for the long process.
-- 8. Avoid the RBAR when the set-based exists.
-- 9. Avoid the N+1 when the join exists.
-- 10. Prefer the window function for the running total.

The ten parts showed the explicit cursor, the implicit cursor, the SQL Server cursor, the MySQL cursor, the WHERE CURRENT OF, the batch commit, the scrollable cursor, the bound parameters, the set-based alternative, and the complete pattern.

Quick Reference

The Cursor Lifecycle

Step	Purpose
`DECLARE`	Define the query
`OPEN`	Execute the query
`FETCH`	Read the next row
`CLOSE`	Release the resources

The Cursor Attributes

Attribute	Meaning
`FOUND`	The last fetch returned a row
`NOT FOUND`	The last fetch returned no row
`ROWCOUNT`	The number fetched
`ISOPEN`	The cursor is open

The FETCH Directions

Direction	Purpose
`NEXT`	The next row
`PRIOR`	The previous row
`FIRST`	The first row
`LAST`	The last row
`ABSOLUTE n`	The nth row
`RELATIVE n`	The n rows from the current
`ALL`	The remaining rows

The Cursor Types

Type	The FETCH	The cost
Forward-only	`NEXT` only	Low
Scroll	Any direction	Buffered
Insensitive	Stable snapshot	Buffered
Sensitive	Live data	Locking

The Set-Based Alternatives

The cursor	The set-based
The RBAR update	The `UPDATE ... WHERE`
The N+1 query	The `JOIN`
The cursor delete	The `DELETE ... WHERE`
The cursor insert	The `INSERT ... SELECT`
The running total	The window function

Best Practices

✅ Do This:

-- Use the implicit cursor for the common case.
FOR r IN SELECT id FROM orders WHERE status = 'pending' LOOP
    ...
END LOOP;  -- ✅

-- Use the explicit cursor for the re-open.
DECLARE c CURSOR FOR SELECT ...;
OPEN c;
-- ...
CLOSE c;  -- ✅

-- Use the EXIT WHEN NOT FOUND.
LOOP
    FETCH c INTO r;
    EXIT WHEN NOT FOUND;
    ...
END LOOP;  -- ✅

-- Use the WHERE CURRENT OF.
UPDATE orders SET status = 'processed' WHERE CURRENT OF order_cursor;  -- ✅

-- Use the batch commit for the long process.
IF v_count % 1000 = 0 THEN COMMIT; END IF;  -- ✅

-- Use the set-based when it exists.
UPDATE orders SET status = 'processed' WHERE status = 'pending';  -- ✅

❌ Don’t Do This:

-- Don't use the cursor for the bulk update.
FOR r IN SELECT id FROM orders LOOP
    UPDATE orders SET status = 'processed' WHERE id = r.id;  -- ❌ The RBAR
END LOOP;

-- Don't use the cursor for the N+1.
FOR r IN SELECT id, customer_id FROM orders LOOP
    SELECT name FROM customers WHERE id = r.customer_id;  -- ❌ The N+1
END LOOP;

-- Don't forget the CLOSE.
OPEN c;
-- no CLOSE  -- ❌ The resource leak

-- Don't forget the EXIT WHEN NOT FOUND.
LOOP
    FETCH c INTO r;  -- ❌ The infinite loop
END LOOP;

-- Don't use the cursor for the running total.
-- Use the window function.  -- ❌

-- Don't use the scroll cursor when the forward-only works.
DECLARE c SCROLL CURSOR FOR ...;  -- ⚠️ The buffered cost

Common Pitfalls

Pitfall	Why It Happens	Fix
The infinite loop	No `EXIT WHEN NOT FOUND`	Add the exit
The resource leak	No `CLOSE`	Add the close
The slow process	The RBAR	Use the set-based
The N+1	The query per iteration	Use the join
The long transaction	No batch commit	Commit per batch
The scroll cost	The scroll cursor	Use the forward-only
The positioned update fails	No `FOR UPDATE`	Add the lock

Real-World Examples

1. The Explicit Cursor

DECLARE c CURSOR FOR SELECT id FROM orders;
OPEN c;
LOOP FETCH c INTO r; EXIT WHEN NOT FOUND; END LOOP;
CLOSE c;

2. The Implicit Cursor

FOR r IN SELECT id FROM orders LOOP ... END LOOP;

3. The SQL Server Cursor

FETCH NEXT FROM c INTO @id;
WHILE @@FETCH_STATUS = 0 BEGIN ... FETCH NEXT FROM c INTO @id; END;

4. The MySQL Cursor

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

5. The WHERE CURRENT OF

UPDATE orders SET status = 'processed' WHERE CURRENT OF c;

6. The Batch Commit

IF v_count % 1000 = 0 THEN COMMIT; END IF;

7. The Scrollable Cursor

DECLARE c SCROLL CURSOR FOR SELECT id FROM orders;
FETCH LAST FROM c INTO r;

8. The Bound Parameters

DECLARE c CURSOR (p_status TEXT) FOR SELECT ... WHERE status = p_status;
OPEN c('pending');

9. The Set-Based

UPDATE orders SET status = 'processed' WHERE status = 'pending';

10. The Complete Pattern

FOR r IN SELECT id FROM orders WHERE status = 'pending' LOOP ... END LOOP;

Visual

The Cursor Lifecycle

┌────────────────────────────────────────────────────────────┐
│  THE CURSOR LIFECYCLE                                      │
│                                                            │
│  DECLARE c CURSOR FOR SELECT ...;                          │
│    │                                                       │
│    v                                                       │
│  OPEN c;                                                   │
│    │                                                       │
│    v                                                       │
│  ┌──────────────────────────────────────────────────┐     │
│  │  LOOP                                             │     │
│  │    FETCH c INTO r;                                │     │
│  │    EXIT WHEN NOT FOUND;                           │     │
│  │    -- process the row                             │     │
│  │  END LOOP;                                        │     │
│  └──────────────────────────────────────────────────┘     │
│    │                                                       │
│    v                                                       │
│  CLOSE c;                                                  │
│                                                            │
│  The DECLARE defines the query. The OPEN executes it.      │
│  The FETCH reads the row. The CLOSE releases.              │
└────────────────────────────────────────────────────────────┘

The RBAR vs the Set

┌────────────────────────────────────────────────────────────┐
│  THE RBAR vs THE SET                                       │
│                                                            │
│  RBAR (the cursor)                                         │
│  ┌──────────────────────────────────────────────────┐     │
│  │  FOR r IN SELECT id FROM orders LOOP              │     │
│  │    UPDATE orders SET status = 'processed'         │     │
│  │    WHERE id = r.id;                               │     │
│  │  END LOOP;                                        │     │
│  │                                                   │     │
│  │  The one UPDATE per row.                          │     │
│  │  For 1,000,000 rows: 1,000,000 statements.        │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  SET (the set-based)                                       │
│  ┌──────────────────────────────────────────────────┐     │
│  │  UPDATE orders                                    │     │
│  │  SET status = 'processed'                         │     │
│  │  WHERE status = 'pending';                        │     │
│  │                                                   │     │
│  │  The one UPDATE for all rows.                     │     │
│  │  For 1,000,000 rows: 1 statement.                 │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  The set-based is the one statement, and the optimizer     │
│  plans it. The RBAR is the N statements.                   │
└────────────────────────────────────────────────────────────┘

The N+1

┌────────────────────────────────────────────────────────────┐
│  THE N+1                                                   │
│                                                            │
│  N+1 (the cursor)                                          │
│  ┌──────────────────────────────────────────────────┐     │
│  │  FOR r IN SELECT id, customer_id FROM orders LOOP │     │
│  │    SELECT name INTO customer_name                  │     │
│  │    FROM customers WHERE id = r.customer_id;        │     │
│  │  END LOOP;                                        │     │
│  │                                                   │     │
│  │  The one query per row.                           │     │
│  │  For 1,000 rows: 1,001 queries.                   │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  JOIN (the set-based)                                      │
│  ┌──────────────────────────────────────────────────┐     │
│  │  FOR r IN SELECT o.id, c.name                     │     │
│  │           FROM orders o                           │     │
│  │           JOIN customers c ON c.id = o.customer_id│     │
│  │           LOOP                                    │     │
│  │    -- process                                     │     │
│  │  END LOOP;                                        │     │
│  │                                                   │     │
│  │  The one query.                                   │     │
│  │  For 1,000 rows: 1 query.                         │     │
│  └──────────────────────────────────────────────────┘     │
└────────────────────────────────────────────────────────────┘

The Batch Commit

┌────────────────────────────────────────────────────────────┐
│  THE BATCH COMMIT                                          │
│                                                            │
│  v_count := 0;                                             │
│  FOR r IN SELECT id FROM orders WHERE status = 'pending' LOOP│
│    UPDATE orders SET status = 'processed' WHERE id = r.id; │
│    v_count := v_count + 1;                                 │
│                                                            │
│    IF v_count % 1000 = 0 THEN                              │
│      COMMIT;                                               │
│    END IF;                                                 │
│  END LOOP;                                                 │
│  COMMIT;                                                   │
│                                                            │
│  ┌──────────────────────────────────────────────────┐     │
│  │  The transaction is committed every 1000 rows.    │     │
│  │  The long-running transaction is avoided.         │     │
│  │  The lock is released per batch.                  │     │
│  └──────────────────────────────────────────────────┘     │
└────────────────────────────────────────────────────────────┘

Summary

Item	Value
Cursor	The pointer to the result set
`DECLARE`	Define the query
`OPEN`	Execute the query
`FETCH`	Read the row
`CLOSE`	Release the resources
`NOT FOUND`	The end attribute
`FOR ... IN SELECT`	The implicit cursor
`WHERE CURRENT OF`	The positioned update
RBAR	The row-by-row anti-pattern
N+1	The query-per-iteration anti-pattern

Key takeaways:

A cursor is the pointer to the result set. The DECLARE defines the query, the OPEN executes it, the FETCH reads the row, and the CLOSE releases the resources. The loop around the FETCH is the iteration.
The FOR ... IN SELECT is the implicit cursor. It declares, opens, fetches, and closes automatically. It is the preferred form for the common case, and the explicit cursor is the rare case where the re-open or the scroll is needed.
The EXIT WHEN NOT FOUND ends the loop. The NOT FOUND attribute is true when the last FETCH returned no row. The EXIT breaks the loop, and the CLOSE follows.
The WHERE CURRENT OF updates the current row. The cursor must be declared with the FOR UPDATE, and the update targets the row the cursor is positioned on. The pattern is the positioned update.
The RBAR is the anti-pattern. The row-by-row update is the N statements, and the set-based UPDATE is the one statement. The set-based is the faster, and the optimizer plans it.
The N+1 is the anti-pattern. The query per iteration is the N+1 queries, and the join is the one query. The join is the faster, and the loop reads the joined result.
The batch commit avoids the long transaction. The COMMIT per batch flushes the work, and the lock is released. The long-running transaction is the alternative, and the lock contention is the cost.
The cursor is the fallback. The set-based operation is the first choice, and the cursor is the exception. The external API call, the stateful process, and the complex recursion are the cases where the cursor is the right tool.

Remember: The cursors are the procedural SQL’s way of processing the rows one at a time. The lifecycle is the DECLARE, the OPEN, the FETCH, and the CLOSE. The FOR ... IN SELECT is the implicit form, and the explicit cursor is the rare case. The RBAR and the N+1 are the anti-patterns, and the set-based operation is the alternative. The result is the tool for the cases the set-based query cannot express, and the discipline to keep it out of the hot path.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!