SQL Statements › DECLARE CURSOR › Description

Description

cursor-name

1. A cursor with the specified name is defined when your program is executed. The name must not be the same as the name of another cursor declared in your program.

   If you specify SQLMODE=ANSI or SQLMODE=FIPS in the SQL Preprocessor options, the cursor name can be 1 to 18 characters.

   For all other modes, the cursor name can be 1 to 32 characters.

WITH HOLD

When this CA Datacom/DB extension is specified, the cursor stays open when a COMMIT WORK is executed. Any record-at-a-time command that commits the logical unit of work (for example LOGCP, LOGCR) works the same way. This is especially useful in a batch program that does updates and issues COMMIT WORK periodically to keep the log from becoming full and to limit the amount of work RESTART would have to do in case of a failure. The repositioning of the cursor is harder to program without WITH HOLD.

select-statement

For information about the select-statement, see Select-Statement.

statement-name

Specifies the name of a prepared statement. That statement must be prepared (using the PREPARE statement) sometime after the DECLARE CURSOR statement is executed and before the OPEN statement is executed, and it must be a select-statement. For information on the PREPARE statement, see PREPARE.

A cursor in the open state designates a result table and a position relative to the rows of that table. The table is the result table specified by the select-statement of the cursor.

The result table is read-only if the select-statement includes any of the following:

• The keyword DISTINCT
• A UNION operator
• A column function
• A GROUP BY clause
• A HAVING clause
• An ORDER BY clause

The result table is also read-only if the FROM clause of the outer subselect of the select-statement:

• Identifies more than one table or view
• Identifies a read-only view
• Identifies a table or view that is also identified in a FROM clause of a subquery of the select-statement