SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
D-25
Examples—DECLARE CURSOR
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.

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.
Examples—DECLARE CURSOR

This static SQL statement defines a cursor for a read:
EXEC SQL DECLARE CURSOR1 CURSOR FOR
SELECT COL1, COL2, COL3, COL4 FROM =PARTS
WHERE COL2 >= :HOSTVAR2 ORDER BY COL2 BROWSE ACCESS;

This static SQL statement defines a cursor for an update. The FOR UPDATE
clause lists the columns to be updated.
EXEC SQL DECLARE CURSOR1 CURSOR FOR
SELECT COL1, COL2, COL3, COL4 FROM =PARTS
WHERE (COL2 = :HOSTVAR2) STABLE ACCESS
FOR UPDATE OF COL2, COL3, COL4;

This dynamic SQL example defines a cursor for a SELECT stored in a C host
variable. (The SELECT is not shown.)
Variable declarations:
EXEC SQL BEGIN DECLARE SECTION;
intext char[50];
...
EXEC SQL END DECLARE SECTION;
Executable code:
EXEC SQL PREPARE SELECTIT FROM :intext;
...
EXEC SQL DECLARE GETPARTS CURSOR FOR SELECTIT;