Loading learning content...
A cursor declaration defines what a cursor will do, but the cursor remains dormant until you activate it through operational commands. The three fundamental cursor operations—OPEN, FETCH, and CLOSE—form a lifecycle that every cursor-based program must follow.
OPEN: Executes the cursor's query and creates the result set. Resources are allocated, locks may be acquired, and the cursor is positioned before the first row.
FETCH: Retrieves data from the current cursor position and optionally advances the position. This is typically called repeatedly in a loop until all rows are processed.
CLOSE: Releases the result set and associated resources. Locks are freed, memory is deallocated, but the cursor definition remains for potential reopening.
Mastering these operations involves understanding:
By the end of this page, you will master cursor operations: OPEN mechanics and resource allocation, FETCH variations and status checking, loop patterns for iteration, positioned UPDATE and DELETE, CLOSE and DEALLOCATE semantics, and robust error handling. You'll write production-quality cursor code with proper resource management.
The OPEN statement activates a declared cursor, executing its associated query and establishing the result set. This is when the database actually does the work of finding matching rows.
Syntax:
OPEN cursor_name;
What Happens at OPEN:
Important: The cursor position after OPEN is before the first row, not on the first row. You must FETCH to move onto the first row and retrieve its data.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566
-- SQL Server: Basic OPENDECLARE @order_id INT, @total DECIMAL(10,2); DECLARE order_cursor CURSOR FOR SELECT order_id, total_amount FROM orders WHERE status = 'pending'; -- Query executes HERE, at OPENOPEN order_cursor; -- Cursor now positioned BEFORE first row-- Must FETCH to get first row's dataFETCH NEXT FROM order_cursor INTO @order_id, @total; -- Oracle PL/SQL: OPEN with parametersDECLARE CURSOR emp_cursor(p_dept_id NUMBER, p_min_salary NUMBER) IS SELECT employee_id, salary FROM employees WHERE department_id = p_dept_id AND salary > p_min_salary;BEGIN -- Query executes with these parameter values OPEN emp_cursor(10, 50000); -- Process... CLOSE emp_cursor; -- Can reopen with different parameters OPEN emp_cursor(20, 60000); -- Process department 20... CLOSE emp_cursor;END; -- PostgreSQL: OPEN in transactionBEGIN; DECLARE order_cursor CURSOR FOR SELECT * FROM orders WHERE date_placed > '2024-01-01'; -- Actually, in PostgreSQL, DECLARE creates AND opens the cursor-- No separate OPEN needed for SQL cursors-- But in PL/pgSQL functions, OPEN is required: CREATE FUNCTION process_orders() RETURNS void AS $$DECLARE order_cursor CURSOR FOR SELECT * FROM orders; order_rec orders%ROWTYPE;BEGIN OPEN order_cursor; -- Opens here in PL/pgSQL LOOP FETCH order_cursor INTO order_rec; EXIT WHEN NOT FOUND; -- Process... END LOOP; CLOSE order_cursor;END;$$ LANGUAGE plpgsql; COMMIT;For complex queries or large result sets, OPEN can be the most time-consuming cursor operation. A query joining multiple large tables, even if only fetching 10 rows, executes fully (or substantially) at OPEN time. Profile OPEN duration when troubleshooting cursor performance.
OPEN Errors:
OPEN can fail for several reasons:
The FETCH statement retrieves data from the current cursor position and typically advances the position to the next row. This is the operation you call repeatedly to iterate through the result set.
Basic Syntax:
FETCH [direction] FROM cursor_name INTO variable_list;
FETCH Directions:
For forward-only cursors, only FETCH NEXT is available. Scrollable cursors support additional directions:
| Direction | Syntax | Description |
|---|---|---|
| NEXT | FETCH NEXT FROM cursor | Move forward one row (default) |
| PRIOR (PREVIOUS) | FETCH PRIOR FROM cursor | Move backward one row |
| FIRST | FETCH FIRST FROM cursor | Move to first row |
| LAST | FETCH LAST FROM cursor | Move to last row |
| ABSOLUTE n | FETCH ABSOLUTE 5 FROM cursor | Move to row n (1-indexed) |
| RELATIVE n | FETCH RELATIVE -3 FROM cursor | Move n rows from current |
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576
-- SQL Server: Basic FETCH NEXTDECLARE @emp_id INT, @name VARCHAR(100), @salary DECIMAL(10,2); DECLARE emp_cursor CURSOR FAST_FORWARD FOR SELECT employee_id, first_name + ' ' + last_name, salary FROM employees WHERE department_id = 10; OPEN emp_cursor; -- First FETCH moves from "before first" to row 1FETCH NEXT FROM emp_cursor INTO @emp_id, @name, @salary; -- @@FETCH_STATUS tells us what happened-- 0 = Success, -1 = No more rows, -2 = Row deleted (keyset)WHILE @@FETCH_STATUS = 0BEGIN PRINT 'Employee: ' + @name + ' earns $' + CAST(@salary AS VARCHAR); -- Move to next row FETCH NEXT FROM emp_cursor INTO @emp_id, @name, @salary;END CLOSE emp_cursor;DEALLOCATE emp_cursor; -- SQL Server: Scrollable cursor FETCH operationsDECLARE product_cursor SCROLL CURSOR FOR SELECT product_id, product_name, unit_price FROM products ORDER BY unit_price DESC; OPEN product_cursor; -- Get the most expensive product (first, since sorted DESC)FETCH FIRST FROM product_cursor INTO @id, @name, @price;PRINT 'Most expensive: ' + @name; -- Get the least expensive (last row)FETCH LAST FROM product_cursor INTO @id, @name, @price;PRINT 'Least expensive: ' + @name; -- Get the 10th most expensiveFETCH ABSOLUTE 10 FROM product_cursor INTO @id, @name, @price;PRINT '10th most expensive: ' + @name; -- Move 5 rows backward (to 5th position)FETCH RELATIVE -5 FROM product_cursor INTO @id, @name, @price;PRINT '5th most expensive: ' + @name; CLOSE product_cursor;DEALLOCATE product_cursor; -- Oracle PL/SQL: FETCH with %FOUND attributeDECLARE CURSOR order_cursor IS SELECT order_id, total_amount FROM orders WHERE status = 'pending'; v_order_id orders.order_id%TYPE; v_total orders.total_amount%TYPE;BEGIN OPEN order_cursor; LOOP FETCH order_cursor INTO v_order_id, v_total; -- Exit when no more rows EXIT WHEN order_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Order ' || v_order_id || ': $' || v_total); END LOOP; -- How many rows did we fetch? DBMS_OUTPUT.PUT_LINE('Total rows: ' || order_cursor%ROWCOUNT); CLOSE order_cursor;END;FETCH INTO Clause:
The INTO clause specifies where to store the fetched data. Variables must:
Bulk FETCH (Oracle):
Oracle supports fetching multiple rows at once for improved performance:
12345678910111213141516171819202122232425262728293031323334353637383940414243
-- Oracle: BULK COLLECT for batch fetchingDECLARE TYPE t_order_ids IS TABLE OF orders.order_id%TYPE; TYPE t_totals IS TABLE OF orders.total_amount%TYPE; v_order_ids t_order_ids; v_totals t_totals; CURSOR order_cursor IS SELECT order_id, total_amount FROM orders WHERE status = 'pending';BEGIN OPEN order_cursor; -- Fetch 100 rows at a time LOOP FETCH order_cursor BULK COLLECT INTO v_order_ids, v_totals LIMIT 100; -- Process the batch FOR i IN 1..v_order_ids.COUNT LOOP DBMS_OUTPUT.PUT_LINE('Order: ' || v_order_ids(i)); END LOOP; -- Exit when FETCH returned fewer than requested (end of data) EXIT WHEN v_order_ids.COUNT < 100; END LOOP; CLOSE order_cursor;END; -- PostgreSQL: FETCH with countBEGIN; DECLARE order_cursor CURSOR FOR SELECT * FROM orders WHERE status = 'pending'; -- Fetch 10 rows at once (returns result set)FETCH 10 FROM order_cursor; -- Fetch next 10FETCH 10 FROM order_cursor; CLOSE order_cursor;COMMIT;When processing many rows, bulk fetching dramatically reduces context-switch overhead between SQL and procedural engines. In Oracle, BULK COLLECT into collections is often 10-100x faster than row-by-row fetching for large result sets.
Detecting when you've processed all rows is critical for correct cursor iteration. Each database platform provides mechanism for checking fetch outcomes.
The Fetch Status Concept:
After each FETCH, the database sets a status indicator that tells you:
| Platform | Status Mechanism | Success | No Data | Notes |
|---|---|---|---|---|
| SQL Server | @@FETCH_STATUS | 0 | -1 | -2 = row missing (keyset) |
| Oracle | %FOUND / %NOTFOUND | %FOUND = TRUE | %NOTFOUND = TRUE | Cursor attributes |
| PostgreSQL | FOUND variable | FOUND = TRUE | FOUND = FALSE | In PL/pgSQL |
| MySQL | Handler + flag | No NOT FOUND | Handler triggers | Use CONTINUE HANDLER |
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495
-- SQL Server: @@FETCH_STATUS patternDECLARE @name VARCHAR(100);DECLARE name_cursor CURSOR FOR SELECT first_name FROM employees; OPEN name_cursor;FETCH NEXT FROM name_cursor INTO @name; WHILE @@FETCH_STATUS = 0 -- 0 means "got a row"BEGIN -- Process @name PRINT @name; FETCH NEXT FROM name_cursor INTO @name;END CLOSE name_cursor;DEALLOCATE name_cursor; -- @@FETCH_STATUS values:-- 0 = FETCH successful-- -1 = FETCH failed or end of result set-- -2 = Row fetched is missing (KEYSET cursor, row was deleted) -- Oracle: Cursor attributes patternDECLARE CURSOR emp_cur IS SELECT employee_name FROM employees; v_name employees.employee_name%TYPE;BEGIN OPEN emp_cur; LOOP FETCH emp_cur INTO v_name; EXIT WHEN emp_cur%NOTFOUND; -- Exit after failed fetch -- Process v_name (only reached if fetch succeeded) DBMS_OUTPUT.PUT_LINE(v_name); END LOOP; CLOSE emp_cur;END; -- Oracle cursor attributes:-- %FOUND - TRUE if last FETCH returned a row-- %NOTFOUND - TRUE if last FETCH returned no row-- %ROWCOUNT - Number of rows fetched so far-- %ISOPEN - TRUE if cursor is currently open -- PostgreSQL: FOUND variable patternCREATE FUNCTION list_employees() RETURNS void AS $$DECLARE emp_cursor CURSOR FOR SELECT first_name FROM employees; emp_name VARCHAR(100);BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO emp_name; EXIT WHEN NOT FOUND; -- FOUND is automatically set RAISE NOTICE 'Employee: %', emp_name; END LOOP; CLOSE emp_cursor;END;$$ LANGUAGE plpgsql; -- MySQL: Handler pattern (the only option)DELIMITER //CREATE PROCEDURE list_employees()BEGIN DECLARE done BOOLEAN DEFAULT FALSE; DECLARE emp_name VARCHAR(100); DECLARE emp_cursor CURSOR FOR SELECT first_name FROM employees; -- Handler sets 'done' when no more rows DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN emp_cursor; read_loop: LOOP FETCH emp_cursor INTO emp_name; IF done THEN LEAVE read_loop; -- Exit the loop END IF; -- Process emp_name SELECT emp_name; END LOOP; CLOSE emp_cursor;END //DELIMITER ;A common bug: processing data before checking if the fetch succeeded. Always check the status/FOUND condition before using the fetched values. In the standard pattern, FETCH happens at the bottom of the loop, with status check at the WHILE condition.
Several loop patterns exist for cursor iteration, each with trade-offs in clarity, efficiency, and compatibility. Understanding these patterns helps you write readable, maintainable cursor code.
12345678910111213141516171819202122232425262728
-- Pattern: Initial FETCH, WHILE loop, FETCH at bottom-- Most portable, works on all platforms OPEN my_cursor; -- Prime the loop with first fetchFETCH NEXT FROM my_cursor INTO @var1, @var2; -- Loop while we have dataWHILE @@FETCH_STATUS = 0BEGIN -- Process current row -- @var1 and @var2 contain current row's data -- Fetch next row at end of loop FETCH NEXT FROM my_cursor INTO @var1, @var2;END CLOSE my_cursor; -- Advantages:-- + Works on all platforms-- + Status check before processing (safe)-- + Clear flow control -- Disadvantages:-- - Duplicate FETCH statement (DRY violation)-- - More lines of codeSQL Server: FETCH-WHILE. Oracle: Cursor FOR loop (simplest) or LOOP-EXIT WHEN (more control). PostgreSQL: LOOP-EXIT WHEN or FOR record_var IN cursor. MySQL: Handler pattern (only option). Always match your pattern to your platform's idioms.
Cursors declared FOR UPDATE support positioned modifications—updating or deleting the exact row the cursor is currently pointing to, using the WHERE CURRENT OF clause.
Syntax:
UPDATE table_name
SET column = value, ...
WHERE CURRENT OF cursor_name;
DELETE FROM table_name
WHERE CURRENT OF cursor_name;
Requirements:
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182
-- SQL Server: Positioned UPDATE exampleDECLARE @acct_id INT, @balance DECIMAL(15,2), @last_txn DATE; DECLARE account_cursor CURSOR FOR SELECT account_id, balance, last_transaction_date FROM accounts WHERE status = 'active' FOR UPDATE OF status, balance; -- Specify updatable columns OPEN account_cursor;FETCH NEXT FROM account_cursor INTO @acct_id, @balance, @last_txn; WHILE @@FETCH_STATUS = 0BEGIN -- Check if account is dormant (no activity for 2 years) IF @last_txn < DATEADD(YEAR, -2, GETDATE()) BEGIN -- Mark as dormant using positioned UPDATE UPDATE accounts SET status = 'dormant' WHERE CURRENT OF account_cursor; PRINT 'Marked account ' + CAST(@acct_id AS VARCHAR) + ' as dormant'; END -- Check for zero balance IF @balance = 0 BEGIN -- Could also DELETE if appropriate: -- DELETE FROM accounts WHERE CURRENT OF account_cursor; UPDATE accounts SET status = 'closed' WHERE CURRENT OF account_cursor; END FETCH NEXT FROM account_cursor INTO @acct_id, @balance, @last_txn;END CLOSE account_cursor;DEALLOCATE account_cursor; -- Oracle: Positioned UPDATE with FOR UPDATE NOWAITDECLARE CURSOR inventory_cursor IS SELECT product_id, quantity_on_hand, reorder_level FROM inventory WHERE warehouse_id = 100 FOR UPDATE OF quantity_on_hand NOWAIT; -- Fail if locked v_product_id inventory.product_id%TYPE; v_qty inventory.quantity_on_hand%TYPE; v_reorder inventory.reorder_level%TYPE;BEGIN OPEN inventory_cursor; LOOP FETCH inventory_cursor INTO v_product_id, v_qty, v_reorder; EXIT WHEN inventory_cursor%NOTFOUND; -- Trigger reorder if below threshold IF v_qty < v_reorder THEN UPDATE inventory SET quantity_on_hand = v_qty + 100 -- Order 100 units WHERE CURRENT OF inventory_cursor; -- Also log the reorder INSERT INTO reorder_log (product_id, ordered_at, quantity) VALUES (v_product_id, SYSDATE, 100); END IF; END LOOP; CLOSE inventory_cursor; COMMIT;EXCEPTION WHEN OTHERS THEN IF inventory_cursor%ISOPEN THEN CLOSE inventory_cursor; END IF; ROLLBACK; RAISE;END;Why Use Positioned Operations?
Positioned UPDATE/DELETE ensures you modify exactly the row you just examined. Without it, you'd need:
-- Without positioned update (problematic):
UPDATE accounts SET status = 'dormant'
WHERE account_id = @acct_id; -- What if another row gets this ID?
The WHERE CURRENT OF guarantees you're modifying the exact physical row the cursor is on, not just a row with matching key values (important during concurrent modifications).
Locking Considerations:
FOR UPDATE cursors typically acquire locks as rows are fetched:
Use FOR UPDATE NOWAIT (Oracle) or equivalent to fail immediately if locks aren't available, rather than waiting indefinitely.
FOR UPDATE cursors can create significant contention. Process and commit in small batches when possible. Consider using optimistic locking (version columns, timestamps) instead of pessimistic cursor-based locking for better concurrency.
Proper cursor cleanup is essential for releasing database resources. Failing to close cursors leads to memory leaks, exhausted cursor limits, and lock contention.
CLOSE:
The CLOSE statement releases the result set and associated resources, but preserves the cursor definition.
CLOSE cursor_name;
After CLOSE:
DEALLOCATE:
DEALLOCATE removes the cursor definition from memory entirely. The cursor name can then be reused.
DEALLOCATE cursor_name;
Platform Variations:
| Platform | CLOSE | DEALLOCATE | Auto-Cleanup |
|---|---|---|---|
| SQL Server | Required separately | Required separately | On batch end (LOCAL), connection end (GLOBAL) |
| Oracle | Required | Not needed (scope-based) | On block exit or exception |
| PostgreSQL | Required | Not needed (drops cursor) | On transaction end (without HOLD) |
| MySQL | Required | Not needed | On procedure exit |
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647
-- SQL Server: Full cleanup syntaxDECLARE my_cursor CURSOR FOR SELECT * FROM table1;OPEN my_cursor;-- ... fetch loop ...CLOSE my_cursor; -- Release result setDEALLOCATE my_cursor; -- Remove definition -- Checking if cursor exists before deallocationIF CURSOR_STATUS('global', 'my_cursor') >= -1BEGIN IF CURSOR_STATUS('global', 'my_cursor') >= 0 CLOSE my_cursor; DEALLOCATE my_cursor;END -- Oracle: Close is sufficientDECLARE CURSOR my_cursor IS SELECT * FROM table1;BEGIN OPEN my_cursor; -- ... fetch loop ... CLOSE my_cursor; -- No DEALLOCATE needed; cursor definition goes away with blockEND; -- PostgreSQL: CLOSE removes the cursor entirelyBEGIN;DECLARE my_cursor CURSOR FOR SELECT * FROM table1;FETCH ALL FROM my_cursor;CLOSE my_cursor; -- Cursor is deallocated-- Can no longer reference my_cursorCOMMIT; -- Reopening a closed cursor (SQL Server)DECLARE reopen_cursor CURSOR FOR SELECT * FROM orders; -- First useOPEN reopen_cursor;-- ... process ...CLOSE reopen_cursor; -- Definition still exists -- Later, reopen with fresh result setOPEN reopen_cursor; -- Query runs again-- ... process new data ...CLOSE reopen_cursor; DEALLOCATE reopen_cursor; -- Now remove definitionCursor cleanup should be guaranteed even when errors occur. Use TRY/CATCH (SQL Server), EXCEPTION blocks (Oracle), or ensure cleanup in FINALLY-equivalent constructs. A cursor left open due to an unhandled exception continues consuming resources.
Robust cursor code must handle errors gracefully while ensuring resources are always released. Each platform has its own error handling mechanisms.
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091
-- SQL Server: TRY/CATCH with cursor cleanupDECLARE @order_id INT, @amount DECIMAL(10,2);DECLARE order_cursor CURSOR FOR SELECT order_id, total_amount FROM orders WHERE status = 'pending'; BEGIN TRY OPEN order_cursor; FETCH NEXT FROM order_cursor INTO @order_id, @amount; WHILE @@FETCH_STATUS = 0 BEGIN -- Processing that might fail EXEC dbo.ProcessOrder @order_id, @amount; FETCH NEXT FROM order_cursor INTO @order_id, @amount; ENDEND TRYBEGIN CATCH -- Log the error PRINT 'Error processing order ' + CAST(@order_id AS VARCHAR); PRINT ERROR_MESSAGE();END CATCH -- ALWAYS clean up, regardless of success or failureIF CURSOR_STATUS('local', 'order_cursor') >= 0 CLOSE order_cursor;IF CURSOR_STATUS('local', 'order_cursor') >= -1 DEALLOCATE order_cursor; -- Alternative: Check %ISOPEN equivalent-- SQL Server pattern with guaranteed cleanup:DECLARE @cursor_open BIT = 0; BEGIN TRY DECLARE cleanup_cursor CURSOR FOR SELECT * FROM table1; OPEN cleanup_cursor; SET @cursor_open = 1; -- ... processing ...END TRYBEGIN CATCH -- Handle errorEND CATCH IF @cursor_open = 1BEGIN CLOSE cleanup_cursor; DEALLOCATE cleanup_cursor;END -- Oracle: EXCEPTION block with cleanupDECLARE CURSOR order_cursor IS SELECT order_id, total_amount FROM orders WHERE status = 'pending'; v_order_id orders.order_id%TYPE; v_amount orders.total_amount%TYPE;BEGIN OPEN order_cursor; LOOP FETCH order_cursor INTO v_order_id, v_amount; EXIT WHEN order_cursor%NOTFOUND; -- Processing that might raise an exception process_order(v_order_id, v_amount); END LOOP; CLOSE order_cursor; EXCEPTION WHEN OTHERS THEN -- Ensure cursor is closed even on error IF order_cursor%ISOPEN THEN CLOSE order_cursor; END IF; -- Log and re-raise log_error(SQLCODE, SQLERRM); RAISE;END; -- Oracle: Autonomous transaction for logging-- (Allows logging to commit even if main transaction rolls back)CREATE OR REPLACE PROCEDURE log_cursor_error(p_cursor VARCHAR2, p_error VARCHAR2)IS PRAGMA AUTONOMOUS_TRANSACTION;BEGIN INSERT INTO error_log (cursor_name, error_text, logged_at) VALUES (p_cursor, p_error, SYSTIMESTAMP); COMMIT;END;Common Cursor Errors:
| Error | Cause | Solution |
|---|---|---|
| Cursor already open | OPEN on already-open cursor | Check status before OPEN, or CLOSE first |
| Cursor not open | FETCH/CLOSE on unopened cursor | Ensure OPEN was called |
| Cursor not declared | Reference non-existent cursor | Check cursor name spelling and scope |
| Maximum cursors exceeded | Too many open cursors | Increase limit or close unused cursors |
| Invalid fetch | FETCH PRIOR on forward-only | Use SCROLL cursor or only FETCH NEXT |
| Row not found for update | WHERE CURRENT OF on deleted row | Check @@FETCH_STATUS for -2 (keyset) |
Always check cursor status before operations. Always guarantee cleanup in error handlers. Consider using cursor wrapper procedures that encapsulate open-process-close-handle-errors patterns for reusability.
The OPEN, FETCH, and CLOSE operations form the runtime lifecycle of every cursor. Let's consolidate the key concepts:
What's Next:
With the operational mechanics understood, the next page explores cursor types in depth. We'll examine STATIC, KEYSET, DYNAMIC, and FAST_FORWARD cursors in SQL Server, equivalents in other platforms, and how to choose the right type for your specific requirements.
You now understand the complete cursor operation lifecycle: OPEN, FETCH, CLOSE, and DEALLOCATE. You can implement proper loop patterns, detect end-of-data conditions, perform positioned modifications, and handle errors while guaranteeing resource cleanup. Next, we'll explore the different types of cursors and their characteristics.