Transact-SQL User's Guide

Home

Transact-SQL User's Guide

Chapter 17 Cursors: Accessing Data Row by Row

Declaring cursors

You must declare a cursor before you can use it. The declaration specifies the query that defines the cursor result set. You can explicitly define a cursor as updatable or read-only by using the for update or for read only keywords. If you omit either one, Adaptive Server determines whether the cursor is updatable based on the type of query that defines the cursor result set. However, Sybase suggests that you explicitly specify one or the other; for updates, this ensures that Adaptive Server performs the positioned updates correctly. You cannot use the update or delete statements on the result set of a read-only cursor.

declare cursor syntax

The syntax of the declare cursor statement is:

declare cursor_name cursor
     for select_statement
     [for {read only | update [of column_name_list]}]

The cursor_name is the name of the cursor. It must be a valid Adaptive Server identifier containing no more than 30 characters, and it must start with a letter, a pound sign (#), or an underscore (_).

The select_statement is the query that defines the cursor result set. See select in the Reference Manual for information about its options. In general, select_statement may use the full syntax and semantics of a Transact-SQL select statement, including the holdlock keyword. However, it cannot contain a compute, for browse, or into clause.

The for read only option specifies that the cursor result set cannot be updated. In contrast, the for update option specifies that the cursor result set is updatable. You can specify of column_name_list after for update with the list of columns from the select_statement defined as updatable.

The declare cursor statement must precede any open statement for that cursor. You cannot combine declare cursor with other statements in the same Transact-SQL batch, except when using a cursor in a stored procedure.

For example, the following declare cursor statement defines a result set for the authors_crsr cursor that contains all authors that do not reside in California:

declare authors_crsr cursor
for select au_id, au_lname, au_fname
from authors
where state != "CA"
for update

The select_statement can contain references to Transact-SQL parameter names or local variables. However, the names can reference only the parameters and local variables defined in a stored procedure that contains the declare cursor statement. If the cursor is used in a trigger, the select_statement can also reference the inserted and deleted temporary tables that are used in triggers. For information on using the select statement, see Chapter 2, "Queries: Selecting Data from a Table."

Types of cursors

There are four types of cursors:

Client cursors - are declared through Open Client calls (or Embedded SQL). Open Client keeps track of the rows returned from Adaptive Server and buffers them for the application. Updates and deletes to the result set of client cursors can be done only through the Open Client calls.
Execute cursors - are a subset of client cursors whose result set is defined by a stored procedure. The stored procedure can use parameters. The values of the parameters are sent through Open Client calls.
Server cursors - are declared in SQL. If they are used in stored procedures, the client executing the stored procedure is not aware of them. Results returned to the client for a fetch are the same as the results from a normal select.
Language cursors - are declared in SQL without using Open Client. As with server cursors, the client is not aware of the cursors, and the results are returned to the client in the same format as a normal select.

Client cursors, through the use of applications using Open Client calls or Embedded-SQL, are the most frequently used form of cursors. To simplify the discussion of cursors, the examples in this manual are for language and server cursors only. For examples of client or execute cursors, see your Open Client or Embedded-SQL documentation.

Cursor scope

A cursor's existence depends on its scope. The scope refers to the context in which the cursor is used: within a user session, a stored procedure, or a trigger.

Within a user session, the cursor exists only until the user ends the session. The cursor does not exist for any additional sessions that other users start. After the user logs off, Adaptive Server deallocates the cursors created in that session.

If a declare cursor statement is part of a stored procedure or trigger, the cursor created within it applies to that scope and to the scope that launched the stored procedure or trigger. However, cursors declared inside a trigger on an inserted or a deleted table are not accessible to any nested stored procedures or triggers. Such cursors are accessible within the scope of that trigger. Once the stored procedure or trigger completes, Adaptive Server deallocates the cursors created within it.

Figure 17-3 illustrates how cursors operate between scopes.

Figure 17-3: How cursors operate within scopes raster

A cursor name must be unique within a given scope. Adaptive Server detects name conflicts within a particular scope only during runtime. A stored procedure or trigger can define two cursors with the same name if only one is executed. For example, the following stored procedure works because only one names_crsr cursor is defined in its scope:

create procedure proc2 @flag int
as
if @flag > 0
    declare names_crsr cursor
    for select au_fname from authors
else
    declare names_crsr cursor
    for select au_lname from authors
return

Cursor scans and the cursor result set

The method Adaptive Server uses to create the cursor result set depends on the type of query used. If the query does not require a worktable, Adaptive Server performs a fetch by cursoring down the base table using the table's index keys, much like a select statement, except that it returns the number of rows specified by the fetch. After the fetch, Adaptive Server positions the cursor at the next valid index key, until you fetch again or close the cursor.

Some queries use worktables to generate the cursor result set. To verify whether a particular cursor uses a worktable, check the output of a set showplan, no exec on statement.

Be aware that when a worktable is used, the rows retrieved with a cursor fetch statement may not reflect the values in the actual base table rows. For example, a cursor declared with an order by clause usually requires the creation of a worktable to order the rows for the cursor result set. Adaptive Server does not lock the rows in the base table that correspond to the rows in the worktable, which permits other clients to update these base table rows. Hence, the rows returned to the client from the cursor statement are different from the base table rows. See "Cursors and locking" for more information on how locks work with cursors.

A cursor result set is generated as the rows are returned through a fetch of that cursor. This means that a cursor select query is processed like a normal select query. This process, known as cursor scans, provides a faster turnaround time and eliminates the need to read rows the application does not require.

Adaptive Server requires that cursor scans use a unique index of a table, particularly for isolation level 0 reads. If the table has an IDENTITY column and you need to create a nonunique index on it, use the identity in nonunique index database option to include an IDENTITY column in the table's index keys so that all indexes created on the table are unique. This database option makes logically nonunique indexes internally unique and allows the indexes to be used to process updatable cursors for isolation level 0 reads.

You can still use cursors that reference tables without indexes, if none of those tables are updated by another process that causes the current row position to move. For example:

declare storinfo_crsr cursor
for select stor_id, stor_name, payterms
    from stores
    where state = "CA"

The table stores, specified with the above cursor, does not have any indexes. Adaptive Server allows the declaration of cursors on tables without unique indexes, as long as you have not specified for update in the declare cursor statement. If an update does not change the position of the row, the cursor position does not change until the next fetch.

Making cursors updatable

You can update or delete a row returned by a cursor if the cursor is updatable. If the cursor is read-only, you can only read the data; you cannot update or delete it. By default, Adaptive Server attempts to determine whether a cursor is updatable before designating it as read-only.

You can explicitly specify whether a cursor is updatable by using the read only or update keywords in the declare statement. Specifying a cursor to be updatable ensures that Adaptive Server performs the positioned updates correctly. Make sure the table being updated has a unique index. If it does not, Adaptive Server rejects the declare cursor statement.

The following example defines an updatable result set for the pubs_crsr cursor:

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

The above example includes all the rows from the publishers table, but it explicitly defines only the city and state columns for update.

Unless you plan to update or delete rows through a cursor, you should declare a cursor as read-only. If you do not explicitly specify read only or update, the cursor is implicitly updatable when the select statement does not contain any of the following constructs:

distinct option
group by clause
Aggregate function
Subquery
union operator
at isolation read uncommitted clause

You cannot specify the for update clause if a cursor's select_statement contains one of the above constructs. Adaptive Server also defines a cursor as read-only if you declare certain types of cursors that include an order by clause as part of their select_statement. See "Types of cursors" for information on the types of cursors Adaptive Server supports.

Determining which columns can be updated

If you do not specify a column_name_list with the for update clause, all the specified columns in the query are updatable. Adaptive Server attempts to use unique indexes for updatable cursors when scanning the base table. For cursors, Adaptive Server considers an index containing an IDENTITY column to be unique, even if it is not so declared.

Adaptive Server allows you to update columns in the column_name_list that are not specified in the list of columns of the cursor's select_statement, but that are part of the tables specified in the select_statement. However, when you specify a column_name_list with for update, you can update only the columns in that list.

In the following example, Adaptive Server uses the unique index on the pub_id column of publishers (even though pub_id is not included in the definition of newpubs_crsr):

declare newpubs_crsr cursor
for select pub_name, city, state
from publishers
for update

If you do not specify the for update clause, Adaptive Server chooses any unique index, although it can also use other indexes or table scans if no unique index exists for the specified table columns. However, when you specify the for update clause, Adaptive Server must use a unique index defined for one or more of the columns to scan the base table. If no unique index exists, Adaptive Server returns an error message.

In most cases, you should include only columns to be updated in the column_name_list of the for update clause. If the table has only one unique index, you do not need to include its column in the for update column_name_list; Adaptive Server finds it during the cursor scan. If the table has more than one unique index, include its column in the for update column_name_list, so that Adaptive Server can find it quickly for the cursor scan. For example, the table used in the following declare cursor statement has one unique index, on the column c3, so that column should not be included in the for update list:

declare mycursor cursor
for select c1, c2, 3
from mytable
for update of c1, c2

However, if mytable has more than one unique index, for example, on columns c3 and c4, you need to specify one unique index in the for update clause as follows:

declare mycursor cursor
for select c1, c2, 3
from mytable
for update of c1, c2, c3

Allowing Adaptive Server to use the unique index in the cursor scan in this manner helps to prevent an update anomaly called the Halloween problem. The Halloween problem occurs when a client updates a column through a cursor, and that column defines the order in which the rows are returned from the base tables (that is, a unique indexed column). For example, if Adaptive Server accesses a base table using an index, and the index key is updated by the client, the updated index row can move within the index and be read again by the cursor. The row seems to appear twice in the result set: when the index key is updated by the client and when the updated index row moves farther down the result set.

Another way to avoid the Halloween problem is to create tables with the unique auto_identity index database option set to on. See the System Administration Guide for more information.


	Expand Search