SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
D-24
Considerations—DECLARE CURSOR
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.
Considerations—DECLARE CURSOR

In static SQL, a cursor declaration must compile before other statements that
reference the cursor. In dynamic SQL, a cursor declaration must execute before
other statements that reference the cursor.

A SELECT statement in a DECLARE CURSOR statement cannot include an INTO
clause.
If a SELECT includes an ORDER BY clause, the ORDER BY sort specification can
contain a column name or integer followed by the order-designating keyword ASC
or DESC. The integer designates a position in the select list, starting from 1. The
column name is not required in the sort specification.
You can also use expressions in the select list.
If a SELECT in a DECLARE CURSOR updates or deletes rows:

The FROM clause can include only one table or protection view and cannot
include a JOIN operator.

The table referred to in the SELECT must not appear in any subquery in the
WHERE clause.

The SELECT cannot include aggregate functions, the keyword DISTINCT, a
shorthand view, a union operator, or a GROUP BY, HAVING, ORDER BY, or
BROWSE access clause. The only exception to this rule is: if you use a cursor
to locate rows to delete without specifying the FOR UPDATE OF clause in the