|
Use this statement to declare a cursor. Cursors are the primary means for manipulating the results of queries.
DECLARE cursor-name
[ UNIQUE ]
[ NO SCROLL
| DYNAMIC SCROLL
| SCROLL
| INSENSITIVE
| SENSITIVE
]
CURSOR FOR
{ select-statement
| statement-name [ FOR { UPDATE [ cursor-concurrency ] | READ ONLY } ]
| call-statement }
DECLARE cursor-name
[ NO SCROLL
| DYNAMIC SCROLL
| SCROLL
| INSENSITIVE
| SENSITIVE
]
CURSOR
{ FOR select-statement [ FOR { UPDATE [ cursor-concurrency ] | READ ONLY } ]
| FOR call-statement
| USING variable-name }
cursor-name : identifier
statement-name : identifier | hostvar
variable-name : identifier
cursor-concurrency :
BY { VALUES | TIMESTAMP | LOCK }
UNIQUE When a cursor is declared UNIQUE, the query is forced to return all the columns required to uniquely identify each row. Often this means ensuring that all columns in the primary key or a uniqueness table constraint are returned. Any columns that are required but were not specified in the query are added to the result set.
A DESCRIBE done on a UNIQUE cursor sets the following additional options 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
NO SCROLL A cursor declared NO SCROLL is restricted to moving forward through the result set using FETCH NEXT and FETCH RELATIVE 0 seek operations.
As rows cannot be returned to once the cursor leaves the row, there are no sensitivity restrictions on the cursor. Consequently, when a NO SCROLL cursor is requested, SQL Anywhere supplies the most efficient kind of cursor, which is an asensitive cursor. See Asensitive cursors.
DYNAMIC SCROLL DYNAMIC SCROLL is the default cursor type. DYNAMIC SCROLL cursors can use all formats of the FETCH statement.
When a DYNAMIC SCROLL cursor is requested, SQL Anywhere supplies an asensitive cursor. When using cursors there is always a trade-off between efficiency and consistency. Asensitive cursors provide efficient performance at the expense of consistency. See Asensitive cursors.
SCROLL A cursor declared SCROLL can use all formats of the FETCH statement. When a SCROLL cursor is requested, SQL Anywhere supplies a value-sensitive cursor. See Value-sensitive cursors.
SQL Anywhere must execute value-sensitive cursors in such a way that result set membership is guaranteed. DYNAMIC SCROLL cursors are more efficient and should be used unless the consistent behavior of SCROLL cursors is required.
INSENSITIVE 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. See Insensitive cursors.
SENSITIVE A cursor declared SENSITIVE is sensitive to changes to membership or values of the result set. See Sensitive cursors.
FOR statement-name Statements are named using the PREPARE statement. Cursors can be declared only for a prepared SELECT or CALL.
FOR UPDATE | READ ONLY A cursor declared FOR READ ONLY cannot be used in an UPDATE (positioned) or a DELETE (positioned) operation. FOR UPDATE is the default.
Cursors default to FOR UPDATE for single-table queries without an ORDER BY clause, or if the ansi_update_constraints option is set to Off. When the ansi_update_constraints option is set to Cursors or Strict, then cursors over a query containing an ORDER BY clause default to READ ONLY. However, you can explicitly mark cursors as updatable using the FOR UPDATE clause. Because it is expensive to allow updates over cursors with an ORDER BY clause or a join, cursors over a query containing a join of two or more tables are READ ONLY and cannot be made updatable.
In response to any request for a cursor that specifies FOR UPDATE, SQL Anywhere provides either a value-sensitive cursor or a sensitive cursor. Insensitive and asensitive cursors are not updatable.
USING variable-name For use within stored procedures only. The variable is a string containing a SELECT statement for the cursor. The variable must be available when the DECLARE is processed, and so must be one of the following:
A parameter to the procedure. For example,
CREATE FUNCTION GetRowCount( IN qry LONG VARCHAR)
RETURNS INT
BEGIN
DECLARE crsr CURSOR USING qry;
DECLARE rowcnt INT;
SET rowcnt = 0;
OPEN crsr;
lp: LOOP
fetch crsr;
IF SQLCODE <> 0 THEN LEAVE lp END IF;
SET rowcnt = rowcnt + 1;
END LOOP;
RETURN rowcnt;
END;
Nested inside another BEGIN... END after the variable has been assigned a value. For example,
CREATE PROCEDURE get_table_name(
IN id_value INT, OUT tabname CHAR(128)
)
BEGIN
DECLARE qry LONG VARCHAR;
SET qry = 'SELECT table_name FROM SYS.SYSTAB ' ||
'WHERE table_id=' || string(id_value);
BEGIN
DECLARE crsr CURSOR USING qry;
OPEN crsr;
FETCH crsr INTO tabname;
CLOSE crsr;
END
END;
BY VALUES | TIMESTAMP | LOCK In embedded SQL, a concurrency specification can be set by including syntax within the SELECT statement itself, or in the cursor declaration. Pessimistic or optimistic concurrency can be chosen at the cursor level either through options with DECLARE CURSOR or FOR statements, or though the concurrency setting API in specific programming interfaces. If a statement is updatable and the cursor does not specify a particular concurrency control mechanism, the statement's specification is used. The syntax is as follows:
FOR UPDATE BY LOCK The database server acquires intent row locks on FETCHed rows of the result set. These are long-term locks that are held until transaction COMMIT or ROLLBACK.
FOR UPDATE BY { VALUES | TIMESTAMP } The database server utilizes a keyset-driven cursor to enable the application to be informed when rows have been modified or deleted as the result set is scrolled.
The DECLARE CURSOR statement declares a cursor with the specified name for a SELECT statement or a CALL statement. In a Watcom-SQL procedure, trigger, or batch, a DECLARE CURSOR statement must appear with other declarations, immediately following the BEGIN keyword. Also, cursor names must be unique.
If a cursor is declared inside a compound statement, it exists only for the duration of that compound statement (whether it is declared in a Watcom-SQL or Transact-SQL compound statement).
None.
None.
SQL/2003 Core feature.
The following example illustrates how to declare a scroll cursor in embedded SQL:
EXEC SQL DECLARE cur_employee SCROLL CURSOR FOR SELECT * FROM Employees;
The following example illustrates how to declare a cursor for a prepared statement in embedded SQL:
EXEC SQL PREPARE employee_statement FROM 'SELECT Surname FROM Employees'; 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 Surname
FROM Employees;
DECLARE name CHAR(40);
OPEN cur_employee;
lp: LOOP
FETCH NEXT cur_employee INTO name;
IF SQLCODE <> 0 THEN LEAVE lp END IF;
...
END LOOP;
CLOSE cur_employee;
END