declare cursor

Description

Defines a cursor.

Syntax

declare cursor_name 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.

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 (cityand state columns) for update:

declare pubs_crsr cursor
for select pub_name, city, state
from publishers
for update of city, state

Usage


Restrictions on cursors


Cursor select statements


Cursor scope


Result set


Updatable cursors

Standards

SQL92 – Compliance level: Entry-level compliant.

The for update and for read only options are Transact-SQL extensions.

Permissions

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

See also

Commands open