Cursors - Learning Module

Loading content...

0/241

DECLARE CURSOR

Defining Cursor Behavior Precisely

The DECLARE CURSOR statement is where you define the contract between your procedural code and the database's cursor implementation. This declaration determines everything about how your cursor will behave: whether it can scroll backward, whether it sees concurrent changes, whether it can update rows, and how it consumes resources.

A poorly declared cursor can lead to:

Unexpected visibility of concurrent changes (or lack thereof)
Inability to scroll when navigation is needed
Memory overconsumption for large result sets
Locking problems that block other users
Failed UPDATE/DELETE WHERE CURRENT operations

Conversely, a well-declared cursor precisely matches your requirements, providing exactly the functionality needed without unnecessary overhead.

The challenge is that cursor declaration syntax varies significantly across database systems. SQL Server, Oracle, PostgreSQL, and MySQL each have different syntax, different defaults, and different available options. We'll cover the standard SQL syntax and practical variations for major platforms.

What You Will Learn

By the end of this page, you will master cursor declaration: the core DECLARE CURSOR syntax, the meaning of scrollability options (SCROLL, NO SCROLL), sensitivity options (INSENSITIVE, ASENSITIVE), holdability options (WITH HOLD), and updatability options (FOR READ ONLY, FOR UPDATE). You'll understand platform-specific variations and make informed choices for your cursor declarations.

Basic DECLARE CURSOR Syntax

The fundamental structure of a cursor declaration associates a name with a SELECT statement. The cursor name becomes your handle for all subsequent cursor operations.

Standard SQL Syntax:

DECLARE cursor_name [options] CURSOR [options] FOR
    select_statement
    [FOR UPDATE [OF column_list]]

The cursor name must be unique within its scope (typically the current stored procedure, function, or session). It follows standard identifier naming rules for your database system.

basic_cursor_declarations.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Simplest possible cursor declaration
DECLARE simple_cursor CURSOR FOR
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department_id = 10;
 
-- Cursor with ORDER BY (common for sequential processing)
DECLARE ordered_cursor CURSOR FOR
    SELECT order_id, order_date, total_amount
    FROM orders
    WHERE status = 'pending'
    ORDER BY order_date ASC;
 
-- Cursor with JOIN (multiple table access)
DECLARE employee_dept_cursor CURSOR FOR
    SELECT e.employee_id, e.first_name, d.department_name
    FROM employees e
    INNER JOIN departments d ON e.department_id = d.department_id
    WHERE e.hire_date > '2023-01-01';
 
-- Cursor with subquery
DECLARE high_performer_cursor CURSOR FOR
    SELECT employee_id, annual_sales
    FROM salespersons
    WHERE annual_sales > (
        SELECT AVG(annual_sales) * 1.5 
        FROM salespersons
    );

Query Restrictions:

Not all SELECT statements are valid for cursor declarations. Common restrictions include:

1. COMPUTE/INTO Clauses: Queries with SELECT INTO or COMPUTE BY typically cannot be used with cursors—these produce non-standard result formats.

2. FOR BROWSE: SQL Server's FOR BROWSE clause is incompatible with cursor declaration.

3. UNION Compatibility: Cursors over UNION queries may have restrictions on scrollability and updatability.

4. DISTINCT with FOR UPDATE: DISTINCT queries typically cannot be used with updatable cursors—the database can't identify which physical row to update.

Cursor Declaration Components
Component	Required	Purpose
DECLARE	Yes	Keyword initiating cursor declaration
cursor_name	Yes	Identifier for cursor operations
CURSOR	Yes	Keyword indicating this is a cursor
FOR	Yes	Keyword preceding the SELECT statement
select_statement	Yes	Query defining the result set
Options	No	Scrollability, sensitivity, holdability, etc.
FOR UPDATE	No	Enables positioned UPDATE/DELETE

Naming Conventions

Use descriptive cursor names that indicate purpose: 'pending_orders_cursor' is better than 'c1'. Include '_cursor' or '_cur' suffix for clarity. This becomes crucial in stored procedures with multiple cursors.

Scrollability Options: SCROLL vs. NO SCROLL

Scrollability determines the directions in which you can navigate through the cursor's result set. This seemingly simple choice has significant implications for resource usage and functionality.

FORWARD_ONLY (NO SCROLL):

The default in most databases. The cursor can only move forward through the result set—FETCH NEXT is the only navigation operation available.

