SQL Programming Manual for TAL

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for TAL—527887-001
3-12
DECLARE CURSOR
A static SQL cursor can be accessed only from the compilation unit in which its
DECLARE CURSOR statement occurs. This means that for static SQL programs, the
DECLARE CURSOR, OPEN, FETCH, DELETE WHERE CURRENT, UPDATE
WHERE CURRENT, and CLOSE statements must be in the same compilation unit. If
the program exits a procedure with an open cursor, procedures that execute later and
are in the same compilation unit can still reference the cursor.
Dynamic SQL Cursors
For dynamic SQL cursors, all statements referencing the cursor must appear 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, the OPEN statement must execute before all FETCH statements. Any
FETCH statements must execute before the CLOSE statement executes.
In this example, the DECLARE CURSOR statement uses a cursor that initiates a
SELECT operation from the PARTS table by the part name (PARTDESC) and by part
number (PARTNUM) within the part name. The TACL DEFINE =PARTS refers to the
parts table.
! Host variable declaration. INVOKE produces a
! structure template named parts^type, which is
! then used to declare a structure named parts^rec:
EXEC SQL INVOKE =parts;
STRUCT .parts^rec(parts^type);
! DECLARE CURSOR statement:
EXEC SQL DECLARE order_by_partdesc_cursor CURSOR FOR
SELECT partnum,
partdesc,
price,
qty_available
FROM =parts
WHERE (partdesc, partnum) >
( :parts^rec.partdesc, :parts^rec.partnum )
ORDER BY partdesc, partnum
BROWSE ACCESS ;
! Procedure code:
EXEC SQL OPEN order_by_partdesc_cursor;
WHILE SQLCODE >= 100 DO
! FETCH rows into host variables created with INVOKE
! until NOT FOUND condition is met:
BEGIN
EXEC SQL FETCH order_by_partdesc_cursor INTO
:parts^rec.partnum,