User's Guide Part VI. SQL Anywhere Reference Chapter 43. Watcom-SQL StatementsTo declare a cursor. Cursors are the primary means for retrieving data from the database using Embedded SQL.
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
Embedded SQL, procedures, triggers, and batches.
The statement-name and host-variable formats are for Embedded SQL only.
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:
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 the rows in the cursor are 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, SQL 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 engine statement line switch) when row locking is disabled. See "Tuning bulk operations".
SQL 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 SQL Anywhere for DYNAMIC SCROLL cursors versus NO SCROLL cursors.
| Scroll cursors The behavior of SCROLL cursors has changed since Watcom SQL 3.0. The SCROLL cursors in Watcom SQL 3.0 were equivalent to DYNAMIC SCROLL cursors now. Cursors declared with the default scrolling behavior will not have changed since the old default behavior was the SCROLL and the new default behavior is DYNAMIC SCROLL. |
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 and is supplied by the programmer.
1. EXEC SQL DECLARE cur_employee SCROLL CURSOR FORSELECT * FROM employee ;
2. EXEC SQL PREPARE employee_statementFROM 'SELECT emp_lname FROM employee' ;EXEC SQL DECLARE cur_employee CURSOR FOR employee_statement ;
BEGINDECLARE cur_employee CURSOR FORSELECT emp_lnameFROM employee ;DECLARE name CHAR(40) ;OPEN cur_employee;LOOPFETCH NEXT cur_employee into name ;...END LOOPCLOSE cur_employee;END