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;

Topics

• Updating Through Cursors