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.

FOR UPDATE
In the case of a table that is being searched using the cursor, specifies that a row on which the cursor is used can only be updated or deleted, and a row on which the cursor is not used can be updated, deleted, or added.
If a module contains the UPDATE statement for updating rows using the specified cursor or contains the DELETE statement for deleting rows and the FOR UPDATE OF clause is omitted, then FOR UPDATE is assumed as the default, thus enabling updating, adding, or deleting of any column.
This operand should be omitted for a table being retrieved using the cursor that has no rows to be updated or deleted using that cursor or any other cursors, and no rows to be updated, deleted, or added without using a cursor. If a lock option in the SQL statement is omitted, the lock option is determined by a specified value in PDISLLVL or a specified value for the data guarantee level specified in SQL-compile-option. However, if YES is indicated for PDFORUPDATEEXLOCK or if FOR UPDATE EXCLUSIVE is specified after the data guarantee level in SQL-compile-option, the lock option for the cursor with that specification is assumed to be WITH EXCLUSIVE LOCK. For details, see the HiRDB Version 9 UAP Development Guide.
OF column-name [, column-name]
Specifies the columns to be updated in a table being searched using the cursor when only the rows retrieved with that cursor are to be updated.
This operand can also specify columns that are not specified in a selection expression of the SELECT statement. A column can be specified only once in a SELECT statement.
If a table being searched using the cursor has no rows to be updated or deleted using that cursor or any other cursors and no rows to be updated, deleted, or added without using a cursor, this operand should be omitted.
When a column name is specified, instead of using the column name that was specified in AS column-name, specify the column of the table that was specified in the FROM clause for the outermost query specification.
[NOWAIT]
Produces the same behavior as if the FOR UPDATE clause was specified and WITH EXCLUSIVE LOCK NO WAIT was specified as the lock option. However, if the lock option is specified, NOWAIT cannot be specified. For details about operations when WITH EXCLUSIVE LOCK NO WAIT is specified as the lock option, see 2.19 Lock option.
FOR READ ONLY
In the case of a table being searched using the cursor, specifies that the rows are to be updated either using another cursor or by specifying a direct search condition. The purpose of this specification is to ensure that the update operation performed during searching is not affected by the retrieval results.

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:

  1. A holdable cursor cannot be used in the following cases:
    • When a column of the abstract data type using a plug-in is specified
    • When a function call using a plug-in is specified
    • A query with respect to a named derived table that was derived by specifying a function call using a plug-in
  2. Definition SQL statements cannot be executed while a holdable cursor is open.
  3. If, after an OPEN statement is executed for a SELECT statement using a holdable cursor, a PURGE TABLE statement is executed for a table used in the SELECT statement, the cursor is placed into closed status.
  4. If, after an OPEN statement is executed for a SELECT statement using a holdable cursor and before a DISCONNECT is performed, another user issues a definition SQL statement for a table used in the SELECT statement, the definition SQL statement is placed into lock-wait status. Similarly, if, during the period when preprocessing relative to a SELECT statement using a holdable cursor is still in effect, another user issues a definition SQL statement for a table that is being used in the SELECT statement, the definition SQL statement is placed into lock-wait status.

Common rules

  1. The declared cursor remains closed.
  2. The value (the value of an embedded variable, an SQL variable, or an SQL parameter value that is specified in the SELECT statement of DECLARE CURSOR) that is in effect when the OPEN statement for the cursor is executed remains in effect from the time the cursor is opened until it is closed. To modify such values, the cursor must be closed and then reopened.
  3. A maximum of 1,023 cursors can be declared per UAP.
  4. If either the cursor specification or the cursor lock option contains any of the following specifications, updating and deletion using that cursor cannot be performed and the FOR UPDATE clause cannot be specified:
    1. UNION [ALL] or EXCEPT [ALL]
    2. A table specified in the FROM clause of the outermost query specification in the FROM clause of a subquery.
    3. Joined tables in the outermost query specification.
    4. A derived table in a FROM clause in the outermost query specification
    5. SELECT DISTINCT in the outermost query specification
    6. A GROUP BY clause in an outermost query specification
    7. A HAVING clause in an outermost query specification
    8. A set function on the outermost query specification
    9. The window function on the outermost query specification
    10. Any of the following view tables in the FROM clause of the outermost query specification:
    [Figure]A view table defined by specifying (1) to (9) above in CREATE VIEW
    [Figure] A view table defined by specifying a value expression other than a column specification in the SELECT clause of the outermost query specification in a view definition statement
    [Figure]A view table for which READ ONLY is specified in CREATE VIEW
    11. WITHOUT LOCK NOWAIT
    12. A query specification name specified in the FROM clause of the outermost query specification in the query expression body in which a WITH clause is specified
  5. The FOR READ ONLY clause cannot be specified for a cursor if rows are to be updated or deleted using that cursor.
  6. When a FOR READ ONLY clause is specified, the following restrictions apply:
    1. Scalar operations, function calls, and component specifications that produce results in any of the following data types cannot be specified in a selection expression:
    [Figure]BLOB
    [Figure] BINARY with a maximum length of 32,001 bytes or greater
    [Figure]BOOLEAN
    [Figure] Abstract data type
    2. Only a column specification can be specified for an output BLOB value with a WRITE specification in a selection expression.
    3. A GET_JAVA_STORED_ROUTINE_SOURCE specification cannot be specified.

Rule related to referential constraints

  1. A holdable cursor that is used to retrieve a table in which a foreign key is defined is closed when the table being retrieved goes into check pending status.

Notes

  1. Similar to an embedded variable, a cursor name is effective within a compile-unit module. Multiple SQLs relative to the same cursor cannot be used in different modules.
  2. A cursor declaration must be coded before any SQL statement that references the cursor name used in the declaration.
  3. Because DECLARE CURSOR is not an executable statement, no return code is returned to SQLCODE (and return code testing should not be performed).
  4. By applying the work table creation suppression feature of the update SQL statement in the SQL optimization option and using the index key-value no-lock facility, you can update, add, or delete rows while using a cursor for which neither FOR UPDATE nor FOR UPDATE OF is specified.
  5. Specifying FOR READ ONLY may cause HiRDB to create a work table. In this case, the FOR READ ONLY processing may be subject to restrictions depending on the row length of the work table. For details about work table row lengths, see the HiRDB Version 9 Installation and Design Guide.

Examples

  1. Declare cursor CR1 to fetch rows, one row at a time, from stock table STOCK:

    DECLARE CR1 CURSOR FOR
     SELECT PCODE,PNAME,COLOR,PRICE,SQTY
       FROM STOCK

  2. Declare cursor CR1 for stock table STOCK to fetch rows, one row at a time, in which the unit price (PRICE) is $50 or greater:

    DECLARE CR1 CURSOR FOR
     SELECT * FROM STOCK
       WHERE PRICE = 50

  3. Use cursor CR1 to retrieve all rows from stock table STOCK, apply a 10% discount to the unit price (PRICE), and then insert rows:

    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

  4. Use cursor CR1 to retrieve all rows from stock table STOCK, and apply a 10% discount to the unit price (PRICE):

    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

  5. Use cursor CR1 to retrieve all rows from stock table STOCK, and delete, without using the cursor, rows whose product name (PNAME) is sweater:

    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