declare_cursor [insensitive | semi_sensitive] [scroll | no scroll] cursor
for select_statement for {read_only | update [of column_name_list]}
cursor_name must be a valid Adaptive Server identifier containing no more than 30 characters, and must start with a letter, a pound sign (#), or an underscore (_).
The select_statement is the query that defines the cursor result set. See select in the Reference Manual for information about its options. In general, select_statement may use the full syntax and semantics of a Transact-SQL select statement, including the holdlock keyword. However, it cannot contain a compute, for browse, or into clause.
You can use either insensitive or semi_sensitive to explicitly specify cursor_sensitivity
An insensitive cursor is a snapshot of the result set, taken when the cursor is opened. An internal worktable is created and fully populated with the cursor result set when you open the cursor.
Any locks on the base tables are released, and only the worktable is accessed when you execute fetch. Any data changes in the base table on which the cursor is declared do not affect the cursor result set. The cursor is read-only, and cannot be used with for update.
In a semi_sensitive cursor, some data changes in the base tables may appear in the cursor. The query plan chosen and whether the data rows have been fetched at least once may affect the visibility of the base table data change.
semi_sensitive scrollable cursors are like insensitive cursors, in that they use a worktable to hold the result set for scrolling purposes. But in semi_sensitive mode, the cursor’s worktable materializes as the rows are fetched, rather han at the time you open the cursor. The membership of the result set is fixed only after all the rows have been fetched once, and copied to the scrolling worktable.
If you do not specify cursor_sensitivity, the default value is semi_sensitive.
Even if you declare a cursor semi_sensitive, the visibility of data changes in the base table of the cursor depends on the query plan chosen by the optimizer.
Any sort command forces the cursor to become insensitive, even if you have declared it semi_sensitive, because it requires the rows in a table to be ordered before sort can be executed. A worktable, however, may be populated before any rows can be fetched.
For example, if a select statement contains an order by clause, and there is no index on the order by column, the worktable is fully populated at the time the cursor is opened, whether or not you declare the cursor to be semi_sensitive. So the cursor becomes insensitive.
Generally, rows that have not yet been fetched may display data changes, while rows that have already been fetched do not.
The main benefit of using a semi_sensitive scrollable cursor instead of an insensitive scrollable cursor is that the first row of the result set is returned promptly to the user, since the table lock is applied row by row. If you fetch a row and update it, it goes to the worktable through fetch, and the update is done on the base table. There is no need to wait for the result set worktable to be fully populated.
You can use either scroll or no scroll to specify cursor_scrollability. If the cursor is scrollable, you can scroll through the cursor result set by fetching any, or many rows back and forth; you can also scan the result set repeatedly. A scrollable cursor allows you to set the position of the cursor anywhere in the cursor result set for as long as the cursor is open, by specifying an orientation option: first, last, absolute , next, prior, or relative in a fetch statement. Scrollable cursors require a worktable to be created for the purpose of scrolling. A non-scrollable cursor allows you to select rows only in a forward direction. You cannot go back to a row already fetched, unless you close and reopen the cursor and start fetch from the beginning again.
All scrollable cursors are read-only, and cannot be used with for update in a cursor declaration.
The read_only option specifies that the cursor result set cannot be updated. In contrast, the for update option specifies that the cursor result set is updatable. You can specify of column_name_list after for update with the list of columns from the select_statement defined as updatable.
The declare cursor statement must precede any open statement for that cursor. You cannot combine declare cursor with other statements in the same Transact-SQL batch, except when using a cursor in a stored procedure.
The following declare cursor statement defines a result set for the authors_crsr cursor that contains all authors that do not reside in California:
declare authors_crsr cursor for select au_id, au_lname, au_fname from authors where state != 'CA' for update
The following example defines an insensitive scrollable result set, of the stores_scrollcrsr, containing bookstores in California, for:
declare storinfo_crsr insensitive scroll cursor for select stor_id, stor_name, payterms from stores where state = "CA"
For the syntax of declare cursor, see “Declaring cursors”.
If you do not specify scrollability or sensitivity in the declare statement, the cursor becomes the default cursor. The default cursor is semi_sensitive and non-scrollable, and can fetch only in a forward direction.In a non-scrollable cursor, the only fetch orientation you can execute is next; if you do not specify the direction of the fetch, Adaptive Server uses the default orientation value (next) to find the row.
Once you have declared a cursor scrollable and open, use fetch with the orientation keywords to specify which row you want from the result set.For the syntax of the fetch command, see “fetch syntax”.T
To declare an insensitive, non-scrollable cursor, called “C1,” enter:
declare C1 insensitive cursor for select fname from emp_tab
To declare an insensitive, scrollable cursor, called “C3,”enter:
declare C3 insensitive scroll cursor for select fname from emp_tab
Once you have declared a cursor scrollable and open, use fetch with the orientation keywords to specify which row you want from the result set. For example, to fetch the first row, enter:
fetch first [from] <cursor_name>
You can also fetch the columns of the first row from the result set. To place them in the variables you specify in <fetch target list>, enter:
fetch first from <cursor_name> into <fetch_target_list>
You can fetch the 20th row in the result set directly, regardless of the cursor’s current position:
fetch absolute 20 from <cursor_name> into <fetch target list>
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
