SQLMentor // learn db2

Cursors

Difficulty: Advanced · ~9 min read

Overview

A cursor is a database object you use to step through a result set one row at a time from within procedural code. Think of it as an iterator over a SELECT result.

Cursors live in four states:

DECLARE  →  OPEN  →  FETCH (many times)  →  CLOSE

DB2 cursors come in several flavours:

Variant Behaviour
Forward-only (default) Can only FETCH NEXT
Scrollable Can FETCH FIRST/LAST/PRIOR/NEXT/ABSOLUTE/RELATIVE
Holdable (WITH HOLD) Survives a COMMIT — useful for long batch jobs
Returnable (WITH RETURN) Returns its result set to a procedure's caller

Heads-up: Set-based SQL is almost always faster than cursor loops. Reach for a cursor only when you genuinely need per-row state, complex per-row branching, or interaction with another system between rows.

Syntax

DECLARE c CURSOR [WITH HOLD] [WITH RETURN]
  FOR <SELECT statement>;

OPEN c;

FETCH FROM c INTO :var1, :var2;       -- one row at a time
-- or DB2 PL syntax inside a procedure:
FETCH c INTO v_id, v_name;

CLOSE c;

DB2 also offers a compact form for the common "loop over a result set" case — FOR row AS SELECT ... DO ... END FOR — which manages the cursor for you.

Examples

Example 1: A minimal cursor inside a procedure

CREATE OR REPLACE PROCEDURE print_customers ()
LANGUAGE SQL
BEGIN
  DECLARE v_id   INTEGER;
  DECLARE v_name VARCHAR(80);
  DECLARE v_done INT DEFAULT 0;
  DECLARE c CURSOR FOR SELECT customer_id, name FROM customers;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

  OPEN c;
  fetch_loop: LOOP
    FETCH c INTO v_id, v_name;
    IF v_done = 1 THEN LEAVE fetch_loop; END IF;
    -- do something with v_id, v_name
    CALL DBMS_OUTPUT.PUT_LINE(v_id || ': ' || v_name);
  END LOOP;
  CLOSE c;
END@

NOT FOUND is the special condition raised when FETCH has no more rows.

Example 2: The same loop, the easy way

CREATE OR REPLACE PROCEDURE print_customers_v2 ()
LANGUAGE SQL
BEGIN
  FOR rec AS SELECT customer_id, name FROM customers DO
    CALL DBMS_OUTPUT.PUT_LINE(rec.customer_id || ': ' || rec.name);
  END FOR;
END@

FOR ... DO ... END FOR is far cleaner — DB2 handles the cursor lifecycle. Prefer this for straightforward iterations.

Example 3: Updateable cursor — modify rows as you fetch

CREATE OR REPLACE PROCEDURE bump_prices_carefully ()
LANGUAGE SQL
BEGIN
  DECLARE v_id INTEGER;
  DECLARE v_price DECIMAL(9,2);
  DECLARE v_done INT DEFAULT 0;
  DECLARE c CURSOR FOR
    SELECT product_id, price FROM products
    WHERE  category = 'Hardware'
    FOR UPDATE;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

  OPEN c;
  loop1: LOOP
    FETCH c INTO v_id, v_price;
    IF v_done = 1 THEN LEAVE loop1; END IF;

    -- Per-row decision
    IF v_price < 10 THEN
      UPDATE products SET price = price * 1.20 WHERE CURRENT OF c;
    ELSE
      UPDATE products SET price = price * 1.05 WHERE CURRENT OF c;
    END IF;
  END LOOP;
  CLOSE c;
END@

FOR UPDATE makes the cursor updateable; WHERE CURRENT OF c modifies the row the cursor is currently positioned on — no need for a separate WHERE primary_key = v_id.

Example 4: Scrollable cursor

DECLARE c SCROLL CURSOR FOR
  SELECT product_id, name FROM products ORDER BY product_id;

OPEN c;
FETCH FIRST    FROM c INTO v_id, v_name;     -- first row
FETCH LAST     FROM c INTO v_id, v_name;     -- last row
FETCH ABSOLUTE 5 FROM c INTO v_id, v_name;   -- 5th row
FETCH PRIOR    FROM c INTO v_id, v_name;     -- 4th row
FETCH RELATIVE -2 FROM c INTO v_id, v_name;  -- 2nd row
CLOSE c;

