HP NonStop SQL/MP, Руководство по программированию

Страница: 219 / 331

Dynamic SQL Operations
HP NonStop SQL/MP Programming Manual for C—429847-008
10-21
Using Dynamic SQL Cursors
Using cursors with a USING DESCRIPTOR Clause
If the program is handling parameters entered at run time, use the USING
DESCRIPTOR clause with the OPEN statement to provide the parameter values to
SQL from an input location in the program’s variable declarations. The input SQLDA
describes the input location for each parameter. The DESCRIBE INPUT statement fills
in the SQLDA SQLVAR entries, and your program sets the
var_ptr
fields and
prompts the user for values for the parameters.
Use the USING DESCRIPTOR clause with the FETCH statement to write column
values to an output location specified in the program’s variable declarations. The
output SQLDA describes a list of memory locations into which FETCH copies the data.
Using cursors with an UPDATE WHERE CURRENT Clause
To use UPDATE WHERE CURRENT with a static cursor, 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 that defines the cursor.
This example uses 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.
strncpy (hostvar,
"SELECT salary FROM =employee FOR UPDATE OF salary", 49);
EXEC SQL
PREPARE stmt1 FROM :hostvar;
EXEC SQL
DECLARE c1 CURSOR FOR stmt1;
EXEC SQL
OPEN c1;
EXEC SQL
FETCH c1 INTO :salvar;
EXEC SQL
UPDATE =employee SET salary = salary * 1.20
WHERE CURRENT OF c1;
Using Statement and Cursor Host Variables
The DESCRIBE statement returns descriptions of output variables from previously
prepared dynamic SQL statements. You can use statement and cursor host variables
with the DECLARE CURSOR, PREPARE, OPEN, FETCH, and CLOSE statements.
For each new statement and cursor name, store the name in the host variable before
executing the statement. Thus, you code the statements only once.