Transact-SQL User's Guide

Home

Transact-SQL User's Guide

Chapter 17 Cursors: Accessing Data Row by Row

How cursors work

How Adaptive Server processes cursors

Figure 17-2 shows the steps in using cursors.

Figure 17-2: Using cursors raster

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

Declaring the cursor
If you declare the cursor within a stored procedure, Adaptive Server creates the cursor structure and compiles the query defined for that cursor. It stores the compiled query plan but does not execute it.
For example, the following cursor declaration, 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
```
Use the for update clause when declaring a cursor, to ensure that Adaptive Server performs the positioned updates correctly.
Opening the cursor
When a cursor that has been declared outside of a stored procedure is opened, Adaptive Server generates an optimized query plan and compiles the cursor. It then performs preliminary operations for executing the scan defined in the cursor and returning a result set. For example, when a cursor is opened, Adaptive Server may place a shared intent lock on a table or create a work table.
If a cursor has been declared within a stored procedure, it already exists in compiled form. When the cursor is opened, Adaptive Server only needs to perform preliminary operations for executing a scan and returning a result set.
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. 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 result.
The number of rows returned by a fetch can be specified using the set cursor rows command. 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.
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 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 is ready to re-create the cursor result set. When you perform the fetch, Adaptive Server 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 dumps the query plan from memory and eliminates all trace of the cursor structure. For example:
```
deallocate cursor business_crsr
```
You must declare the cursor again before using it.


	Expand Search