Query Specifications, Subqueries, Query Expressions, and Cursor Specifications › Expansion of Cursor-specification

Expansion of Cursor-specification

The expanded parameters of cursor-specification represent the body of a cursor definition.

Syntax

Expansion of cursor-specification

►►── query-expression ──┬────────────────────────────┬────────────────────────►
                        └── order-by-specification ──┘

 ►──────────────────────┬───────────────────────────────────────────────┬─────►◄
                        └── FOR ─┬─ READ ONLY ──────────────────────────┤
                                 └─ UPDATE ─┬───────────────────────────┤
                                            │       ┌────── , ───────┐  │
                                            └── OF ─▼── column-name ─┴──┘

Expansion of order-by-specification

               ┌─────────────────────── , ───────────────────────────┐
►►── ORDER BY ─▼─┬─┬───────────────┬─ column-name ─┬───┬──────────┬──┴────────►◄
                 │ ├─ table-name. ─┤               │   ├─ ASC ◄───┤
                 │ └─ alias. ──────┘               │   └─ DESC ───┘
                 ├─ column-number ─────────────────┤
                 ├─ result-name ───────────────────┤
                 └─ rowid-pseudo-column ───────────┘

Parameters

Expansion of cursor-specification

query-expression

Represents a table resulting from the evaluation of a query-expression. For expanded syntax, see Expansion of Query-expression.

order-by-specification

Specifies a sort order for the rows in the result table defined by query-expression. Expanded syntax for order-by-specification is shown above, immediately following the cursor-specification syntax.

FOR READ ONLY

Specifies the cursor associated with this cursor-expression is used for retrieval operations only. If specified, it prohibits the execution of both positioned UPDATEs and DELETEs that reference the cursor.

FOR UPDATE

Specifies that the cursor is used for positioned UPDATE operations.

OF column-name

Identifies a column that may be updated through positioned UPDATE statements. If no columns are specified, then all columns in the table may be updated.

Expansion of order-by-specification

ORDER BY

Sorts the rows in the result table defined by query-expression in ascending or descending order by the values in the specified columns. Rows are ordered first by the first column specified, then by the second column specified with the ordering established by the first column, then by the third column specified, and so on.

column-name

Specifies a sort column by name. Column-name must identify a column in the result table of the query expression.

table-name

Specifies the table, view, procedure or table procedure that includes the named column. For Expansion of Table-name expanded table-name syntax, see Identifying Entities in Schemas.

alias

Specifies the alias associated with the table, view, procedure or table procedure that includes the named column. Alias must be defined in the FROM parameter of the query specification that makes up the query expression.

column-number

Specifies a sort column by the position of the column in the result table. The first result column is in position 1.

Column-number must be an integer in the range 1 through the number of columns in the result table.

result-name

Specifies the sort column by the result name specified in the AS parameter of the query expression.

rowid-pseudo-column

Specifies the sort column as a ROWID pseudo-column. For expanded syntax, see Expansion of rowid-pseudo-column.

ASC

Indicates that the values in the specified column are to be sorted in ascending order. ASC is the default.

DESC

Indicates that the values in the specified column are to be sorted in descending order.

Usage

Updateable cursors

A cursor defined by a cursor specification is updateable if the cursor specification:

• Contains an updateable query-expression
• Does not contain an ORDER BY clause
• Does not contain a FOR READ ONLY clause

Updateable cursors may be referenced in positioned DELETE statements.

To reference a cursor in a positioned UPDATE statement, it must be updateable and the FOR UPDATE clause must be specified within the cursor specification.

Note: To ensure optimal performance when processing a cursor that is referenced in a positioned UPDATE statement, you should explicitly identify the columns to be updated rather than specifying FOR UPDATE without naming the columns.

Example

Defining a Cursor for retrieval-only

The following DECLARE CURSOR statement defines a static cursor by including a cursor-specification directly. In this case, the cursor being defined can only be used to retrieve rows from the database. By coding the FOR READ ONLY option, you ensure that neither positioned UPDATEs nor DELETEs are allowed against the cursor:

EXEC SQL
  DECLARE EMP_CURSOR CURSOR FOR
    SELECT EMP_ID, DEPT_ID, EMP_LNAME
       FROM EMPLOYEE
       FOR READ ONLY
END-EXEC

Defining a Dynamic Cursor for UPDATE Operations

The following set of code defines a dynamic cursor to be used to update the DEPT_ID column of the EMPLOYEE table. The cursor-specification containing the FOR UPDATE clause is first prepared and then an ALLOCATE CURSOR statement is used to create the cursor:

MOVE 'SELECT *
       FROM EMPLOYEE FOR UPDATE OF DEPT_ID'
  TO ST-TEXT.

EXEC SQL
  PREPARE 'EMP-STATEMENT' FROM :ST-TEXT
END-EXEC

EXEC SQL
  ALLOCATE 'EMP-CURSOR' CURSOR FOR 'EMP-STATEMENT'
END-EXEC