SQL CURSOR

What is SQL CURSOR?

SQL CURSOR is a database object that lets you iterate row by row through a query result set inside stored procedures, scripts, or PL/pgSQL/Transact-SQL blocks.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL CURSOR: PostgreSQL, MySQL (within stored procedures), SQL Server, Oracle, DB2, SAP HANA, Snowflake (via result set iteration), SQLite (limited, via extensions)

SQL CURSOR Full Explanation

A cursor provides sequential, controlled access to the rows returned by a SELECT statement. Unlike set-based operations that act on all rows at once, a cursor lets procedural code handle one row at a time, making it useful for complex business logic, row-by-row calculations, or conditional updates that cannot be expressed easily in pure SQL.Lifecycle1. DECLARE CURSOR associates the cursor name with a SELECT statement and optional attributes (SCROLL, INSENSITIVE, READ ONLY, FOR UPDATE, etc.).2. OPEN executes the underlying query and materializes the result set.3. FETCH retrieves the next, prior, absolute, or relative row into program variables.4. (Optional) UPDATE or DELETE WHERE CURRENT OF modifies or removes the current row.5. CLOSE releases resources.Caveats- Cursors are slower than set-based operations and should be used only when necessary.- Some attributes vary by vendor; always check dialect documentation.- Without proper closing, cursors can leak memory or locks.- In autocommit mode, some databases close cursors automatically at commit; others require WITH HOLD to survive commits.

SQL CURSOR Syntax

DECLARE cursor_name [ SCROLL | NO SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR
    select_statement
    [ FOR { READ ONLY | UPDATE [ OF column_list ] } ];

OPEN cursor_name;

FETCH [ NEXT | PRIOR | ABSOLUTE n | RELATIVE n | FIRST | LAST ]
    FROM cursor_name INTO variable_list;

UPDATE table_name SET ... WHERE CURRENT OF cursor_name;
DELETE FROM table_name WHERE CURRENT OF cursor_name;

CLOSE cursor_name;

SQL CURSOR Parameters

cursor_name (identifier) - Name of the cursor object
SCROLL/NO SCROLL (keyword) - Whether backward movement is allowed
WITH/WITHOUT HOLD (keyword) - Whether the cursor remains open after COMMIT
FOR READ ONLY (keyword) - Disallow positioned updates
FOR UPDATE [OF cols] (keyword) - Allow positioned updates on listed columns
select_statement (SQL text) - Query that defines the result set

Example Queries Using SQL CURSOR

-- 1. Basic forward-only cursor in PostgreSQL
BEGIN;
DECLARE user_cur CURSOR FOR
  SELECT id, email FROM users WHERE active = true;

FETCH NEXT FROM user_cur INTO _id, _email;
-- process variables in application or PL/pgSQL loop

CLOSE user_cur;
COMMIT;

-- 2. Scroll cursor with positioned update in SQL Server
DECLARE @id INT, @qty INT;
DECLARE sales_cur SCROLL CURSOR FOR
  SELECT order_id, quantity FROM sales FOR UPDATE;
OPEN sales_cur;
FETCH FIRST FROM sales_cur INTO @id, @qty;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @qty < 10
        UPDATE sales SET quantity = 10 WHERE CURRENT OF sales_cur;
    FETCH NEXT FROM sales_cur INTO @id, @qty;
END;
CLOSE sales_cur;
DEALLOCATE sales_cur;

Expected Output Using SQL CURSOR

The cursor materializes the result set, then each FETCH returns one row into variables
Any positioned UPDATE or DELETE affects only the current row
When closed, resources and locks are released

Use Cases with SQL CURSOR

Complex row-level validation or transformation inside stored procedures
Migrating data where each row triggers secondary queries or function calls
Batch processing that must commit periodically between rows
Real-time ETL tasks that need fine-grained control over memory and locks

Common Mistakes with SQL CURSOR

Using cursors for logic that can be rewritten as set-based SQL, causing unnecessary slowdowns
Forgetting to CLOSE or DEALLOCATE, leading to open transactions or memory leaks
Assuming scrollability when the cursor was declared NO SCROLL
Expecting the cursor to stay open after COMMIT without WITH HOLD support

First Introduced In

SQL-92

Frequently Asked Questions

When should I use an SQL CURSOR?

Use a cursor only when set-based SQL cannot achieve the required per-row logic. Examples include conditional updates, row-driven API calls, or incremental ETL tasks.

Does a cursor lock the whole table?

Typically no. Databases lock only the rows fetched, but behavior depends on isolation level and whether FOR UPDATE is present.

How do I speed up a slow cursor?

Select only required columns, declare READ ONLY when possible, fetch multiple rows per round-trip if the dialect supports it, and close the cursor promptly.

Will my cursor survive a COMMIT?

Only if declared WITH HOLD and your database supports holdable cursors. Otherwise, a commit ends the cursor’s life.