SQL/MP Programming Manual for COBOL85

Data Retrieval and Modification
HP NonStop SQL/MP Programming Manual for COBOL85—429326-004
4-15
DECLARE CURSOR Statement
•
A stand-alone UPDATE or DELETE statement on the same table (directly or
through a view) by the same process
•
An UPDATE...WHERE CURRENT or DELETE...WHERE CURRENT statement
using a different cursor to access the same table (directly or through a view) by the
same process
For example, a loop containing both a FETCH statement and a stand-alone UPDATE
or DELETE statement on the same table invalidates the cursor’s buffer on every loop
iteration. You can minimize or eliminate this problem by following these guidelines:
•
Do not use INSERT statements within a cursor operation.
•
Use the UPDATE...WHERE CURRENT or DELETE...WHERE CURRENT
statement for a cursor rather than a stand-alone UPDATE or DELETE statement.
•
Do not open multiple cursors on a table if any of the cursors are used to update
that table.
DECLARE CURSOR Statement
The DECLARE CURSOR statement names and defines a cursor and associates the
cursor with a SELECT statement that specifies the rows to retrieve. A COBOL program
requires no special authorization to execute a DECLARE CURSOR statement.
Follow these guidelines when you use a DECLARE CURSOR statement:
•
The cursor name specified in the DECLARE CURSOR statement is an SQL
identifier and is not case-sensitive. For example, SQL/MP considers Cur, cur, CUR,
and CuR as equivalent names.
•
Declare all host variables you use in the associated SELECT statement before the
DECLARE CURSOR statement. Host variables must also be within the same
scope as all SQL statements that refer to them.
•
Place the DECLARE CURSOR statement in listing order before the other SQL
statements, including the OPEN, FETCH, INSERT, DELETE, UPDATE, and
CLOSE statements, that refer to the cursor. The DECLARE CURSOR statement
must also be within the scope of the statements that refer to the cursor.
•
The DECLARE CURSOR statement does not affect the values in the SQLCA and
SQLSA data structures.