The operation to retrieve a row from a cursor is called a fetch. These are the fetch options:
Fetches the first row in the cursor.
Fetches the row after the last row fetched.
Fetches the row before the last row fetched.
Fetches the last row in the cursor.
Fetches the nth row from the first row in the cursor if n is a positive integer. If n is a negative integer, the row n rows before the end of the cursor is fetched. If n is 0, no rows are fetched.
Fetches the row n rows from the last row fetched. If n is positive, the row n rows after the last row fetched is fetched. If n is negative, the row n rows before the last row fetched is fetched. If n is 0, the same row is fetched again.
When a cursor is opened, the current row position in the cursor is logically before the first row. This causes the different fetch options to have the following behaviors if they are the first fetch performed after the cursor is opened:
Fetches the first row in the cursor.
Fetches the first row in the cursor.
Does not fetch a row.
Fetches the last row in the cursor.
Fetches the nth row from the first row in the cursor if n is a positive integer. If n is a negative integer, then the row n rows before the end of the cursor is fetched (for example, n = -1 returns the last row in the cursor). If n is 0, no rows are fetched.
Fetches the nth row in the cursor if n is positive. No rows are fetched if n is negative or 0.
Transact-SQL cursors are limited to fetching one row at a time. API server cursors support fetching blocks of rows with each fetch. A cursor that supports fetching multiple rows at a time is called a block cursor.
A cursor can be classified by the fetch options it supports:
Rows must be fetched serially from the first row to the last row. FETCH NEXT is the only fetch operation allowed.
Rows can be randomly fetched from anywhere in the cursor. All the fetch operations are allowed (except that dynamic cursors do not support fetch absolute).
Scrollable cursors are especially useful for supporting online applications. A cursor can be mapped to a grid or list box in the application. As the user scrolls up and down and all around the grid, the application uses scroll fetches to retrieve the rows from the cursor the user wants to see.
The APIs for the actual statements, functions, or methods used have different names to fetch rows:
Cursor Functions | Using Cursors |
FETCH |