SQL Programming Manual for Pascal

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for Pascal—528614-001
3-10
Dynamic SQL Cursors
Dynamic SQL Cursors
For dynamic SQL cursors, all statements referencing the cursor must be in the
procedure where the cursor is defined. However, if you open the cursor and use it in
one call to the procedure where it is defined, you can still use the cursor in subsequent
calls without opening the cursor again.
All Cursors
At run time, all FETCH statements that specify the cursor must execute before the
CLOSE statement.
Cursor Declaration Examples
This cursor selects data from the PARTS table by part number (PARTNUM) and by part
name (PARTDESC).
EXEC SQL DECLARE LIST_BY_PARTDESC_CURSOR CURSOR FOR
SELECT PARTNUM,
PARTDESC,
PRICE,
QTY_AVAILABLE
FROM =PARTS
WHERE (PARTDESC>= :PARTS.PARTDESC)
ORDER BY PARTDESC, PARTNUM
BROWSE ACCESS ;
This cursor positions for a list next operation on the PARTS table by part name
(PARTDESC) and by part number (PARTNUM) within the part name. A DEFINE is in
effect for the PARTS table.
EXEC SQL DECLARE LIST_NEXT_BY_PARTDESC_CURSOR CURSOR FOR
SELECT PARTNUM,
PARTDESC,
PRICE,
QTY_AVAILABLE
FROM =PARTS
WHERE (PARTDESC, PARTNUM) >
(:PARTS.PARTDESC , :PARTS.PARTNUM)
ORDER BY PARTDESC, PARTNUM
BROWSE ACCESS ;
This cursor updates the PARTS table by part name (PARTDESC). The definition must
specify either STABLE ACCESS or REPEATABLE ACCESS. The FOR UPDATE
clause lists the columns that can be updated. PARTNUM is the primary key and cannot
be updated; it can only be deleted.
EXEC SQL DECLARE LIST_BY_PARTDESC_CURSOR CURSOR FOR
SELECT PARTNUM,
PARTDESC,
PRICE,
QTY_AVAILABLE
FROM =PARTS