Sharing Database Driven Software Development Experiences and Views

Cursor Declaration

---

DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,…n ] ] ]

---

[ LOCAL | GLOBAL ]

This specifies the Scope of Cursors. LOCAL means that this CURSOR is local to Batch, Stored Procedure or Trigger in which it is created and is only valid in within this scope.

GLOBAL specifies that the scope of CURSOR is global to connection and can be referenced in any Stored Procedure, Trigger or Batch executed by this Connection.

However, if neither GLOBAL nor LOCAL is specified, the default will be LOCAL.

[ FORWARD_ONLY | SCROLL ]

It defines the Scroll ability of CURSOR. FORWARD_ONLY cursors can fetch only the next row whereas SCROLL cursors fetch rows in any direction and by any number.

However, FORWARD_ONLY cursors perform faster.

[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

STATIC CURSOR has no real time requirement. It just copies Data in Result Set into TEMPDB and fetches row from TEMPDB.

KEYSET CURSOR copies the primary key data into TEMPDB. This is the default CURSOR type. It is more time consuming than DYNAMIC but requires less resources.

DYNAMIC CURSOR also stores keys but the key data is refreshed with each modification to key data on base tables and thus it can keep track of inserted, deleted or modified rows. This is expensive in terms of used resources.

FAST_FORWARD CURSOR only fetches the next row and provides optimal performance.

[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

READ ONLY – specifies that data set in cursor cannot be updated.

SCROLL_LOCKS – specifies that cursor will lock the rows to ensure that updates or deletes made through this will be succeeded.

OPTIMISTIC – specifies that cursor does not lock rows and updates or deletes made through this will not succeed if the row has been updated outside this cursor meanwhile.

[ TYPE_WARNING ]

it specifies that client will receive warning message if any implicit conversion occurs in Cursor.

FOR select_statement

This is standard SELECT statement that defines the result set of the cursor.

[ FOR UPDATE [ OF column_name [ ,…n ] ] ]

It defines modifiable columns within the cursor. If this is specified without a column list, all columns can be updated.

---

@@Fetch_Status

“Fetch” is used to retrieve rows from Cursor. @@Fetch_Status will be equal to zero for a successful return of row. It can have three values.

0 -Row successfully returned.
-1 -Fetch statement has read beyond the number of rows in Cursor.
-2 -Row no longer exists in result set.

Example

---

DECLARE employeeID int, employeeBalance double;

DECLARE EmployeeBalanceModificationCursor
CURSOR
LOCAL — [ LOCAL | GLOBAL ]
FORWARD_ONLY — [ FORWARD_ONLY | SCROLL ]
KEYSET — [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
— [ STATIC FAST_FORWARD options are incompatible with FOR UPDATE ]
SCROLL_LOCKS — [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
— [ READ_ONLY is incompatible with FOR UPDATE ]
TYPE_WARNING — [ TYPE_WARNING ]
— [ INFORM client of implicit conversions ]
FOR SELECT employee_ID, employee_Balance
FROM HRMDB.t_EmployeeInformation
ORDER BY employee_ID
FOR UPDATE — [ FOR UPDATE [ OF column_name [ ,…n ] ] ]
;

OPEN EmployeeBalanceModificationCursor; –[ OPEN CURSOR ]

FETCH NEXT FROM EmployeeBalanceModificationCursor –[ FETCH First row ]
INTO @employeeID, @employeeBalance ;

WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE HRMDB.t_EmployeeInformation — [ Change Balance of current employee ]
SET employeeBalance = @employeeBalance + 5000
WHERE CURRENT OF EmployeeBalanceModificationCursor;

FETCH NEXT FROM EmployeeBalanceModificationCursor –[ FETCH Next row ]
INTO @employeeID, @employeeBalance ;

END

CLOSE EmployeeBalanceModificationCursor; — [ CLOSE Cursor]
DEALLOCATE EmployeeBalanceModificationCursor; –[ DEALLOCATE Cursor]

---

This is very simple example of Cursor; it is not the correct choice for this kind of scenario. Cursor is only advisable for some dynamic operation that can’t be done with other available alternatives.

Thanks
A Rahim Khan