2.1.2 Cursor specification: Format 2
(1) Function
This format is used when a table is to be retrieved by means of a list.
A cursor specification for a table retrieved by means of a list is specified in a dynamic SELECT statement (for details about the dynamic SELECT statement, see Dynamic SELECT statement Format 1 (Retrieve dynamically) in 4. Data Manipulation SQL).
(2) Privileges
A user with the SELECT privilege for a base table can execute cursor specification: format 2 to retrieve that base table by means of a list.
(3) Format
SELECT {{value-expression | WRITE specification | GET_JAVA_STORED_ROUTINE_SOURCE specification}
[[AS] column-name]
[,{value-expression | WRITE specification|GET_JAVA_STORED_ROUTINE_SOURCE specification}
[[AS] column-name]]...
|*}
FROM LIST list-name
[ORDER BY {column-name | sort-item-specification-number} [{ASC | DESC}]
[,{column-name | 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
- value-expression | WRITE specification |GET_JAVA_STORED_ROUTINE_SOURCE specification} [[AS] column-name]
[,{value-expression | WRITE specification |GET_JAVA_STORED_ROUTINE_SOURCE specification} [[AS] column-name]]...
|*}
- value-expression
- Specifies the value expression to be retrieved.
- The following rules apply to the value expressions in the SELECT clause:
- In column-name, specify the column name of the base table of the list to be retrieved.
- In a repetition column, specify a repetition column in the base table of the list to be retrieved.
- If a repetition column is specified singly, the ANY subscript cannot be specified.
- In an attribute name, specify the attribute of the abstract data type for the base table of the list to be retrieved.
- The following items cannot be specified in a value expression in the SELECT clause:
- Unqualified table specifications in a column specification for a base table
- External references to (a column in) a base table
- Set functions
- Window function
- Subqueries
- Some receive functions for passing inter-function values can be specified without specifying a receive function for passing inter-function values for a plug-in provided function. For details about receive functions for passing inter-function values that can be specified, see the HiRDB Version 8 UAP Development Guide and various plug-in manuals.
- Only one receive function for passing inter-function values for a plug-in provided function can be specified.
- WRITE specification
- Specifies a WRITE specification to enable BLOB data retrieval results to be output to a file (for details about the WRITE specification, see 2.22 WRITE specification).
- GET_JAVA_STORED_ROUTINE_SOURCE specification
- Specify when a Java class source file is to be extracted from the JAR file (for details, see 2.23 GET_JAVA_STORED_ROUTINE_SOURCE specification).
- [AS] column-name
- Specifies a name to be assigned to value-expression, WRITE specification, or GET_JAVA_STORED_ROUTINE_SOURCE specification.
- * Specifies that all columns in the base table are to constitute the list that is to be retrieved.
Specifies the name of the list storing the set to be retrieved.
- ORDER BY {column-name | sort-item-specification-number} {ASC | DESC}
For rules on the ORDER BY clause, see Format 1.
- LIMIT {[offset-of-first-row-to-return,] {limit-row-count | ALL} | {limit-row-count | ALL} [OFFSET offset-of-first-row-to-return]}
For rules on the LIMIT clause, see Format 1.
(5) Common rules
- If a row that was found when a list was created is not found during retrieval processing, the system returns SQL code +110 and continues the retrieval. However, when one of the following conditions is applicable, the system does not return SQLCODE + 110:
- A column name in the base table for the list other than an argument for a receive function for passing inter-function values is specified, and a * is not specified for a column in the base table for the list.
- An ORDER BY clause is specified.
- If the LIMIT clause specifies an offset, the row that returns SQLCODE + 110 is included in the number of rows to be skipped.
- If the LIMIT clause specifies a limit row count, the row that returns SQLCODE + 110 is included in the number of rows to be acquired.
- A user cannot manipulate lists by connecting to multiple copies of HiRDB at the same time.
(6) Notes
- A row in the base table that is deleted after a list is created is not retrieved. However, if only the following items are specified in a selection expression, the system retrieves the data in the row in the base table before the row is deleted:
- A value expression that does not include the column name of the table
- A receive function for passing inter-function values
- If a row in the base table is updated after a list is created, the updated data will be retrieved. Note that a receive function for passing inter-function values, specified in a selection expression, retrieves un-updated data from a row in the base table.
- If a row in the base table is deleted and then inserted after a list is created, the inserted row may be retrieved.
- From the time the SQL that retrieves a table by means of a list is processed by a PREPARE statement to the time the OPEN statement is executed, you must not execute an ASSIGN LIST statement that creates a list with the same list name.
- When you specify a WRITE specification, you can specify in the first argument the name of the base table's BLOB type column or the BLOB type attribute name of the abstract data type.
(7) Specification example: Specifying a cursor specification in a cursor declaration
DECLARE CRL1 CURSOR FOR
SELECT PCODE,PNAME
FROM LIST1