DECLARE CURSOR
Go Up to Statement and Function Reference (Language Reference Guide)
Defines a cursor for a table by associating a name with the set of rows specified in a SELECT statement. Available in gpre and DSQL.
SQL form:
DECLARE cursor CURSOR FOR select [FOR UPDATE OF col [, col…]];
DSQL form:
DECLARE cursor CURSOR FOR statement_id
Blob form: See DECLARE CURSOR (BLOB).
| Argument | Description |
|---|---|
|
<cursor> |
Name for the cursor. |
|
<select> |
Determines which rows to retrieve. SQL only. |
|
|
Enables |
|
<statement_id> |
|
Description: DECLARE CURSOR defines the set of rows that can be retrieved using the cursor it names. It is the first member of a group of table cursor statements that must be used in sequence.
select specifies a SELECT statement that determines which rows to retrieve. The SELECT statement cannot include INTO or ORDER BY clauses.
The FOR UPDATE OF clause is necessary for updating or deleting rows using the WHERE CURRENT OF clause with UPDATE and DELETE.
A cursor is a one-way pointer into the ordered set of rows retrieved by the select expression in the DECLARE CURSOR statement. It enables sequential access to retrieved rows in turn. There are four related cursor statements:
| Stage | Statement | Purpose |
|---|---|---|
|
1 |
|
Declares the cursor; the |
|
2 |
|
Retrieves the rows specified for retrieval with |
|
3 |
|
Retrieves the current row from the active set, starting with the first row; subsequent |
|
4 |
|
Closes the cursor and releases the system resources. |
Examples: The following embedded SQL statement declares a cursor with a search condition:
EXEC SQL
DECLARE C CURSOR FOR
SELECT CUST_NO, ORDER_STATUS
FROM SALES
WHERE ORDER_STATUS IN ('open', 'shipping');
The next DSQL statement declares a cursor for a previously-prepared statement, QUERY1:
DECLARE Q CURSOR FOR QUERY1