Release 10.1A: OpenEdge Data Management:
SQL Reference

> ## DECLARE CURSOR

> Associates a cursor with a static query or a prepared dynamic query statement. The query or the prepared statement can have references to host variables.

> #### Syntax

>     DECLARE cursor_name CURSOR FOR
>       { query_expression [ ORDER BY clause ] [ FOR UPDATE clause ]
>          | prepared_statement_name
>       } ;

> `cursor_name`

> A name you assign to the cursor. The name must meet the requirements for an identifier.

> `query_expression [ ORDER BY clause ] [FOR UPDATE clause ]`

> A complete query expression.

> `prepared_statement_name`

> The name assigned to a prepared SQL statement in an earlier `PREPARE` statement.

> Examples

>     EXEC SQL WHENEVER SQLERROR GOTO selerr ;
>     EXEC SQL DECLARE stcur CURSOR FOR
>          SELECT InvTransNum, Qty, OrderNum FROM PUB.InventoryTrans ;
>     EXEC SQL OPEN stcur ;
>     EXEC SQL WHENEVER NOT FOUND GOTO seldone ;

>     EXEC SQL WHENEVER SQLERROR GOTO selerr ;
>     EXEC SQL PREPARE stmtid from :sel_stmt_v ;
>     EXEC SQL DECLARE dyncur CURSOR FOR stmtid ;
>     EXEC SQL OPEN dyncur ;
>     EXEC SQL WHENEVER NOT FOUND GOTO seldone ;

> #### Notes

> - You must declare a cursor before any `OPEN`, `FETCH`, or `CLOSE` statement.

> - The scope of the cursor declaration is the entire source file in which it is declared. The operations on the cursor, such as `OPEN`, `CLOSE`, and `FETCH` statements, can occur only within the same compilation unit as the cursor declaration.

> - The use of a cursor allows the execution of the positioned forms of the `UPDATE` and `DELETE` statements.

> - If the `DECLARE` statement corresponds to a static SQL statement with parameter references:

> - The `DECLARE` statement must be executed before each execution of an `OPEN` statement for the same cursor.
> - The `DECLARE` statement and the `OPEN` statement that follows must occur within the same transaction within the same task.
> - If the statement contains parameter references to automatic variables or function arguments, the `DECLARE` statement and the following `OPEN` statement for the same cursor must occur within the same C function.

> ##### Authorization

> None

> ##### Related statements

> PREPARE, OPEN, FETCH, CLOSE SELECT

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095