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 a DECLARE CURSOR cannot include an INTO clause.
A SELECT in a DECLARE CURSOR can optionally include either an ORDER BY clause or a FOR 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.

Advance To:

Opening a Cursor