-- Explicit forward-only cursor
DECLARE forward_cursor CURSOR FORWARD_ONLY FOR
    SELECT * FROM orders;

-- Or equivalently (standard SQL)
DECLARE forward_cursor NO SCROLL CURSOR FOR
    SELECT * FROM orders;

SCROLL:

A scrollable cursor can navigate in any direction using various FETCH operations:

DECLARE scroll_cursor SCROLL CURSOR FOR
    SELECT * FROM orders;

FETCH Operations by Scrollability
FETCH Operation	FORWARD_ONLY	SCROLL	Description
FETCH NEXT	✓	✓	Move to next row, return its data
FETCH PRIOR	✗	✓	Move to previous row, return its data
FETCH FIRST	✗	✓	Move to first row, return its data
FETCH LAST	✗	✓	Move to last row, return its data
FETCH ABSOLUTE n	✗	✓	Move to row position n (1-indexed)
FETCH RELATIVE n	✗	✓	Move n rows from current position

Resource Implications:

Scrollability isn't free. Here's why:

Forward-Only Cursors:

Can sometimes stream results without full materialization
Memory efficient—only buffer current row (or small batch)
Lower overhead per FETCH operation
Cannot revisit rows once passed

Scrollable Cursors:

Must maintain ability to return to any row
Typically require full result set materialization
Higher memory consumption
Can navigate to an specific row (useful for pagination-like patterns)
Must maintain index structures for positioning

scroll_cursor_examples.sql
SQL Server
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- SQL Server: Scroll cursor with various navigation
DECLARE @product_id INT, @product_name VARCHAR(100), @price DECIMAL(10,2);
 
DECLARE product_scroll_cursor SCROLL CURSOR FOR
    SELECT product_id, product_name, price
    FROM products
    WHERE category = 'Electronics'
    ORDER BY price DESC;
 
OPEN product_scroll_cursor;
 
-- Fetch the first (most expensive) product
FETCH FIRST FROM product_scroll_cursor 
INTO @product_id, @product_name, @price;
PRINT 'Most expensive: ' + @product_name + ' - $' + CAST(@price AS VARCHAR);
 
-- Fetch the last (least expensive) product
FETCH LAST FROM product_scroll_cursor 
INTO @product_id, @product_name, @price;
PRINT 'Least expensive: ' + @product_name + ' - $' + CAST(@price AS VARCHAR);
 
-- Fetch the 5th product
FETCH ABSOLUTE 5 FROM product_scroll_cursor 
INTO @product_id, @product_name, @price;
PRINT '5th product: ' + @product_name;
 
-- Move 3 rows backward from current position
FETCH RELATIVE -3 FROM product_scroll_cursor 
INTO @product_id, @product_name, @price;
PRINT '2nd product: ' + @product_name;
 
CLOSE product_scroll_cursor;
DEALLOCATE product_scroll_cursor;

Use SCROLL Only When Needed

Don't declare SCROLL cursors by default. The additional materialization and indexing overhead is wasted if you only ever FETCH NEXT. Use SCROLL only when you genuinely need non-sequential navigation. For simple iteration, FORWARD_ONLY is more efficient.

Sensitivity Options: INSENSITIVE, SENSITIVE, ASENSITIVE

Sensitivity determines whether the cursor sees changes made to the underlying data after the cursor is opened. This is one of the most important—and most misunderstood—aspects of cursor behavior.

INSENSITIVE (Static):

An insensitive cursor creates a snapshot of the result set at OPEN time. Changes to underlying data (by other transactions or even by your own session) are not visible through the cursor.

SENSITIVE (Dynamic):

A sensitive cursor reflects all changes to underlying data in real-time. Each FETCH directly accesses the base tables, seeing current values.

ASENSITIVE:

ASENSITIVE (the default in many databases) means the sensitivity is implementation-defined. The database chooses based on query characteristics and optimization decisions. Your code should not rely on specific sensitivity behavior with ASENSITIVE.

Sensitivity Behavior Comparison
Scenario	INSENSITIVE	SENSITIVE	ASENSITIVE
Another user UPDATEs a row value	Old value visible	New value visible	Depends on implementation
Another user DELETEs a row	Row still visible	Row disappears (or error)	Depends on implementation
Another user INSERTs a matching row	New row not visible	New row may appear	Depends on implementation
Your session UPDATE via cursor	Old value visible	New value visible	New value visible (typically)
Memory consumption	High (full copy)	Low (no copy)	Variable
Consistency guarantee	Snapshot consistency	Real-time, potentially inconsistent	Variable

