SQL Programming Manual for TAL

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for TAL—527887-001
3-10
CONTROL Directives
Using the CONTROL QUERY Directive
Follow these guidelines when you use the CONTROL QUERY directive:
Optimization. The CONTROL QUERY directive affects the optimization for
subsequent DML statements in listing order (rather than execution order) until:
•
Another CONTROL QUERY directive resets the option.
•
The end of the procedure occurs.
Scope. The scope of a CONTROL QUERY directive is as follows.
•
A directive affects SQL statements in the program's listing order, regardless of the
execution order.
•
A directive coded with the global declarations affects only global cursors.
•
A directive coded in a procedure affects only the DML statements in the procedure.
•
A directive coded in a subprocedure affects all statements that follow in listing
order until the end of the procedure in which the subprocedure is embedded.
Flow-Control Statements. A CONTROL QUERY 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.
This example shows two CONTROL directives with dynamic SQL statements. The
EXECUTE statement is affected by the CONTROL TABLE directive with the TIMEOUT
option but not by the CONTROL QUERY directive. The TACL DEFINE =EMPLOYEE
refers to the employee table.
EXEC SQL PREPARE statement FROM "SELECT * FROM =EMPLOYEE";
EXEC SQL CONTROL QUERY INTERACTIVE ACCESS ON;
EXEC SQL CONTROL TABLE =employee TIMEOUT 120 SECONDS;
EXEC SQL EXECUTE statement;
Dynamic SQL Statements. A static CONTROL QUERY directive has no effect on
dynamic SQL statements. To use a CONTROL QUERY directive with dynamic SQL
statements, specify a dynamic CONTROL directive using the PREPARE and
EXECUTE (or EXECUTE IMMEDIATE) statements. For dynamic SQL cursors, the
CONTROL QUERY directive and the DECLARE CURSOR statement must be in the
same procedure.
Using the CONTROL TABLE Directive
Follow these guidelines when you use the CONTROL TABLE directive:
Access to Tables and Views. The CONTROL TABLE directive affects access to
tables and views referenced by SQL statements until:
•
Another CONTROL TABLE directive resets the options.
•
The end of the procedure occurs.