DECLARE CURSOR Format 1 (Declare cursor)
Function
DECLARE CURSOR declares a cursor to be used by the FETCH statement to fetch on a row-by-row basis the results of a retrieval by a query specification.
In Format 1, this SQL statement declares a cursor for a direct cursor specification.
Privileges
None.
Format 1: Declaring a cursor relative to a direct cursor specification
DECLARE cursor-name CURSOR [WITH HOLD][ {WITH RETURN | WITHOUT RETURN} ] FOR
(Cursor-Specification-Format-1)
(Query-Expression)
(Query-Specification)
{SELECT [{ALL|DISTINCT}]{selection-expression
[,selection-expression]...|*}
(Table-Expression)
FROM table-reference [, table-reference]...
[WHERE search-condition]
[GROUP BY value-expression [, value-expression]...]
[HAVING search-condition]
| query-expression }
[ORDER BY {column-specification|sort-item-specification-number}
[{ASC|DESC}]
[, {column-specification|sort-item-specification-number}
[{ASC|DESC}]...]]
[LIMIT { [offset, ] {row_count | ALL}
| {row_count | ALL} [OFFSET offset] } ]
(Lock-Option)
[[(WITH {SHARE|EXCLUSIVE} LOCK
|WITHOUT LOCK [{WAIT|NOWAIT}]}]
[{WITH ROLLBACK|NO WAIT}]]
[FOR {UPDATE [OF column-name [, column-name]...] [NOWAIT]|READ ONLY}]
[UNTIL DISCONNECT]
Operands
Specifies the name of the desired cursor.
When a cursor name is specified in a UAP, it must not be enclosed in double quotation marks, even when the cursor name is the same as an SQL reserved word. However, when a cursor name that is the same as an SQL reserved name is specified in a procedure, it must be enclosed in double quotation marks.
For details about cursor names, see 1.1.7 Specification of names.
Specifies that a holdable cursor is to be used.
Because WITH HOLD provides the same function as specifying UNTIL DISCONNECT, see the section on UNTIL DISCONNECT for an explanation. The result is the same whether WITH HOLD or UNTIL DISCONNECT is specified.
Specifies the cursor that expresses the contents of a query.
See 2.1.1 Cursor specification: Format 1 for cursor specifications, 2.2 Query expressions for query expressions, 2.3 Query specification for query specifications, 2.5 Table expressions for table expressions, and 2.7 Search conditions for search conditions.
Specifies the lock mode for specifying queries, and the action to be taken by the system when the necessary resources for performing a query are being used exclusively by another user.
See 2.19 Lock option for details about the lock option.
The FOR UPDATE [OF column-name [, column-name]] clause is called the FOR UPDATE clause.
Specifies that a holdable cursor is to be used.
The function provided by this specification is exactly the same as specifying WITH HOLD. The result is the same whether WITH HOLD or UNTIL DISCONNECT is specified.
For details about holdable cursors, see the HiRDB Version 9 UAP Development Guide.
The following rules apply to holdable cursors:
Common rules
Rule related to referential constraints
Notes
Examples
DECLARE CR1 CURSOR FOR
SELECT PCODE,PNAME,COLOR,PRICE,SQTY
FROM STOCK
DECLARE CR1 CURSOR FOR
SELECT * FROM STOCK
WHERE PRICE = 50
DECLARE CR1 CURSOR FOR
SELECT * FROM STOCK
FOR UPDATE
OPEN CR1
FETCH CR1 INTO <Name of variable into which column is fetched>
UPDATE STOCK
SET PRICE = <Value of unit price after 10% discount>
WHERE CURRENT OF CR1
INSERT INTO STOCK VALUES (<Insertion values for columns>)
CLOSE CR1
DECLARE CR1 CURSOR FOR
SELECT * FROM STOCK
FOR UPDATE OF PRICE
OPEN CR1
FETCH CR1 INTO <Name of variable into which column is fetched>
UPDATE STOCK
SET PRICE = <Value of unit price after 10% discount>
WHERE CURRENT OF CR1
CLOSE CR1
DECLARE CR1 CURSOR FOR
SELECT * FROM STOCK
FOR READ ONLY
OPEN CR1
FETCH CR1 INTO <Name of variable into which column is fetched>
DELETE FROM STOCK
WHERE PNAME=N'sweater'
CLOSE CR1