sensitivity_examples.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
-- INSENSITIVE cursor: Creates snapshot at OPEN time
-- Oracle syntax (using cursor loops)
DECLARE
    CURSOR insensitive_emp_cur IS
        SELECT employee_id, salary FROM employees
        WHERE department_id = 10;
    v_emp_id employees.employee_id%TYPE;
    v_salary employees.salary%TYPE;
BEGIN
    OPEN insensitive_emp_cur;  -- Snapshot created here
    
    -- Even if another session updates salaries now,
    -- this cursor continues to see the old values
    
    LOOP
        FETCH insensitive_emp_cur INTO v_emp_id, v_salary;
        EXIT WHEN insensitive_emp_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Employee ' || v_emp_id || ': $' || v_salary);
    END LOOP;
    
    CLOSE insensitive_emp_cur;
END;
 
-- SQL Server: Explicit STATIC (insensitive) cursor
DECLARE @emp_id INT, @salary DECIMAL(10,2);
 
DECLARE static_cursor CURSOR STATIC FOR
    SELECT employee_id, salary 
    FROM employees 
    WHERE department_id = 10;
 
OPEN static_cursor;  -- Full result set copied to tempdb
 
-- Changes to employees table are NOT visible through this cursor
FETCH NEXT FROM static_cursor INTO @emp_id, @salary;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Employee ' + CAST(@emp_id AS VARCHAR) + ': $' + CAST(@salary AS VARCHAR);
    FETCH NEXT FROM static_cursor INTO @emp_id, @salary;
END
 
CLOSE static_cursor;
DEALLOCATE static_cursor;
 
-- SQL Server: DYNAMIC (sensitive) cursor
DECLARE dynamic_cursor CURSOR DYNAMIC FOR
    SELECT employee_id, salary 
    FROM employees 
    WHERE department_id = 10;
 
OPEN dynamic_cursor;  -- No copy made; accesses tables directly
 
-- If another user updates salaries, this cursor sees new values
-- If rows are inserted/deleted, cursor behavior may change
FETCH NEXT FROM dynamic_cursor INTO @emp_id, @salary;
-- ...processing...
 
CLOSE dynamic_cursor;
DEALLOCATE dynamic_cursor;

Phantom Rows and Sensitivity:

A phantom row is a row that appears in the result set during iteration that wasn't there when the cursor opened (or vice versa—a row that was there but disappears).

INSENSITIVE: No phantoms possible—result set is frozen
SENSITIVE: Phantoms possible—iteration may see new rows or miss deleted ones
ASENSITIVE: Phantom behavior is undefined

Choosing Sensitivity:

Use INSENSITIVE when:

You need consistent iteration over a stable result set
The overhead of materialization is acceptable
Other transactions might modify the data during your processing
You're generating reports that must be self-consistent

Use SENSITIVE when:

You need to see real-time data changes
Memory consumption is a concern for large result sets
You're monitoring or dashboarding live data
You can handle the complexity of a changing result set

Platform Terminology

SQL Server uses STATIC/KEYSET/DYNAMIC instead of INSENSITIVE/SENSITIVE. Oracle cursors are implicitly sensitive to changes within the session but use consistent read for other sessions. PostgreSQL cursors within transactions see a snapshot. Know your platform's behavior.

Holdability: WITH HOLD and Transaction Scope

By default, cursors are closed when a transaction ends (COMMIT or ROLLBACK). Holdability controls whether a cursor survives transaction boundaries.

WITH HOLD:

A holdable cursor remains open across COMMIT operations, allowing you to:

Process rows in one transaction
COMMIT to release locks and make changes permanent
Continue processing more rows with the same cursor
Repeat until done

This is valuable for long-running batch processes where you don't want to hold a single large transaction.

holdable_cursor_examples.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
-- PostgreSQL: WITH HOLD cursor
BEGIN;
 
DECLARE hold_cursor CURSOR WITH HOLD FOR
    SELECT order_id, customer_id, total_amount
    FROM orders
    WHERE status = 'pending'
    ORDER BY order_date;
 
-- Process first batch
FETCH 100 FROM hold_cursor;  -- Get 100 rows
-- (In application code: process these rows)
 
COMMIT;  -- Cursor remains open!
 
-- Continue in new transaction
BEGIN;
 
FETCH 100 FROM hold_cursor;  -- Get next 100 rows
-- (Process these rows)
 
