How processes cursors

When accessing data using cursors, Adaptive Server divides the process into several operations:

Declaring the cursor When you declare the cursor, Adaptive Server creates the cursor structure. The server does not compile the cursor from the cursor declaration, however, until the cursor is open.

For the syntax and explanation of declare cursor, see “declare cursor syntax”. For complete documentation of fetch and declare cursor, see Vol.2, Commands, in the Reference Manual.

The following cursor declaration of a default, non-scrollable cursor, business_crsr, finds the titles and identification numbers of all business books in the titles table. It also allows you to update the price column in the future through the cursor:

declare business_crsr cursor
for select title, title_id
from titles
where type = "business"
for update of price

If you want to make data changes through the cursor, use the for update clause when declaring a cursor, to ensure that Adaptive Server performs the positioned updates correctly.

This example declares a scrollable cursor, authors_scroll_crsr, which finds authors from California in the authors table.

declare authors_scroll_crsr scroll cursor
for select au_fname, au_lname
from authors
where state = 'CA'

Scrollable cursors are read-only. for update clauses cannot be used in the cursor declaration.

Opening the cursor When you open a cursor that has been declared outside of a stored procedure, Adaptive Server compiles the cursor and generates an optimized query plan. It then performs preliminary operations for executing the scan defined in the cursor and ready to returning a result row.

If a cursor has been declared within a stored procedure, and the stored procedures is called for the first time, Adaptive Server compiles the cursor, generates an optimized query plan, and stores the plan for later use. If the stored procedure is called again later, the cursor already exists in compiled form. When the cursor is opened, Adaptive Server needs only to perform preliminary operations for executing a scan and returning a result set.

Since Transact-SQL statements are compiled during the open phase of a cursor, any error messages related to declaring the cursor appear during the cursor open phase.

Fetching from the cursor The fetch command executes the compiled cursor to return one or more rows meeting the conditions defined in the cursor. By default, a fetch returns only a single row.

In non-scrollable cursors, the first fetch returns the first row that meets the cursor’s search conditions, and stores the current position of the cursor. The second fetch uses the cursor position from the first fetch, returns the next row that meets the search conditions, and stores its current position. Each subsequent fetch uses the cursor position of the previous fetch to locate the next cursor row.

In scrollable cursors, you can fetch any rows and set the current cursor position to any row in the result set, by specifying the fetch orientation option first, last, next, prior, absolute or relative in a fetch statement. fetch for scrollable cursors executes both forward and backward directions, and the result set can be scanned repeatly.

You can change the number of rows returned by a fetch by using set cursor rows. See “Getting multiple rows with each fetch”.

In the following example, the fetch command displays the title and identification number of the first row in the titles table containing a business book:

fetch business_crsr

title                                 title_id
-----------------------------------   --------
The Busy Executive’s Database Guide   BU1032 
 
(1 row affected)

Running fetch business_crsr a second time displays the title and identification number of the next business book in titles.

In the following example, the first fetch command to a scrollable cursor displays the tenth row in the authors table, containing authors from California:

fetch absolute 10 authors_scroll_crsr
au_fname au_lname
--------------------
Akiko Yokomoto

A second fetch with orientation prior returns the row before the tenth row:

fetch prior authors_scroll_crsr
au_fname au_lname
------------------
Chastity Locksley

Processing the row By examining, updating, or deleting it through the cursor, Adaptive Server updates or deletes the data in the cursor result set (and in the corresponding base tables that derived the data) at the current cursor position after a fetch. This operation is optional.

The following update statement raises the price of business books by 5 percent; it affects only the book currently pointed to by the business_crsr cursor:

update titles
set price = price * .05 + price
where current of business_crsr

Updating a cursor row involves changing data in the row or deleting the row completely. You cannot use cursors to insert rows. All updates through a cursor affect the corresponding base tables included in the cursor result set.

Closing the cursor Adaptive Server closes the cursor result set, removes any remaining temporary tables, and releases the server resources held for the cursor structure. However, it keeps the query plan for the cursor so that it can be opened again. For example:

close business_crsr

When you close a cursor and then reopen it, Adaptive Server re-creates the cursor result, and positions the cursor before the first valid row. This allows you to process a cursor result set as many times as necessary. You can close the cursor at any time; you do not have to go through the entire result set.

Deallocating the cursor Adaptive Server removes the query plan from memory and eliminates all trace of the cursor structure. For example:

deallocate cursor business_crsr

The keyword cursor is optional in Adaptive Server 15.0 or later.

You must declare the cursor again before using it.