SQL/MX 2.x Reference Manual (H06.04+)

Embedded-Only SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
3-25
C Examples of DECLARE CURSOR
•
If the READONLY_CURSOR attribute is set to TRUE (the default setting), you
must declare cursors with the FOR UPDATE clause for the named columns or all
columns to be updatable. (This READONLY_CURSOR setting improves cursor
performance.)
•
If the READONLY_CURSOR attribute is set to FALSE and you omit the FOR
READ ONLY clause, all columns except primary key columns are automatically
updatable; that is, you do not need to specify the FOR UPDATE clause.
For more information on the READONLY_CURSOR attribute, see Row Maintenance
on page 10-61.
Order of Cursor Operations
In static SQL, a cursor declaration must compile before other statements that reference
the cursor. In dynamic SQL, a cursor declaration must execute before other statements
that reference the cursor.
Declaring Host Variables
The host variables occurring in the cursor specification must be declared within the
scope of the associated OPEN statement. Otherwise, an error occurs during
preprocessing.
WITH HOLD
You can use holdable cursors only for SELECT statements that use the
Publish/Subscribe stream access mode or an embedded UPDATE or embedded
DELETE.
C Examples of DECLARE CURSOR
•
This SQL statement defines a static read-only cursor:
EXEC SQL DECLARE cursor1 CURSOR FOR
SELECT COL1, COL2, COL3, COL4 FROM SALES.PARTS
WHERE COL2 >= :hostvar2
READ UNCOMMITTED ACCESS
ORDER BY COL2;
•
This SQL statement defines a static updatable cursor. The FOR UPDATE clause
lists the columns to be updated:
EXEC SQL DECLARE cursor1 CURSOR FOR
SELECT COL1, COL2, COL3, COL4 FROM SALES.PARTS
WHERE COL2 >= :hostvar2
READ COMMITTED ACCESS
FOR UPDATE OF COL2, COL3, COL4;
Pub/Sub