SQL Programming Manual for TAL

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for TAL—527887-001
3-18
EXECUTE and EXECUTE IMMEDIATE
EXECUTE and EXECUTE IMMEDIATE
The EXECUTE statement executes a previously prepared dynamic SQL statement.
The EXECUTE IMMEDIATE statement compiles and executes an SQL statement that
is represented as text in a host variable.
Use an EXECUTE statement for any DDL, DML, or DCL statement except the
SELECT statement. Use an EXECUTE IMMEDIATE statement for any DDL, DML, or
DCL statement except the OPEN, CLOSE, and SELECT statements. Use a cursor to
process a SELECT statement.
The EXECUTE statement must appear in the same procedure as the PREPARE,
DESCRIBE INPUT, and DESCRIBE statements that are used to process the dynamic
SQL input statement.
FETCH
The FETCH statement positions a cursor at the next row of the result table defined by
the cursor and retrieves the column values. This statement returns each column value
in the row into the corresponding host variable.
To execute a FETCH statement, a program's process accessor ID (PAID) must have
read access to any tables or views associated with the cursor.
At run time, the OPEN statement must execute before all FETCH statements, and the
CLOSE statement must execute after all FETCH statements for the cursor. Any host
variable used in a DECLARE CURSOR statement must be in the same scope for each
OPEN or FETCH statement for the cursor.
This table shows the SQLCODE values that NonStop SQL returns after a FETCH
statement:
Determining the Scope of a Cursor for a FETCH Statement
A static SQL cursor can only be used in the procedures in the compilation unit where
the cursor is declared. Thus, for static SQL operations, the DECLARE CURSOR,
OPEN, FETCH, DELETE WHERE CURRENT, UPDATE WHERE CURRENT, and
CLOSE statements that refer to the cursor must be in the same compilation unit.
If the program exits a procedure with an open cursor, procedures that execute later can
still refer to the cursor, provided the procedures are in the same compilation unit.
SQLCODE Value Description
0 The FETCH statement was successful.
100 The end of a table was encountered.
< 0 An error occurred; SQLCODE contains the error number.
> 0 (not 100) A warning occurred; SQLCODE contains the first warning number.