Dynamic SELECT statement Format 1 (Retrieve dynamically)

Function

The dynamic SELECT statement is used for the following purposes:

Privileges

See 2.1.1 Cursor specification: Format 1.

Format

(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 }
  |derived-query-expression UNION ALL {query-specification|(derived-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

For the following items, see the indicated sections in this manual:

FOR UPDATE [OF column-name [, column-name]...] is referred to as a FOR UPDATE clause
FOR UPDATE
This operand is specified when a table is being searched using a cursor and when rows in the table are updated or deleted using the cursor, and, in addition, when rows in the table are to be updated, added, or deleted either using another cursor or by directly specifying a search condition.
When the FOR UPDATE clause is omitted, the current cursor cannot be used to update, add, or delete rows from the table being retrieved.
The FOR UPDATE clause cannot be specified if either the cursor specification or the lock option contains any of the following specifications:
  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 an outermost query specification
  4. SELECT DISTINCT in an outermost query specification
  5. A table derived from a FROM clause in the outermost query specification
  6. The GROUP BY clause in an outermost query specification
  7. The HAVING clause in an outermost query specification
  8. A set function in an outermost query specification
  9. The window function in an outermost query specification
  10. Any of the following view tables in the FROM clause of an outermost query specification:
    [Figure]A view table defined by specifying any of items (1) to (9) above in a view definition statement.
    [Figure]View tables that are 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 defined by specifying the READ ONLY option in the view definition statement.
  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
OF column-name [, column-name]...
If a table is being searched using a cursor and when only searched rows using the cursor are to be updated, this operand specifies the column to be updated.
Columns that are not specified in the selection expression of the SELECT statement can also be specified in column-name. Each column specified in column-name must be distinct.
If a table is being searched using a cursor and rows in the table are not updated or deleted using that cursor or another cursor, and rows on which a cursor is not used are not updated, deleted, or added, this operand should not be specified.
The column name that is specified should specify a column in the table specified in the FROM clause of the outermost query specification, rather than the column name specified in AS column-name.
[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
This operand is specified when performing an update by specifying another cursor or a direct search condition during a retrieval using a cursor. Specify FOR READ ONLY so that any update performed during the retrieval process does not affect the results of retrieval.
Specifying the FOR READ ONLY clause is subject to the following restrictions:
(a) Scalar operations, function calls, and component specifications that produce any of the following data types in the results cannot be specified in a selection expression:
  • BLOB
  • BINARY with a maximum length of 32,001 bytes or greater
  • BOOLEAN
  • Abstract data type
(b) Only column specifications can be specified as an output BLOB value with a WRITE specification.
(c) The GET_JAVA_STORED_ROUTINE_SOURCE specification cannot be specified.

Specifies that a holdable cursor is to be used. 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 for a named derived table derived by specifying a function call using a plug-in
  2. Definition SQL statements cannot be executed while a holdable cursor is open. If the holdable cursor is closed, execution of a definition SQL statement invalidates any preprocessing that is using the holdable cursor.
  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.

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. Because specification of the FOR UPDATE clause causes a work table to be created, this operand should be omitted if no rows are to be updated, added, or deleted from the current table using a cursor.
  2. 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.

Examples

For examples, see DECLARE CURSOR Format 1 (Declare cursor) and ALLOCATE CURSOR statement Format 1 (Allocate a statement cursor).