NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
D-23
Considerations—DECLARE CURSOR
Considerations—DECLARE CURSOR
•
Order of cursor operations
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.
•
SELECT statements for DECLARE 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 as follows: if you use
a cursor to locate rows to delete without specifying the FOR UPDATE OF
clause in the cursor declaration, you can include an ORDER BY clause if you
are sure that SQL will choose a plan that satisfies the specified order without
sorting the rows.
•
Locking considerations
DECLARE CURSOR does not acquire locks. Locks are acquired when you execute
a FETCH on the cursor or—if the SELECT requires a sort—when you open the
cursor. The access option you specify in the SELECT applies to rows you access
with the cursor.
If the cursor deletes rows, you must ensure that your program requests locks for the
deletions by including the FOR UPDATE clause, using a LOCK TABLE statement
preceding the FETCH on the cursor, or using STABLE or REPEATABLE access in
the SELECT. In the latter case, the row usually is held with a shared lock that is
escalated to an exclusive lock for the DELETE. If the SQL cannot obtain the
exclusive lock before timeout occurs, the DELETE operation can fail.