COMMIT;  -- Cursor still open
 
-- When completely done:
CLOSE hold_cursor;
 
-- Oracle: Cursors are holdable by default within package state
-- But explicit cursor variables can be managed across calls
 
-- SQL Server: Use cursor variables with GLOBAL scope
DECLARE @hold_cursor CURSOR;
 
SET @hold_cursor = CURSOR GLOBAL FORWARD_ONLY FOR
    SELECT order_id FROM orders WHERE status = 'pending';
 
OPEN @hold_cursor;
 
-- Cursor survives batch boundaries when using GLOBAL
FETCH NEXT FROM @hold_cursor;
 
-- In separate batch:
FETCH NEXT FROM @hold_cursor;  -- Still works!
 
CLOSE @hold_cursor;
DEALLOCATE @hold_cursor;

WITHOUT HOLD (Default):

Most databases default to WITHOUT HOLD, meaning:

Cursor is automatically closed on COMMIT
Cursor is automatically closed on ROLLBACK
Locks held by the cursor are released with the transaction
Attempting to FETCH after COMMIT fails

Trade-offs:

WITH HOLD Advantages

•Process large datasets without one huge transaction
•Release locks periodically to reduce contention
•Commit partial work—recoverable on failure
•Avoid transaction log pressure
•Support interleaved operations

WITH HOLD Disadvantages

•Result set must be materialized to survive commit
•Higher memory consumption
•May not support positioned UPDATE/DELETE after commit
•Complexity in error handling across transactions
•Potential for resource leaks if not properly closed

Batch Processing Pattern with WITH HOLD

For processing millions of rows: open WITH HOLD cursor, fetch and process N rows (e.g., 1000), COMMIT, repeat. This commits work in chunks, releases locks periodically, and allows recovery from the last committed batch if something fails.

Updatability: FOR READ ONLY vs. FOR UPDATE

Updatability determines whether you can modify data through the cursor using positioned UPDATE and DELETE operations (WHERE CURRENT OF cursor_name).

FOR READ ONLY:

A read-only cursor cannot modify the underlying data through cursor operations. Any UPDATE or DELETE WHERE CURRENT OF will fail.

DECLARE readonly_cursor CURSOR FOR
    SELECT * FROM orders
    WHERE status = 'shipped'
    FOR READ ONLY;

FOR UPDATE:

An updatable cursor allows positioned modifications. You can optionally specify which columns may be updated.

DECLARE update_cursor CURSOR FOR
    SELECT order_id, status, shipping_date
    FROM orders
    WHERE status = 'pending'
    FOR UPDATE;  -- All columns updatable

-- Or specify columns:
DECLARE update_cursor CURSOR FOR
    SELECT order_id, status, shipping_date
    FROM orders
    WHERE status = 'pending'
    FOR UPDATE OF status, shipping_date;  -- Only these columns

updatable_cursor_examples.sql
SQL Server
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
-- SQL Server: Positioned UPDATE through cursor
DECLARE @order_id INT, @status VARCHAR(20), @ship_date DATE;
 
DECLARE order_update_cursor CURSOR FOR
    SELECT order_id, status, shipping_date
    FROM orders
    WHERE status = 'pending'
    FOR UPDATE OF status, shipping_date;
 
OPEN order_update_cursor;
 
FETCH NEXT FROM order_update_cursor INTO @order_id, @status, @ship_date;
 
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Check if order should be shipped
    IF dbo.CanShipOrder(@order_id) = 1
    BEGIN
        -- Positioned UPDATE: modify the current row
        UPDATE orders
        SET status = 'shipped',
            shipping_date = GETDATE()
        WHERE CURRENT OF order_update_cursor;
        
        PRINT 'Shipped order: ' + CAST(@order_id AS VARCHAR);
    END
    
    FETCH NEXT FROM order_update_cursor INTO @order_id, @status, @ship_date;
END
 
CLOSE order_update_cursor;
DEALLOCATE order_update_cursor;
 
-- Positioned DELETE example
DECLARE @product_id INT, @stock_level INT;
 
DECLARE inventory_cleanup_cursor CURSOR FOR
    SELECT product_id, stock_level
    FROM inventory
    WHERE discontinued = 1 AND stock_level = 0
    FOR UPDATE;  -- Need FOR UPDATE even for DELETE
 
OPEN inventory_cleanup_cursor;
 
