SQL Programming Manual for TAL

Dynamic NonStop SQL Operations
HP NonStop SQL Programming Manual for TAL—527887-001
7-32
Using Dynamic Cursors
•
The PREPARE statement does not have to precede the other statements in the
program listing order; however, the PREPARE statement must be executed after
DECLARE CURSOR and before DESCRIBE, DESCRIBE INPUT, OPEN, FETCH,
and CLOSE.
Using Cursors with a USING DESCRIPTOR Clause
If the program is handling input parameters with values entered at run time, you use
the USING DESCRIPTOR clause with the OPEN statement to specify values for the
parameters in the SELECT statement. The input SQLDA specifies the address of
program data buffers that contain the input parameter values.
You also use the USING DESCRIPTOR clause with the FETCH statement to write
column values to an output buffer specified in the program’s variable declarations. The
output SQLDA specifies the address of program data buffers into which FETCH copies
the data.
Using Cursors with an UPDATE WHERE CURRENT Clause
To use UPDATE WHERE CURRENT with a static cursor, you specify a FOR UPDATE
OF clause with a column list in the DECLARE CURSOR statement. In contrast, to use
UPDATE WHERE CURRENT with a dynamic SQL cursor, you must specify a FOR
UPDATE OF clause in the SELECT statement associated with the cursor.
This example shows an UPDATE WHERE CURRENT operation with a dynamic SQL
cursor. In the example, the host variable HOSTVAR contains the SELECT statement to
define the cursor. The host variable :SALVAR receives the selected values.
--Copy the string:
--"SELECT salary FROM =employee FOR UPDATE OF salary"
--into host variable hostvar
...
EXEC SQL
PREPARE s1 FROM :hostvar;
EXEC SQL
DECLARE c1 CURSOR FOR s1;
EXEC SQL
OPEN c1;
EXEC SQL
FETCH c1 INTO :salvar;
EXEC SQL
UPDATE =employee SET salary = salary * 1.20
WHERE CURRENT OF c1;