Declaring a Cursor
Go Up to Selecting Multiple Rows
To declare a cursor and specify rows of data to retrieve, use the DECLARE CURSOR
statement. DECLARE CURSOR
is a descriptive, non-executable statement. InterBase uses the information in the statement to prepare system resources for the cursor when it is opened, but does not actually perform the query. Because DECLARE CURSOR
is non-executable, SQLCODE
is not assigned when this statement is used.
The syntax for DECLARE CURSOR
is:
DECLARE cursorname CURSOR FOR SELECT <col> [, <col> ...] FROM table [, <table> ...] WHERE <search_condition> [GROUP BY col [, col ...]] [HAVING <search_condition>] [ORDER BY col [ASC | DESC] [, col ...] [ASC | DESC] | FOR UPDATE OF col [, col ...]];
<cursorname> is used in subsequent OPEN
, FETCH
, and CLOSE
statements to identify the active cursor.
With the following exceptions, the SELECT
statement inside a DECLARE CURSOR
is similar to a stand-alone SELECT
:
- A
SELECT
in aDECLARE CURSOR
cannot include anINTO
clause. - A
SELECT
in aDECLARE CURSOR
can optionally include either anORDER BY
clause or aFOR UPDATE
clause.
For example, the following statement declares a cursor:
EXEC SQL DECLARE TO_BE_HIRED CURSOR FOR SELECT D.DEPARTMENT, D.LOCATION, P.DEPARTMENT FROM DEPARTMENT D, DEPARTMENT P WHERE D.MNGR_NO IS NULL AND D.HEAD_DEPT = P.DEPT_NO;
Updating Through Cursors
In many applications, data retrieval and update may be interdependent. DECLARE CURSOR
supports an optional FOR UPDATE
clause that optionally lists columns in retrieved rows that can be modified. For example, the following statement declares such a cursor:
EXEC SQL DECLARE H CURSOR FOR SELECT CUST_NO FROM CUSTOMER WHERE ON_HOLD = '*' FOR UPDATE OF ON_HOLD;
If a column list after FOR UPDATE
is omitted, all columns retrieved for each row may be updated. For example, the following query enables updating for two columns:
EXEC SQL DECLARE H CURSOR FOR SELECT CUST_NAME CUST_NO FROM CUSTOMER WHERE ON_HOLD = '*';
For more information about updating columns through a cursor, see Updating Multiple Rows.