When accessing data using cursors, Adaptive Server divides the process into several operations:
Declaring the cursor When you declare a cursor, Adaptive Server creates a cursor structure. The server does not compile the cursor from the cursor declaration, however, until the cursor is open.
The following cursor declaration of a default, nonscrollable cursor, business_crsr, finds the titles and identification numbers of all business books in the titles table.
declare business_crsr cursor for select title, title_id from titles where type = "business" for update of price
Using the for update clause when declaring a cursor ensures that Adaptive Server correctly performs the positioned updates. In this example, it allows you to use the cursor to change the price.
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'
Because scrollable cursors are read-only, you cannot use a for update clause in a cursor declaration.
Opening a 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 the preliminary operations for scanning the rows defined in the cursor and is ready to return a result row.
When you declare a cursor within a stored procedure, Adaptive Server compiles the cursor the first time the stored procedure is called. Adaptive Server also generates an optimized query plan, and stores the plan for later use. When the stored procedure is called again, 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 a 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 nonscrollable 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 a fetch orientation in a fetch statement. The orientation options are first, last, next, prior, absolute, and relative. fetch for scrollable cursors executes in both forward and backward directions, and the result set can be scanned repeatedly.
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 the orientation option prior, returns the row before the tenth row:
fetch prior authors_scroll_crsr au_fname au_lname ------------------ Chastity Locksley
Processing a row Adaptive Server updates or deletes the data in the cursor result set (and in the corresponding base tables that supplied the data) at the current cursor position. These operations are 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. You cannot use cursors to insert rows. All updates performed through a cursor affect the corresponding base tables included in the cursor result set.
Closing a 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. Use the close command to close a cursor. 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 a cursor Adaptive Server removes the query plan from memory and eliminates all trace of the cursor structure. To deallocate a cursor, use the deallocate cursor command. For example:
deallocate cursor business_crsr
In Adaptive Server version 15.0 and later, The keyword cursor is optional for this command.
You must declare the cursor again before using it.
