Defines a cursor, by associating a select statement with a cursor name. You can use declare cursor with an archive database.
declare cursor_name
[semi_sensitive | insensitive] [scroll | no scroll] [release_locks_on_close]
cursor for select_statement
[for {read only | update [of column_name_list]}]
is the name of the cursor being defined.
is the query that defines the cursor result set. See select for more information.
specifies that the data changes made independently of the cursor may be visible to the cursor result set. The visibility of the dependent data changes depends on the query plan chosen by the optimizer. If there is no worktable created in the plan, the data changes are visible to the result set. The default is semi_sensitive.
specifies that the data changes made independently of the cursor are not visible to the cursor result set. If you do not specify this argument, the default is semi_sensitive. You cannot update an insensitive cursor.
specifies whether the declared cursor is scrollable. Scrollable cursors allowing you fetch the cursor result set nonsequentially, allowing you to scan the cursor back and forth. You cannot update a scrollable cursor.
allows you to configure the lock-releasing behavior of each cursor so that the shared locks can be released when the cursor is closed, even if the transaction is active. This option applies to cursors of all types.
specifies that the cursor result set cannot be updated.
specifies that the cursor result set is updatable.
is the list of columns from the cursor result set (specified by the select_statement) defined as updatable. Adaptive Server also allows you to include columns that are not specified in the list of columns of the cursor’s select_statement (and excluded from the result set), but that are part of the tables specified in the select_statement.
Defines a result set for the authors_crsr cursor that contains all authors from the authors table who do not reside in California:
declare authors_crsr cursor for select au_id, au_lname, au_fname from authors where state != 'CA'
Defines a read-only result set for the titles_crsr cursor that contains the business-type books from the titles table:
declare titles_crsr cursor for select title, title_id from titles where title_id like "BU%" for read only
Defines an updatable result set for the pubs_crsr cursor that contains all of the rows from the publishers table. It defines the address of each publisher (city and state columns) for update:
declare pubs_crsr cursor for select pub_name, city, state from publishers for update of city, state
Defines an insensitive scrollable result set for the stores_scrollcrsr that contains the book stores in California:
declare stores_scrollcrsr insensitive scroll cursor for select stor_id, stor_name from stores where state = 'CA'
Defines an insensitive nonscrollable result set for the stores_scrollcrsr that contains the book stores in California:
declare stores_scrollcrsr insensitive no scroll cursor for select stor_id, stor_name from stores where state = 'CA'
A declare cursor statement must precede any open statement for that cursor.
You cannot include other statements with declare cursor in the same Transact-SQL batch.
You can include up to 1024 columns in an update clause of a client’s declare cursor statement.
cursor_name must be a valid Adaptive Server identifier containing no more than 30 characters.
You cannot update a scrollable cursor.
You cannot update an insensitive cursor.
select_statement can use the full syntax and semantics of a Transact-SQL select statement, with these restrictions:
Must contain a from clause
Cannot contain a compute, for browse, or into clause
Can contain the holdlock keyword
The select_statement can contain references to Transact-SQL parameter names or Transact-SQL local variables (for all cursor types except language). The names must reference the Transact-SQL parameters and local variables defined in the procedure, trigger, or statement batch that contains the declare cursor statement.
The parameters and local variables referenced in the declare cursor statement do not have to contain valid values until the cursor is opened.
The select_statement can contain references to the inserted and deleted temporary tables that are used in triggers.
A cursor’s existence depends on its scope. The scope refers to the context in which the cursor is used, that is, within a user session, within a stored procedure, or within a trigger.
Within a user session, the cursor exists only until the user ends the session. The cursor does not exist for any additional sessions started by other users. After the user logs off, Adaptive Server deallocates the cursors created in that session.
If a declare cursor statement is part of a stored procedure or trigger, the cursor created within it applies to stored procedure or trigger scope and to the scope that launched the stored procedure or trigger. Cursors declared inside a trigger on an inserted or a deleted table are not accessible to any nested stored procedures or triggers. However, cursors declared inside a trigger on an inserted or a deleted table are accessible within the scope of the trigger. Once the stored procedure or trigger completes, Adaptive Server deallocates the cursors created within it.
Figure 1-3 illustrates how cursors operate between scopes.
Figure 1-3: How cursors operate within scopes
A cursor name must be unique within a given scope. Adaptive Server detects name conflicts within a particular scope only during runtime. A stored procedure or trigger can define two cursors with the same name if only one is executed. For example, the following stored procedure works because only one names_crsr cursor is defined in its scope:
create procedure proc2 @flag int as if @flag > 0 declare names_crsr cursor for select au_fname from authors else declare names_crsr cursor for select au_lname from authors return
Cursor result set rows may not reflect the values in the actual base table rows. For example, a cursor declared with an order by clause usually requires the creation of an internal table to order the rows for the cursor result set. Adaptive Server does not lock the rows in the base table that correspond to the rows in the internal table, which permits other clients to update these base table rows. In this case, the rows returned to the client from the cursor result set are not in sync with the base table rows.
A cursor result set is generated as the rows are returned through a fetch of that cursor. This means that a cursor select query is processed like a normal select query. This process, known as a cursor scan, provides a faster turnaround time and eliminates the need to read rows that are not required by the application.
A restriction of cursor scans is that they can only use the unique indexes of a table. However, if none of the base tables referenced by the cursor result set are updated by another process in the same lock space as the cursor, the restriction is unnecessary. Adaptive Server allows the declaration of cursors on tables without unique indexes, but any attempt to update those tables in the same lock space closes all cursors on the tables.
After defining a cursor using declare cursor, Adaptive Server determines whether the cursor is updatable or read-only. If:
A cursor is updatable – you can update or delete rows through the cursor; that is, use cursor_name to do a position update or delete.
A cursor is read-only – you cannot use cursor_name to perform a position update or delete.
Use the for update or for read only clause to explicitly define a cursor as updatable or read-only. You cannot define an updatable cursor if its select_statement contains one of the following constructs:
distinct option
group by clause
Aggregate function
Subquery
union operator
at isolation read uncommitted clause
If you do not specify either the for update or the read only clause, Adaptive Server checks to see whether the cursor is updatable.
Adaptive Server also defines a cursor as read-only if you declare a language- or server-type cursor that includes an order by clause as part of its select_statement. Adaptive Server handles updates differently for client- and execute-type cursors, thereby eliminating this restriction.
release_locks_on_close has no effect if the cursor scan occurs at isolation level 1.
The default behavior at isolation levels 2 and 3 if the transaction is committed or rolled back before the cursor is closed is for Adaptive Server to release the shared locks acquired by the cursor until that point, with the exception of the lock on the last fetched row. If you use release_on_locks_close, the shared locks acquired by the cursor exist until the cursor is closed.
Use sp_cursorinfo to determine if a cursor was declared with the release_on_locks_close parameter:
1) sp_cursorinfo 2> go
Cursor name 'c' is declared at nesting level '0'. The cursor is declared as NON-SCROLLABLE RELEASE_LOCKS_ON_CLOSE cursor. The cursor id is 917505. The cursor has been successfully opened 0 times. The cursor will remain open when a transaction is committed or rolled back.
If you do not specify a column_name_list with the for update clause, all the specified columns in the query are updatable. Adaptive Server attempts to use unique indexes for updatable cursors when scanning the base table. For cursors, Adaptive Server considers an index containing an IDENTITY column to be unique, even if it is not so declared.
If you do not specify the for update clause, Adaptive Server chooses any unique index, although it can also use other indexes or table scans if no unique index exists for the specified table columns. However, when you specify the for update clause, Adaptive Server must use a unique index defined for one or more of the columns to scan the base table. If none exists, it returns an error.
In most cases, include only columns to be updated in the column_name_list of the for update clause. If the table has only one unique index, you do not need to include its column in the for update column_name_list; Adaptive Server finds it when it performs the cursor scan. If the table has more than one unique index, do not include any of them in the for update column_name_list.
This allows Adaptive Server to use that unique index for its cursor scan, which helps prevent an update anomaly called the Halloween problem. Another way to prevent the Halloween problem is to create tables with the unique auto_identity index database option. See the System Administration Guide.
The Halloween problem occurs when a client updates a column of a cursor result set row that defines the order in which the rows are returned from the base tables. For example, if Adaptive Server accesses a base table using an index, and the index key is updated by the client, the updated index row can move within the index and be read again by the cursor. This is a result of an updatable cursor only logically creating a cursor result set. The cursor result set is actually the base tables that derive the cursor.
If you specify the read only option, you cannot update the cursor result set using the cursor name to perform update or delete.
If you do specify insensitive or semi_sensitive when you execute declare cursor, the default sensitivity is implicit, so that the cursor’s sensitivity depends on the query plan chosen by the optimizer. If the query plan has any worktable created, the cursor becomes insensitive.
If you specify the cursor’s sensitivity to be semisensitive, sensitivity also depends on the query plan.
If you specify insensitive, the cursor is read_only. You cannot use a for update clause in a cursor declaration.
If you do not specify the cursor’s scrollability, no scroll is implied.
All scrollable cursors are read-only. You cannot use a for update clause in a cursor declaration.
ANSI SQL – Compliance level: Entry-level compliant.
declare cursor permission defaults to all users. No permission is required to use it.
Commands open
