Types of cursors

Types of cursors:

Scrollable :
? Scrollable cursors can move in either direction.
? It can position the cursor anywhere in the result set using the FETCH SQL statement.
? The keyword SCROLL must be specified when declaring the cursor. The default is NO SCROLL.
? The target position for a scrollable cursor can be specified relative to the current cursor position or absolute from the beginning of the result set.

  Syntax:

          DECLARE
cursor_name sensitivity SCROLL CURSOR
FOR
SELECT...
FROM
FETCH [NEXT | PRIOR | FIRST | LAST] FROM cursor_name|
FETCH ABSOLUTE n FROM cursor_name|
FETCH RELATIVE n FROM cursor_name

Non-Scrollable (forward-only):

• Here, we can FETCH each row at most once and the cursor automatically moves to the immediately following row.
• With-Hold cursors
• Cursors will be closed automatically once the transaction is over i.e. when a
commit or rollback occurs. This property of a cursor can be changed by declared it using the WITH HOLD clause.
• Holdable cursor is open for commit and closed for rollback

DECLARE cursor_name CURSOR WITH HOLD FOR SELECT ... FROM...

Posted in: SQL Server Tutorial