NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
D-22
DECLARE CURSOR Statement
DECLARE CURSOR Statement
DECLARE CURSOR is a DML statement used in host programs to define a cursor and
associate the cursor with a SELECT statement. The program uses the cursor to fetch
rows retrieved by the SELECT statement one-by-one.
cursor CURSOR FOR select-stmt
specifies the cursor and the SELECT statement to associate.
cursor is an SQL identifier that is the name of the cursor and that is unique among
cursor names in the program.
In static SQL, select-stmt is the SELECT statement itself, optionally enclosed
in quotation marks. In dynamic SQL, select-stmt is the name of the prepared
SELECT as defined in the PREPARE statement.
FOR UPDATE OF col [ , col ]
(static SQL only) specifies that rows selected by the cursor can be updated or
deleted and identifies columns to be updated. (The columns to be updated do not
need to be columns in the select list of the SELECT.)
You must use this clause if you update rows (if stmt includes UPDATE WHERE
CURRENT OF), but it is optional if you delete rows (if stmt includes DELETE
WHERE CURRENT OF). You cannot repeat or qualify column names, or specify
SYSKEY or a user-defined primary key for a key-sequenced table.
:cursor-var CURSOR FOR :select-stmt-var
(dynamic SQL only) specifies host variables that contain the names of the cursor
and the SELECT statement to associate.
If you use this clause, the DECLARE CURSOR statement must be in executable
code (not variable declarations) and must be executed before your program
references the cursor. In this case (and in no other), SQL returns information to the
SQLCA and SQLSA when the DECLARE CURSOR executes.
:cursor-var is a host variable that contains the name of the cursor. The cursor
name must be unique among cursor names in the program. cursor-var must be a
fixed or variable-length string in the host language.
:select-stmt-var is a host variable that contains the name of a SELECT
statement or the name of a host variable that is defined in a PREPARE statement in
the current program. select-stmt-var must be a fixed or variable-length string
in the host language.
{ cursor CURSOR FOR select-stmt }
{ [ FOR UPDATE OF col [ , col ] ... ] }
DECLARE { }
{ :cursor-var CURSOR FOR :select-stmt-var }