Chapter 1: Commands

declare cursor

Description

Defines a cursor, by associating a select statement with a cursor name.

Syntax

declare cursor_name 
	[semi_sensitive | insensitive] [scroll | no scroll] 
		cursor for select_statement 
	[for {read only | update [of column_name_list]}]

Parameters

cursor_name: is the name of the cursor being defined.

select_statement: is the query that defines the cursor result set. See select for more information.

semi_sensitive: specifies the data changes made independently of the cursor, and 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.

insensitive: specifies 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.

scroll | no scroll: specifies whether the declared cursor is scrollable. Scrollable cursors allowing you fetch the cursor result set non-sequencially, allowing you to scan the cursor back and forth. You cannot update an scrollable cursor.

for read only: specifies that the cursor result set cannot be updated.

for update: specifies that the cursor result set is updatable.

of column_name_list: 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.

Examples

Example 1

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'

Example 2

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

Example 3

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

Example 4

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'

Example 5

Defines an insensitive non-scrollable 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'

Usage

Restrictions on cursors

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.

Cursor select statements

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.

Cursor scope

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-1 illustrates how cursors operate between scopes.

Figure 1-1: 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
```

Result set

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 that case, the rows returned to the client from the cursor result set would not be 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.

Updatable cursors

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, using cursor_name to do a position update or delete.
- A cursor is read-only – you cannot use cursor_name to do 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.

Updatable cursors and allpages locking

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, do not include its column in the for update column_name_list because Adaptive Server must use it to perform the cursor scan. If the table has more than one unique index, exclude at least one 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. For more information, 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.

Using scrollable cursors

If you do specify insensitive or semi_sensitive when you 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 semi-sensitive, 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.

Standards

ANSI SQL – Compliance level: Entry-level compliant.

Permissions

declare cursor permission defaults to all users. No permission is required to use it.