declare cursor

Defines a cursor, by associating a select statement with a cursor name. You can use declare cursor with an archive database.

Syntax

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]}]

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 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.
insensitive – 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.
scroll | no scroll – 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.
release_locks_on_close – 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.
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. The SAP ASE 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 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'
```

Usage

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 SAP ASE identifier containing no more than 30 characters.
You cannot include encrypted columns in the for update clause of a declare cursor statement.
You cannot update a scrollable cursor.
You cannot update an insensitive cursor.

Standards

ANSI SQL – Compliance level: Entry-level compliant.

Permissions

No permission is required to use declare cursor.