Nonstop Database, HiRDB Version 9 UAP Development Guide
Once you declare or allocate a cursor and open it with an OPEN statement, you can extract data and perform other operations such as referencing and updating. However, after the cursor is opened, whether or not operations that do not use a cursor can be performed depends on the specification of the FOR READ ONLY or FOR UPDATE clause and whether or not an operation that uses a cursor (updating or deletion) is performed. The FOR READ ONLY and FOR UPDATE clauses are specified in the cursor declaration and in the SELECT statement identified by either the SQL statement identifier specified in the cursor declaration or the extended statement name specified in the cursor allocation.
The table below shows the relationships between cursor updatability and operations that do not use a cursor. When the SQL optimization option for suppressing creation of update-SQL work tables is specified, the restrictions on operations that do not use a cursor are relaxed.
Table 3-25 Relationships between cursor updatability and operations that do not use a cursor
Condition | Operation that does not use a cursor | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
Specification of cursor updatability | Process that uses a cursor | SQL optimization option for suppressing creation of update-SQL work tables not applied | SQL optimization option for suppressing creation of update-SQL work tables applied and index key value no-lock facility used | ||||||||
UD | Del | Ret | UD | Del | Add | Ret | UD | Del | Add | ||
Static SQL | FOR READ ONLY clause specified | NP | NP | Y | Y | Y | Y | Y | Y | Y | Y |
FOR UPDATE OF column name specified | NP | NP | Y | CU | N | N | Y | Y#2 | Y#2 | Y#2 | |
NP | P | Y | Y | Y | Y | Y | Y | Y | Y | ||
P | NP | Y | CU | N | N | Y | Y#2 | Y#2 | Y#2 | ||
P | P | Y | Y | Y | Y | Y | Y | Y | Y | ||
FOR UPDATE clause specified | NP | NP | Y | Y | Y | Y | Y | Y | Y | Y | |
NP | P | Y | Y | Y | Y | Y | Y | Y | Y | ||
P | NP | Y | Y | Y | Y | Y | Y | Y | Y | ||
P | P | Y | Y | Y | Y | Y | Y | Y | Y | ||
None of the above#1 | NP | NP | Y | N | N | N | Y | Y#2 | Y#2 | Y#2 | |
NP | P | Y | Y | Y | Y | Y | Y | Y | Y | ||
P | NP | Y | Y | Y | Y | Y | Y | Y | Y | ||
P | P | Y | Y | Y | Y | Y | Y | Y | Y | ||
Dynamic SQL | FOR READ ONLY clause specified | NP | NP | Y | Y | Y | Y | Y | Y | Y | Y |
FOR UPDATE OF column name specified | NP | NP | Y | CU | N | N | Y | Y#2 | Y#2 | Y#2 | |
NP | P | Y | Y | Y | Y | Y | Y | Y | Y | ||
P | NP | Y | CU | N | N | Y | Y#2 | Y#2 | Y#2 | ||
P | P | Y | Y | Y | Y | Y | Y | Y | Y | ||
FOR UPDATE clause specified | NP | NP | Y | Y | Y | Y | Y | Y | Y | Y | |
NP | P | Y | Y | Y | Y | Y | Y | Y | Y | ||
P | NP | Y | Y | Y | Y | Y | Y | Y | Y | ||
P | P | Y | Y | Y | Y | Y | Y | Y | Y | ||
None of the above | NP | NP | Y | N | N | N | Y | Y#2 | Y#2 | Y#2 | |
NP | P | Y | Y | Y | Y | Y | Y | Y | Y | ||
P | NP | Y | Y | Y | Y | Y | Y | Y | Y | ||
P | P | Y | Y | Y | Y | Y | Y | Y | Y |
CREATE INDEX X1 ON T1(C1); DECLARE CR1 CURSOR FOR SELECT C1 FROM T1 WHERE C1>0;
FETCH CR1 INTO :XX; UPDATE T1 SET C1=10;
To use multiple cursors for updating the same table simultaneously, you must specify all columns to be updated in the FOR UPDATE clause of the individual cursor declarations or dynamic SELECT statements. For example, to use cursor 1 to update column 1 and cursor 2 to update column 2, specify both column 1 and column 2 in the FOR UPDATE clause when you declare cursor 1 and cursor 2. If only column 1 is specified for cursor 1 or only column 2 is specified for cursor 2, an error occurs during updating.
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.