Operations in a relational database act on a complete set of rows. The set of rows returned by a SELECT statement consists of all the rows that satisfy the conditions in the WHERE clause of the statement. This complete set of rows returned by the statement is known as the result set. Applications, especially interactive online applications, cannot always work effectively with the entire result set as a unit. These applications need a mechanism to work with one row or a small block of rows at a time. Cursors are an extension to result sets that provide that mechanism.
Cursors extend result processing by:
Allowing positioning at specific rows of the result set.
Retrieving one row or block of rows from the current position in the result set.
Supporting data modifications to the rows at the current position in the result set.
Supporting different levels of visibility to changes made by other users to the database data that is presented in the result set.
Providing Transact-SQL statements in scripts, stored procedures, and triggers access to the data in a result set.
Requesting a Cursor
Microsoft SQL Server 2005 supports two methods for requesting a cursor:
Transact-SQL
The Transact-SQL language supports a syntax for using cursors modeled after the SQL-92 cursor syntax.
Database application programming interface (API) cursor functions
SQL Server supports the cursor functionality of these database APIs:
ADO (Microsoft ActiveX Data Object)
OLE DB
ODBC (Open Database Connectivity)
An application should never mix these two methods of requesting a cursor. An application that has used the API to specify cursor behaviors should not then execute a Transact-SQL DECLARE CURSOR statement to also request a Transact-SQL cursor. An application should only execute DECLARE CURSOR if it has set all the API cursor attributes back to their defaults.
If neither a Transact-SQL nor API cursor has been requested, SQL Server defaults to returning a complete result set, known as a default result set, to the application.
FETCH (Transact-SQL)
FETCH
[ [ NEXT | PRIOR | FIRST | LAST
| ABSOLUTE { n | @nvar }
| RELATIVE { n | @nvar }
]
FROM
]
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }
[ INTO @variable_name [ ,...n ] ]
Arguments :
NEXT
Returns the result row immediately following the current row and increments the current row to the row returned. If FETCH NEXT is the first fetch against a cursor, it returns the first row in the result set. NEXT is the default cursor fetch option.
PRIOR
Returns the result row immediately preceding the current row, and decrements the current row to the row returned. If FETCH PRIOR is the first fetch against a cursor, no row is returned and the cursor is left positioned before the first row.
FIRST
Returns the first row in the cursor and makes it the current row.
LAST
Returns the last row in the cursor and makes it the current row.
ABSOLUTE { n | @nvar}
If n or @nvar is positive, returns the row n rows from the front of the cursor and makes the returned row the new current row. If n or @nvar is negative, returns the row n rows before the end of the cursor and makes the returned row the new current row. If n or @nvar is 0, no rows are returned. n must be an integer constant and @nvar must be smallint, tinyint, or int.
RELATIVE { n | @nvar}
If n or @nvar is positive, returns the row n rows beyond the current row and makes the returned row the new current row. If n or @nvar is negative, returns the row n rows prior to the current row and makes the returned row the new current row. If n or @nvar is 0, returns the current row. If FETCH RELATIVE is specified with n or @nvar set to negative numbers or 0 on the first fetch done against a cursor, no rows are returned. n must be an integer constant and @nvar must be smallint, tinyint, or int.
GLOBAL
Specifies that cursor_name refers to a global cursor.
cursor_name
Is the name of the open cursor from which the fetch should be made. If both a global and a local cursor exist with cursor_name as their name, cursor_name to the global cursor if GLOBAL is specified and to the local cursor if GLOBAL is not specified.
@cursor_variable_name
Is the name of a cursor variable referencing the open cursor from which the fetch should be made.
INTO @variable_name[ ,...n]
Allows data from the columns of a fetch to be placed into local variables. Each variable in the list, from left to right, is associated with the corresponding column in the cursor result set. The data type of each variable must either match or be a supported implicit conversion of the data type of the corresponding result set column. The number of variables must match the number of columns in the cursor select list.
Remarks
If the SCROLL option is not specified in an SQL-92 style DECLARE CURSOR statement, NEXT is the only FETCH option supported. If SCROLL is specified in an SQL-92 style DECLARE CURSOR, all FETCH options are supported.
When the Transact-SQL DECLARE cursor extensions are used, these rules apply:
If either FORWARD_ONLY or FAST_FORWARD is specified, NEXT is the only FETCH option supported.
If DYNAMIC, FORWARD_ONLY or FAST_FORWARD are not specified, and one of KEYSET, STATIC, or SCROLL are specified, all FETCH options are supported.
DYNAMIC SCROLL cursors support all the FETCH options except ABSOLUTE.
The @@FETCH_STATUS function reports the status of the last FETCH statement. The same information is recorded in the fetch_status column in the cursor returned by sp_describe_cursor. This status information should be used to determine the validity of the data returned by a FETCH statement prior to attempting any operation against that data. For more information, see @@FETCH_STATUS.
Example :
This example creates a SCROLL cursor to allow full scrolling capabilities through the LAST, PRIOR, RELATIVE, and ABSOLUTE options.
-- Execute the SELECT statement alone to show the
-- full result set that is used by the cursor.
SELECT LastName, FirstName FROM Person.Contact
ORDER BY LastName, FirstName
-- Declare the cursor.
DECLARE contact_cursor SCROLL CURSOR FOR
SELECT LastName, FirstName FROM Person.Contact
ORDER BY LastName, FirstName
OPEN contact_cursor
-- Fetch the last row in the cursor.
FETCH LAST FROM contact_cursor
-- Fetch the row immediately prior to the current row in the cursor.
FETCH PRIOR FROM contact_cursor
-- Fetch the second row in the cursor.
FETCH ABSOLUTE 2 FROM contact_cursor
-- Fetch the row that is three rows after the current row.
FETCH RELATIVE 3 FROM contact_cursor
-- Fetch the row that is two rows prior to the current row.
FETCH RELATIVE -2 FROM contact_cursor
CLOSE contact_cursor
DEALLOCATE contact_cursor
GO
