SQL Programming Manual for TAL

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for TAL—527887-001
3-29
OPEN
Using the PREFIX and SUFFIX Clauses
This example specifies both a prefix and a suffix for indicator variables. The indicator
variables generated are X^ZNUM^2 and X^ZCHAR^2.
The CREATE TABLE statement that defines table TAB is:
CREATE TABLE tab
( znum NUMERIC (6),
zchar CHARACTER(16),
PRIMARY KEY znum
)
CATALOG \sys.$vol.testcat ;
The INVOKE directive with the PREFIX and SUFFIX clauses is:
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL INVOKE tab PREFIX x^ SUFFIX ^2;
EXEC SQL END DECLARE SECTION;
NonStop SQL generates this structure template:
! Record Definition for table \SYS.$DB.XPERSNL.TAB
! Definition current at 15:32:39 - 09/22/89
STRUCT tab^type (*);
BEGIN
INT x^znum^2; ! indicator variable name
INT(32) znum;
INT x^zchar^2; ! indicator variable name
STRING zchar[0:15];
END;
OPEN
The OPEN statement opens a cursor.
At run time, the OPEN statement for a cursor must execute before all FETCH
statements for the cursor. Any host variables used in a DECLARE CURSOR statement
must be in the same scope for each OPEN statement for the cursor.
Determining the Scope of a Cursor After an OPEN 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.
For a dynamic SQL cursor, all statements referring to the cursor must appear in the
procedure where the cursor is declared; however, if you open the cursor and use it in