2.1.1 Cursor specification: Format 1

Organization of this subsection
(1) Function
(2) Privileges
(3) Format
(4) Operands
(5) Notes
(6) Specification examples

(1) Function

A cursor specification enables the user to retrieve data from one or more tables or to sort the results of a retrieval.

A cursor specification is made in a cursor declaration or in the dynamic SELECT statement.

For details about the SELECT statement, see Dynamic SELECT statement Format 1 (Retrieve dynamically) in Chapter 4.

(2) Privileges

Users who can use all query specifications that are included in a cursor specification or who can use subqueries are authorized to use a cursor specification.

See 2.3 Query specification for query specification privileges and 2.4 Subqueries for subquery privileges.

(3) Format

Format 1: Searching one or more tables

query-expression
  [ORDER BY {column-specification
             |sort-item-specification-number}
  [{ASC | DESC}]
  [,{column-specification | sort-item-specification-number}
  [{ASC | DESC}]]... ]
  [LIMIT {[offset-of-first-row-to-return,] {limit-row-count | ALL}
         | {limit-row-count | ALL} [OFFSET offset-of-first-row-to-return]}]

(4) Operands

A query expression specifies either a query specification or the union set or the disjunction set between derived tables that are obtained as a result of a query specification.

For details of query expressions, see 2.2 Query expressions.

Specifies the sort method to be used when the retrieval results produced by a query expression are to be sorted in ascending or descending order.

If the ORDER BY clause is omitted, the rows in the derived table may appear in random order.

The following rules apply to the ORDER BY clause:

  1. A maximum of 255 columns can be specified in a sort key.
  2. A column can be specified only once.
  3. If an AS column name is specified in the outermost query selection expression and a table derived from the query expression contains a column with the same name, that column name cannot be specified in a sort key.
  4. None of the following can be specified as a sort key:
    • Repetition columns and value expressions that contain a repetition column
    • BLOB
    • BINARY with a minimum definition length of 32,001 bytes
    • BOOLEAN
    • Abstract data type
    • WRITE specification
    • GET_JAVA_STORED_ROUTINE_SOURCE specification
  5. When the ORDER BY clause is specified, an SQL variable, SQL parameter, or literal of any of the following data types cannot be specified by itself in a selection expression:
    • BLOB
    • BINARY with a minimum definition length of 32,001 bytes
    • Abstract data type

Specifies a column to be used as a sort key.

The following rules apply to column specifications:

  1. If the query expression contains either UNION [ALL] or EXCEPT [ALL], a column cannot be specified. However, if the table derived from the query expression contains no column with the same name, that column name can be specified.
  2. If SELECT DISTINCT is specified in the outermost query, the column specified in the ORDER BY clause must be an item that is output as a retrieval result (specified in the selection expression).
  3. If the outermost query specification does not specify SELECT DISTINCT but instead specifies either a GROUP BY clause or a set function, the column specified in the ORDER BY clause must be a GROUPing column. In other cases, any column in the table specified in the outermost query specification can be specified.

When UNION [ALL] or EXCEPT [ALL] is specified, specifies the ordinal positional number in the derived table of a column to be used as a sort key. A column in the derived table obtained as a result of the query expression must be specified.

When UNION [ALL] or EXCEPT [ALL] is not specified and a column in the derived table obtained as a result of a set function, windows function, literal, arithmetic operation, date operation, scalar operation, CASE expression, CAST specification, function invocation, component specification, or concatenation operation is used as a sort key, this variable specifies the position in the derived table of the column that is to be used as the sort key.

A sort item specification number cannot be specified in the ORDER BY clause of a query expression in which ROW is specified in the selection expression.

ASC
Specifies that the retrieval results are to be sorted in ascending order.
DESC
Specifies that the retrieval results are to be sorted in descending order.

Of search results in a query expression, specify the number of rows to be skipped from the beginning and the number of rows to be acquired. The LIMIT clause can improve SQL search performance. For a description of under what conditions the LIMIT clause should be specified, see the section on the facility for acquiring n rows of search results from the beginning in the HiRDB Version 8 UAP Development Guide.

offset-of-first-row-to-return
In offset-of-first-row-to-return, specifies the number of rows to be skipped from the beginning of the results in a search expression.
limit-row-count
If an offset is not specified, specifies the number of rows to be acquired from the results of the query expression.
If an offset is specified, specifies the number of rows to be acquired by skipping the offset from the beginning of the results of the query expression.
ALL
If an offset is not specified, acquires all the results from the query expression.
If an offset is specified, acquires all the results by skipping the offset from the beginning of the results of the query expression.

The LIMIT clause is subject to the following rules:

  1. In offset-of-first-row-to-return and limit-row-count, you can specify an integer literal, an embedded variable, a ? parameter, an SQL variable, and an SQL parameter.
  2. Set the data type for offset-of-first-row-to-return and limit-row-count as an integer (either SMALLINT or INTEGER).
  3. The null value cannot be specified in either offset-of-first-row-to-return or limit-row-count.
  4. If either a ? parameter or an embedded variable is specified in offset-of-first-row-to-return or limit-row-count, the ? parameter or the embedded variable is assumed to have the INTEGER (without an indicator variable) data type.
  5. The range of values that can be specified in offset-of-first-row-to-return is 0 to 2,147,483,647.
  6. Specifying 0 in offset-of-first-row-to-return has the same effect as when nothing is specified in offset-of-first-row-to-return.
  7. The range of values that can be specified in limit-row-count is -1 to 2,147,483,647.
  8. If the value -1 is specified in limit-row-count, it should be specified as a literal.
  9. The maximum total value that can be specified in offset-of-first-row-to-return and limit-row-count is 2,147,483,647.
  10. If limit-row-count[Figure] 0, the search result row count will be:
    Max (Min (query-expression-row-count - offset-of-first-row-to-return, limit-row-count), 0)
  11. If either limit-row-count = -1 or ALL is specified, the search result row count will be:
    Max (query-expression-row-count - offset-of-first-row-to-return, 0)
  12. If an offset is specified, the retrieval result is not uniquely identified unless there are multiple rows containing the same sort key value as the last row of the rows that are skipped, or unless a sort key is specified.
  13. If the result of the query expression is greater than limit-row-count, the retrieval result is not uniquely identified unless there are multiple rows containing the same sort key as the last row of limit-row-count or a sort key is specified. The following example shows a case in which retrieval results are not uniquely identified.
    Example:
    If a table (STOCK) is retrieved using the cursor declared in the following SQL statement, multiple rows have the third smallest value, 3640, in the sort key column (PRICE). Therefore, whether the retrieval result is 1 or 2 is indefinite:

    DECLARE CR1 CURSOR FOR
     SELECT * FROM STOCK ORDER BY PRICE LIMIT 3

    [Figure]

(5) Notes

  1. Specifying ORDER BY can cause HiRDB to create a work table. In this case, the processing of ORDER BY can be restricted depending on the row length of the work table. For details about work table row lengths, see the HiRDB Version 8 Installation and Design Guide.

(6) Specification examples

  1. Provide a cursor specification in a cursor declaration:

     DECLARE CR1 CURSOR FOR
       SELECT PNAME,SUM(SQUANTITY)
         FROM STOCK
         GROUP BY PNAME
         ORDER BY PNAME ASC

  2. Specify a LIMIT clause in a cursor specification in a cursor declaration:

     DECLARE CR1 CURSOR FOR
       SELECT PCODE,SQUANTITY
         FROM STOCK
         WHERE SQUANTITY>20
         ORDER BY 2,1
         LIMIT 10