Useful for paging UIs that allow back-and-forth navigation.

Example 5: WITH HOLD — survive COMMIT

DECLARE c CURSOR WITH HOLD FOR
  SELECT order_id FROM orders WHERE status = 'PENDING';

OPEN c;

fetch_loop: LOOP
  FETCH c INTO v_order_id;
  IF v_done = 1 THEN LEAVE fetch_loop; END IF;

  CALL process_order(v_order_id);
  COMMIT;                       -- usually closes the cursor; WITH HOLD keeps it open
END LOOP;
CLOSE c;

Critical for batch processing: you want to commit each row's work to release locks, but keep iterating the same result set.

Example 6: WITH RETURN — return rows from a procedure

CREATE OR REPLACE PROCEDURE pending_orders ()
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
  DECLARE c CURSOR WITH RETURN FOR
    SELECT order_id, customer_id, amount
    FROM   orders
    WHERE  status = 'PENDING';
  OPEN c;
  -- intentionally don't CLOSE — open cursor flows back to caller
END@

CALL pending_orders()@

Combined with DYNAMIC RESULT SETS n, this is how procedures return result sets to clients.

Example 7: Diagnostics after FETCH

DECLARE v_rows INTEGER;
GET DIAGNOSTICS v_rows = ROW_COUNT;
-- v_rows is the number of rows the last FETCH actually returned (0 or 1 for forward-only)

For procedures that need to know how many rows arrived, GET DIAGNOSTICS is the standard way.

Notes & Tips

  • Set-based first. Before reaching for a cursor, ask: "can this be a single UPDATE / MERGE?" Usually yes — and it'll be 10–100× faster.
  • A cursor loop with one INSERT per row is the single biggest performance anti-pattern in DB2 procedures. Buffer rows and do one bulk INSERT … SELECT instead.
  • Always pair OPEN with CLOSE (the FOR ... DO ... END FOR form does this automatically). Leaked cursors hold locks and consume memory.
  • WITH HOLD cursors are essential for long batch jobs that need to commit periodically. Without it, the cursor closes at every COMMIT.
  • FOR UPDATE + WHERE CURRENT OF is the cleanest way to do per-row updates inside a cursor loop.
  • For procedures returning result sets, WITH RETURN + DYNAMIC RESULT SETS is the canonical pattern. Don't CLOSE the cursor — DB2 wants it open at procedure exit.

Practice Exercises

  1. Write a procedure that iterates the orders table with a cursor and inserts a summary row into daily_summary — then rewrite it with a single INSERT … SELECT and compare timings.
  2. Use a WITH HOLD cursor inside a procedure that processes 10,000 pending orders, committing after every 100.
  3. Build a scrollable cursor over products ordered by price and FETCH FIRST, FETCH LAST, FETCH RELATIVE 50.
  4. Write a procedure that returns the 10 most expensive products as a result set via WITH RETURN.
  5. Add GET DIAGNOSTICS … ROW_COUNT after a FETCH and inspect the value.

Quick Quiz

Q1. When should you use a cursor instead of set-based SQL?

Show answer

When you genuinely need per-row procedural logic that can't be expressed in a single SQL statement: branching to different external systems, calling stored procedures per row, complex conditional rewriting, or interactive paging. For most batch transformations, a single UPDATE, MERGE, or INSERT … SELECT is dramatically faster.

Q2. What does WITH HOLD do for a cursor?

Show answer

It keeps the cursor open across COMMIT. Without WITH HOLD, COMMIT closes every open cursor in the session. WITH HOLD is essential for long batch jobs that need to commit periodically (to release locks and limit log usage) without losing their iteration position.

Q3. What is WHERE CURRENT OF c used for?

Show answer

It limits an UPDATE or DELETE to the row the cursor c is currently positioned on. The cursor must be declared FOR UPDATE. It saves you from having to re-locate the row via its primary key — and it's safer because the cursor's position is exactly the row you just read.

Next Up

Next we step back from procedural code to the transaction semantics that wrap every DB2 statement.