![DECLARE CURSOR statement [T-SQL]](gif/forward.gif)
Reference Manual
CHAPTER 9. SQL Statements
To declare a cursor. Cursors are the primary means for manipulating the results of queries.
DECLARE cursor-name
... [ UNIQUE
| SCROLL
| NO SCROLL
| DYNAMIC SCROLL
| INSENSITIVE ]
... CURSOR FOR statement | CURSOR FOR statement-name
... [ FOR UPDATE | FOR READ ONLY ]
cursor-name: identifier
statement-name: identifier or host-variable
None.
None.
The DECLARE CURSOR statement declares a cursor with the specified name for a SELECT statement or a CALL statement.
When a cursor is declared UNIQUE, the query is forced to return all the columns required to uniquely identify each row. Often this will mean ensuring that all of the columns in the primary key or a uniqueness table constraint are returned. Any columns that are required but were not specified will be added.
A DESCRIBE done on a UNIQUE cursor sets the following additional flags in the indicator variables:
DT_KEY_COLUMN The column is part of the key for the row
DT_HIDDEN_COLUMN The column was added to the query, because it was required to uniquely identify the rows
A cursor declared FOR READ ONLY may not be used in an UPDATE (positioned) or a DELETE (positioned) operation. FOR UPDATE is the default.
A cursor declared NO SCROLL is restricted to FETCH NEXT and FETCH RELATIVE 0 seek operations. A cursor declared SCROLL or DYNAMIC SCROLL can use all formats of the FETCH statement. DYNAMIC SCROLL is the default.
SCROLL cursors behave differently from DYNAMIC SCROLL cursors when a row in the cursor is modified or deleted after the first time the row is read. SCROLL cursors have more predictable behavior when changes happen.
Each row fetched in a SCROLL cursor is remembered. If one of these rows is deleted, either by your program or by another program in a multiuser environment, it creates a "hole" in the cursor. If you fetch the row at this "hole" with a SCROLL cursor, Adaptive Server Anywhere returns the error SQLE_NO_CURRENT_ROW indicating that the row has been deleted, and leaves the cursor positioned on the "hole". (A DYNAMIC SCROLL cursor will just skip the "hole" and retrieve the next row.)
This allows your application to remember row positions within a cursor and be assured that these positions will not change. For example, an application could remember that Cobb is the second row in the cursor for SELECT * FROM employee. If the first employee (Whitney) is deleted while the SCROLL cursor is still open, FETCH ABSOLUTE 2 will still position on Cobb while FETCH ABSOLUTE 1 will return SQLE_NO_CURRENT_ROW. Similarly, if the cursor is on Cobb, FETCH PREVIOUS will return SQLE_NO_CURRENT_ROW.
In addition, a fetch on a SCROLL cursor will return the warning SQLE_ROW_UPDATED_WARNING if the row has changed since it was last read. (The warning only happens once; fetching the same row a third time will not produce the warning.) Similarly, an UPDATE (positioned) or DELETE (positioned) statement on a row that has been modified since it was last fetched will return the error SQLE_ROW_UPDATED_SINCE_READ and abort the statement. An application must FETCH the row again before the UPDATE or DELETE will be permitted.
Note that an update to any column will cause the warning/error, even if the column is not referenced by the cursor. For example, a cursor on Surname and Initials would report the update even if only the Birthdate column were modified. These update warning and error conditions will not occur in bulk operations mode (-b database server statement line switch) when row locking is disabled. See Tuning bulk loading of data.
Adaptive Server Anywhere maintains more information about SCROLL cursors than DYNAMIC SCROLL cursors; thus, DYNAMIC SCROLL cursors are more efficient and should be used unless the consistent behavior of SCROLL cursors is required. There is no extra overhead in Adaptive Server Anywhere for DYNAMIC SCROLL cursors versus NO SCROLL cursors.
A cursor declared INSENSITIVE has its membership fixed when it is opened; a temporary table is created with a copy of all the original rows. FETCHING from an INSENSITIVE cursor does not see the effect of any other INSERT, UPDATE, or DELETE statement, or any other PUT, UPDATE WHERE CURRENT, DELETE WHERE CURRENT operations on a different cursor. It does see the effect of PUT, UPDATE WHERE CURRENT, DELETE WHERE CURRENT operations on the same cursor.
INSENSITIVE cursors make it easier to write an application that deals with cursors, since you only have to worry about changes you make explicitly to the cursor; you do not have to worry about actions taken by other users or by other parts of your application.
INSENSITIVE cursors can be expensive if the cursor is on a lot of rows. Also, INSENSITIVE cursors are not affected by ROLLBACK or ROLLBACK TO SAVEPOINT; the ROLLBACK is not an operation on the cursor that changes the cursor contents.
INSENSITIVE cursors meet the ODBC requirements for static cursors.
Statements are named using the PREPARE statement. Cursors can be declared only for a prepared SELECT or CALL.
The DECLARE CURSOR statement does not generate any C code.
Cursor-name is a string supplied by the programmer.
SQL/92 Entry level feature.
Sybase Supported by Open Client/Open Server.
The following example illustrates how to declare a scroll cursor in Embedded SQL:
EXEC SQL DECLARE cur_employee SCROLL CURSOR FOR SELECT * FROM employee ;
The following example illustrates how to declare a cursor for a prepared statement in Embedded SQL:
EXEC SQL PREPARE employee_statement FROM 'SELECT emp_lname FROM employee' ; EXEC SQL DECLARE cur_employee CURSOR FOR employee_statement ;
The following example illustrates the use of cursors in a stored procedure:
BEGIN
DECLARE cur_employee CURSOR FOR
SELECT emp_lname
FROM employee ;
DECLARE name CHAR(40) ;
OPEN cur_employee;
LOOP
FETCH NEXT cur_employee INTO name ;
...
END LOOP
CLOSE cur_employee;
END