FETCH NEXT FROM inventory_cleanup_cursor INTO @product_id, @stock_level;
 
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Archive the record before deletion
    INSERT INTO inventory_archive 
    SELECT *, GETDATE() as archived_date 
    FROM inventory WHERE product_id = @product_id;
    
    -- Positioned DELETE
    DELETE FROM inventory 
    WHERE CURRENT OF inventory_cleanup_cursor;
    
    FETCH NEXT FROM inventory_cleanup_cursor INTO @product_id, @stock_level;
END
 
CLOSE inventory_cleanup_cursor;
DEALLOCATE inventory_cleanup_cursor;

Updatability Restrictions:

Not all cursors can be updatable. The following conditions typically prevent FOR UPDATE:

1. Query Structure:

DISTINCT in SELECT
GROUP BY or HAVING clauses
UNION, INTERSECT, EXCEPT operations
Aggregate functions without GROUP BY
Subqueries in certain positions

2. Multi-table Queries:

JOINs may restrict which table is updatable
Views based on multiple tables may not be updatable

3. Cursor Type:

INSENSITIVE cursors may not support positioned updates
Read-only materializations can't reflect changes

4. Computed Columns:

Columns derived from expressions aren't directly updatable

Updatability by Query Type
Query Characteristic	Updatable?	Reason
Simple SELECT from single table	✓ Yes	Direct row access possible
SELECT DISTINCT	✗ No	Can't identify unique physical row
SELECT with GROUP BY	✗ No	Result rows don't map to base rows
SELECT with JOIN	◐ Partial	Only key-preserved table updatable
SELECT from view	◐ Depends	Only if view is updatable
SELECT with UNION	✗ No	Can't identify source row
SELECT with TOP/LIMIT	✓ Yes	Row identity preserved

Locking Implications of FOR UPDATE

FOR UPDATE cursors typically acquire exclusive (or update-intent) locks on rows as they're fetched. These locks prevent other transactions from modifying the data until your transaction completes. Use FOR UPDATE only when you genuinely intend to modify rows—otherwise, you're creating unnecessary lock contention.

Platform-Specific Declaration Syntax

Each major database system has its own cursor declaration syntax and options. Understanding platform differences is essential for portable code and correct behavior.

sql_server_cursors.sql
SQL Server
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- SQL Server cursor declaration syntax
DECLARE cursor_name CURSOR
    [LOCAL | GLOBAL]           -- Scope
    [FORWARD_ONLY | SCROLL]    -- Scrollability
    [STATIC | KEYSET | DYNAMIC | FAST_FORWARD]  -- Type
    [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]     -- Concurrency
    [TYPE_WARNING]             -- Warning if type conversion
FOR select_statement
[FOR UPDATE [OF column_list]]
 
-- Example: Fully specified cursor
DECLARE detailed_cursor CURSOR
    LOCAL                     -- Visible only in current batch
    SCROLL                    -- Can navigate any direction
    KEYSET                    -- Keys frozen, values current
    SCROLL_LOCKS              -- Lock rows when scrolled to
FOR 
    SELECT employee_id, salary 
    FROM employees
    WHERE department_id = 10
FOR UPDATE OF salary;
 
-- FAST_FORWARD: Optimized forward-only, read-only
DECLARE fast_cursor CURSOR FAST_FORWARD FOR
    SELECT * FROM orders WHERE status = 'pending';
-- FAST_FORWARD = FORWARD_ONLY + READ_ONLY + optimizations
 
-- LOCAL vs GLOBAL
DECLARE local_cursor CURSOR LOCAL FOR SELECT ...;
-- Deallocated when batch/procedure exits
 
DECLARE global_cursor CURSOR GLOBAL FOR SELECT ...;
-- Persists until explicitly deallocated or connection ends

SQL Server Cursor Types:

STATIC: Insensitive snapshot copied to tempdb
KEYSET: Keys frozen at OPEN; values fetched fresh
DYNAMIC: Fully sensitive to all changes
FAST_FORWARD: Optimized forward-only read-only

Common Declaration Patterns

Let's examine common cursor declaration patterns for typical use cases. Each pattern balances functionality, performance, and resource consumption.

cursor_patterns.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
-- PATTERN 1: Simple iteration (most common)
-- Use case: Process each row exactly once, in order
DECLARE simple_iteration CURSOR FAST_FORWARD FOR
    SELECT order_id, customer_id, total
    FROM orders
    WHERE status = 'pending'
    ORDER BY order_date;
-- Fast, minimal resources, can't go backward
 
