SQL Cursor?

A cursor lets you iterate row-by-row over a query result and run procedural logic per row. Useful when set-based SQL can’t express the logic or when you must call side-effects per row. It’s slower than set-based operations—use sparingly.

SQL Server (T-SQL) example

DECLARE @id int, @amount decimal(12,2);

DECLARE cur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
  SELECT id, amount FROM dbo.Payments WHERE status = 'PENDING';

OPEN cur;
FETCH NEXT FROM cur INTO @id, @amount;

WHILE @@FETCH_STATUS = 0
BEGIN
  -- per-row work
  UPDATE dbo.Payments SET status = 'PAID' WHERE id = @id;
  FETCH NEXT FROM cur INTO @id, @amount;
END

CLOSE cur;
DEALLOCATE cur;

Tips: prefer LOCAL FAST_FORWARD READ_ONLY; keep transactions short; fetch only needed columns.

Oracle (PL/SQL) examples

Implicit cursor FOR loop (clean & safe):

BEGIN
  FOR r IN (SELECT id, amount FROM payments WHERE status='PENDING') LOOP
    UPDATE payments SET status='PAID' WHERE id = r.id;
  END LOOP;
END;
/

Explicit cursor:

DECLARE
  CURSOR c IS SELECT id FROM payments WHERE status='PENDING';
  v_id payments.id%TYPE;
BEGIN
  OPEN c;
  LOOP
    FETCH c INTO v_id; EXIT WHEN c%NOTFOUND;
    UPDATE payments SET status='PAID' WHERE id = v_id;
  END LOOP;
  CLOSE c;
END;
/

MySQL (in a stored procedure)

DELIMITER //
CREATE PROCEDURE mark_paid()
BEGIN
  DECLARE v_id INT; DECLARE done INT DEFAULT 0;
  DECLARE cur CURSOR FOR SELECT id FROM payments WHERE status='PENDING';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_id;
    IF done = 1 THEN LEAVE read_loop; END IF;
    UPDATE payments SET status='PAID' WHERE id = v_id;
  END LOOP;
  CLOSE cur;
END//
DELIMITER ;

PostgreSQL (transaction-scoped)

BEGIN;
DECLARE pay_cur CURSOR FOR SELECT id FROM payments WHERE status='PENDING';
FETCH NEXT FROM pay_cur; -- repeat fetch/update as needed
CLOSE pay_cur;
COMMIT;

Prefer set-based when possible (faster)

UPDATE payments SET status='PAID' WHERE status='PENDING';

Java note (since you’re using Java)

Often you don’t need DB cursors—use a streaming ResultSet:

stmt.setFetchSize(1000); // PostgreSQL/Oracle; MySQL needs server-side prep
try (ResultSet rs = stmt.executeQuery("SELECT ...")) {
  while (rs.next()) {
    // process row
  }
}

When to use cursors

  • Per-row side effects (external calls, complex validation).
  • Very large result sets where you must stream (server-side cursor).
  • Where procedural control flow is unavoidable.

Avoid / replace with

  • Joins, window functions, MERGE, UPDATE ... FROM, CTEs—nearly always faster and simpler.

Back to blog

Leave a comment