SQL Programming Manual for TAL

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for TAL—527887-001
3-11
DECLARE CURSOR
Scope. The scope of a CONTROL TABLE directive is as follows.
•
A directive affects SQL statements in the program's listing order, regardless of the
execution order.
•
A directive with the global declarations affects only statements at the global level
(statements that are outside of procedures).
•
A directive in a procedure affects only the statements in the procedure.
•
A directive in a subprocedure affects all statements that follow in listing order until
the end of the procedure in which the subprocedure is embedded.
•
To affect tables or views referenced in a cursor, the CONTROL TABLE directive
must precede the DECLARE CURSOR statement. For static SQL cursors, the
CONTROL TABLE directive and the DECLARE CURSOR statement must be with
the global declarations. For dynamic SQL cursors, the statements must be in the
same procedure.
Flow-Control Statements. A CONTROL TABLE directive coded within flow-control
statements (for example, IF, THEN, and ELSE) applies to static SQL statements in the
program's listing order, regardless of the execution order.
Dynamic SQL Statements. A static CONTROL TABLE directive does not affect
dynamic SQL statements. To use a CONTROL TABLE directive with dynamic SQL
statements, specify a dynamic CONTROL TABLE directive using the PREPARE and
EXECUTE (or EXECUTE IMMEDIATE) statements.
A dynamic CONTROL TABLE directive does not affect static SQL statements, unless
you specify the TIMEOUT option. In this case, the CONTROL TABLE directive affects
all static and dynamic SQL statements that follow in execution order (rather than listing
order), until another CONTROL TABLE directive resets the option or the end of the
procedure occurs.
DECLARE CURSOR
The DECLARE CURSOR statement defines a cursor and associates the cursor with a
SELECT statement. Using the cursor, TAL program can process, one by one, the rows
retrieved by the SELECT statement.
Follow these guidelines when you use the DECLARE CURSOR statement:
Static SQL Cursors
A static DECLARE CURSOR statement can appear only in the global declarations part
of a TAL compilation unit.
All host variables referenced by the DECLARE CURSOR statement in the static SQL
program must have global scope and must be declared in listing order before the
DECLARE CURSOR statement that references them.