How SAP ASE Processes Cursors

When accessing data using cursors, SAP ASE divides the process into several operations.

When you declare a cursor, SAP ASE 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 SAP ASE 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.
When you open a cursor that has been declared outside of a stored procedure, SAP ASE 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, SAP ASE compiles the cursor the first time the stored procedure is called. SAP ASE 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, SAP ASE needs only to perform preliminary operations for executing a scan and returning a result set.

Note: 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.
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.

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
```
SAP ASE 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.
SAP ASE 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, SAP ASE 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.
SAP ASE 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 SAP ASE version 15.0 and later, The keyword cursor is optional for this command.

You must declare the cursor again before using it.