The complete documentation of the fetch statement is in the Reference Manual.
fetch [next | prior | first | last | absolute fetch_offset | relative fetch_offset] [from ] cursor_name [into fetch_target_list]
first, next, prior, last, absolute, and relative specify the fetch direction of the scrollable cursor. If no keyword is specified, the default value is next. For more information, see the Reference Manual.
If you use fetch absolute or fetch relative, fetch_offset must be specified. It can be a literal of an integer or an exact, signed numeric with a scale of 0. The fetch_offset can also be a Transact-SQL local variable with an integer or numeric datatype, with a scale of 0. When the cursor is positioned beyond the last row or before the first row, no data is returned and no error is raised.
When you use fetch absolute and fetch_offset is greater than or equal to 0, the offset is calculated from the position before the first row of the result set. If fetch absolute is less than 0, the offset is calculated from the position after the last row of the result set.
If you use fetch relative, when fetch_offset n is greater than 0, the cursor is placed n rows after the current position; if fetch_offset n>0, the cursor is placed abs(n) rows before the current position.
For example, with the scrollable cursor stores_scrollcrsr, you can fetch any row you want:
fetch absolute 3 stores_scrollcrsr stor_id stor_name --------------------------------------- 7896 Fricative Bookshop
This fetch positions the cursor on the third row in the result set. A subsequent fetch prior operation positions the cursor on the second row of the result set:
fetch prior stores_scrollcrsr stor_id stor_name --------------------------------------- 7067 News & Brews
A subsequent fetch relative -1 positions the cursor on the first row of the result set:
fetch relative -1 stores_scrollcrsr stor_id stor_name ------------------------- 7066 Barnum's
After generating the cursor result set, in a fetch statement for a non-scrollable cursor, Adaptive Server moves the cursor position one or more rows in the result set. It retrieves the data from the result set and stores the current position, allowing additional fetches until Adaptive Server reaches the end of the result set.
The next example illustrates a non-scrollable cursor. After declaring and opening the authors_crsr cursor, you can fetch the first row of its result set as follows:
fetch authors_crsr
au_id au_lname au_fname ----------- ------------------- --------------- 341-22-1782 Smith Meander (1 row affected)
Each subsequent fetch retrieves another row from the cursor result set. For example:
fetch authors_crsr
au_id au_lname au_fname ----------- ------------------- --------------- 527-72-3246 Greene Morningstar (1 row affected)
After you fetch all the rows, the cursor points to the last row of the result set. If you fetch again, Adaptive Server returns a warning through the @@sqlstatus or @@fetch_status global variables (described under “Checking the cursor status”), indicating that there is no more data. The cursor position remains unchanged.
If you are using non-scrollable cursors, you cannot fetch a row that has already been fetched. Close and reopen the cursor to generate the cursor result set again, and start fetching again from the beginning.
With scrollable cursors, you can use a fetch direction option to fetch any row in the result set. In this example, the 25th row is fetched.
fetch absolute 25 from pubs_crsr into @name, @city, @state
The into clause specifies that Adaptive Server returns column data into the specified variables. The fetch_target_list must consist of previously declared Transact-SQL parameters or local variables.
For example, after declaring the @name, @city, and @state variables, you can fetch rows from the pubs_crsr cursor as follows:
fetch pubs_crsr into @name, @city, @state
You can also fetch only the columns of the first row from the result set. To place the fetch columns in a list, enter:
fetch first from <cursor_name> into <fetch_target_list>
For the syntax of declare cursor, see “Declaring cursors”.
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
