SQL Programming Manual for TAL

Dynamic NonStop SQL Operations
HP NonStop SQL Programming Manual for TAL—527887-001
7-31
Using Dynamic Cursors
Using Dynamic Cursors
Dynamic SQL statements use cursors to process SELECT statements in the same way
static SQL statements use cursors. The program reads rows from a table, one by one,
and sends the column values to output data buffers specified in the program. This
subsection provides some guidelines when you use cursors. The order for executing
statements to use a cursor with dynamic SQL operations is:
Follow these guidelines when you declare and use a cursor:
•
You can use a host variable wherever you can use the cursor-name and
statement-name parameters. For each new statement and cursor, you store the
name in the host variable before executing the statements.
•
The DECLARE CURSOR, PREPARE, OPEN, FETCH, CLOSE, DELETE WHERE
CURRENT, UPDATE WHERE CURRENT, DESCRIBE INPUT, and DESCRIBE
statements for a particular cursor and its associated statement must all appear in
the same procedure.
Operation Description
PREPARE statement-name
FROM :host-variable
Dynamically compiles the SELECT statement
defining the cursor
Issue the DESCRIBE INPUT and
DESCRIBE statements
DECLARE cursor-name CURSOR
FOR statement-name
Declares the cursor
OPEN cursor-name
USING DESCRIPTOR input-sqlda
Opens the cursor and gets parameter values
from input data buffer in the program
Loop until end-of-file
FETCH cursor-name
USING DESCRIPTOR output-sqlda
Retrieves data and outputs column values to
output data buffer in the program
CLOSE cursor-name Closes the cursor
Figure 7-5. Displaying Output (page 3 of 3)
-- Proceed to handle all the possible data types for
-- output values and write the data pointed to by
-- the VAR^PTR field in the output SQLDA in a format
-- depending on the data type. For complete code, see
-- the detailed sample program
END; --end CASE
--Call WRITE to print the contents of the output buffer
END; -- end FOR loop, traversing sqlvar array
END;
VST0705.vsd