-- PATTERN 2: Report generation with consistent snapshot
-- Use case: Generate report that must be self-consistent
DECLARE report_cursor CURSOR STATIC FOR
    SELECT d.department_name, 
           COUNT(*) as emp_count,
           AVG(e.salary) as avg_salary
    FROM departments d
    LEFT JOIN employees e ON d.id = e.department_id
    GROUP BY d.department_name;
-- Snapshot at OPEN; other transactions can't affect results
 
-- PATTERN 3: Update-as-you-go processing
-- Use case: Examine each row and conditionally update it
DECLARE update_cursor CURSOR FOR
    SELECT account_id, balance, last_activity
    FROM accounts
    WHERE last_activity < DATEADD(YEAR, -1, GETDATE())
    FOR UPDATE OF status;
-- Can use WHERE CURRENT OF to update status
 
-- PATTERN 4: Batch commit processing (large datasets)
-- Use case: Process millions of rows with periodic commits
DECLARE batch_cursor CURSOR GLOBAL FORWARD_ONLY FOR
    SELECT transaction_id, amount, processed
    FROM transactions
    WHERE processed = 0
    ORDER BY transaction_id;
-- GLOBAL survives batch boundaries; commit every N rows
 
-- PATTERN 5: Scrollable lookup cursor
-- Use case: Random access to result set positions
DECLARE lookup_cursor SCROLL CURSOR KEYSET FOR
    SELECT employee_id, name, department
    FROM employees
    WHERE active = 1
    ORDER BY name;
-- Can FETCH ABSOLUTE to jump to specific position
 
-- PATTERN 6: Parameterized cursor (PL/SQL)
-- Use case: Reusable cursor for different inputs
DECLARE
    CURSOR dept_employees(p_dept_id NUMBER) IS
        SELECT employee_id, salary
        FROM employees
        WHERE department_id = p_dept_id;
BEGIN
    OPEN dept_employees(10);  -- IT department
    -- process...
    CLOSE dept_employees;
    
    OPEN dept_employees(20);  -- Sales department
    -- process...
    CLOSE dept_employees;
END;

Pattern Selection Guide
Use Case	Recommended Options	Key Benefit
Simple forward processing	FAST_FORWARD / FORWARD_ONLY READ_ONLY	Maximum performance
Consistent reporting	STATIC / INSENSITIVE	Snapshot isolation
Row-by-row updates	FOR UPDATE (no special type)	Positioned modifications
Large batch with commits	GLOBAL + WITH HOLD	Transaction partitioning
Random position access	SCROLL + KEYSET/STATIC	Bidirectional navigation
Real-time monitoring	DYNAMIC / SENSITIVE	See concurrent changes

Start Simple, Add Options as Needed

Begin with the simplest cursor (forward-only, read-only) and add options only when requirements demand them. Each option adds overhead or complexity. FAST_FORWARD or equivalent should be your default starting point.

Summary: DECLARE CURSOR

The DECLARE CURSOR statement establishes the fundamental characteristics of your cursor. Let's consolidate the key concepts:

Key Takeaways

•Basic declaration associates name with query: The cursor name becomes your handle for all operations; the SELECT defines the result set.
•Scrollability (SCROLL/NO SCROLL) determines navigation: SCROLL enables bidirectional movement but requires materialization. Use only when needed.
•Sensitivity (INSENSITIVE/SENSITIVE/ASENSITIVE) controls visibility: Insensitive cursors work with snapshots; sensitive cursors see real-time changes.
•Holdability (WITH HOLD) enables cross-transaction persistence: Useful for batch processing but adds resource overhead.
•Updatability (FOR UPDATE) enables positioned modifications: Required for WHERE CURRENT OF operations; limits query structure.
•Platform syntax varies significantly: SQL Server, Oracle, PostgreSQL, and MySQL each have different options and defaults.
•Start simple, add complexity as needed: FAST_FORWARD/FORWARD_ONLY READ_ONLY should be your default starting point.

What's Next:

With the cursor declared, the next step is using it. The next page covers the cursor operation lifecycle in depth: OPEN, FETCH, and CLOSE—the commands that execute the query, retrieve rows, and release resources. We'll examine fetch patterns, loop structures, and proper resource management.

Page Complete

You now understand how to declare cursors with precise control over scrollability, sensitivity, holdability, and updatability. You know the platform-specific variations and common declaration patterns. Next, we'll learn the operational commands that bring these declarations to life.