You use DECLARE CURSOR to define a cursor. You can use the cursor to access the individual rows in a derived table. The current row on which the cursor is positioned can be read. If the cursor is updatable, you can also update and delete rows.
The cursor declaration must physically precede any statement that uses the cursor in the program text. All the statements that use this cursor must be located in the same compilation unit. This does not apply for local cursors (in procedures).
DECLARE CURSOR is not an executable statement.
DECLARE cursor [SCROLL | NO SCROLL ] CURSOR
[WITH HOLD | WITHOUT HOLD]
FOR { cursor_description | statement_id }
cursor_description ::=
query_expression
[ORDER BY sort_expression [ASC | DESC]
[, sort_expression [ASC | DESC]]...]
[FETCH FIRST max ROWS ONLY]
[FOR { READ ONLY | UPDATE [OF column ,...] }]
sort_expression ::= { column | { column ( posno ) | column[posno] } | column_no | expression }
posno ::= unsigned_integer
column_no ::= unsigned_integer
max ::= unsigned_integer
cursor
Name of the cursor. You cannot define more than one cursor with the same name within a compilation unit. The scope of validity of the cursor is limited to the compilation unit in which the cursor is defined. This does not apply for local cursors (in procedures).
SCROLL
You can position the cursor on any row in the derived table and in any order with FETCH NEXT/PRIOR/FIRST/LAST/RELATIVE/ABSOLUTE.
You can only specify SCROLL if no FOR UPDATE clause was defined in the cursor description of cursor.
If you specify SCROLL, cursor cannot be changed. The FOR READ ONLY clause applies implicitly.
NO SCROLL
The derived table can only be read sequentially. The cursor can only be positioned on the next row. In FETCH, only the position specification NEXT is permitted.
WITH HOLDA
A cursor can be defined with WITH HOLD. Keeps such a cursor open at the end of the transaction, even after COMMIT WORK. WITH HOLD cannot be specified for local cursors (in procedures), see section "Cursor".
Nevertheless, if a cursor defined with WITH HOLD is opened with OPEN or positioned with FETCH within a transaction and the transaction is terminated with ROLLBACK, the cursor will be closed regardless. The cursor will also be closed automatically at the end of the SQL session.
WITHOUT HOLD
Closes any open cursors at the end of the transaction.
cursor_description
Declares a static cursor.
cursor_description defines the derived table and the attributes of the cursor. The earliest point at which a row in the derived table can be selected is when you open the cursor with OPEN. The latest point at which a row can be selected is when you execute a FETCH statement.
statement_id
Declares a dynamic cursor.
statement_id is the name of a dynamic cursor description. You can specify a dynamic cursor description at program runtime. The same clauses can be used as in a static cursor description. You must prepare a dynamic cursor description with a PREPARE statement in which the name statement_id is used.
query_expression
Query expression for selecting rows and column from base tables or views.
In query_expression the value for host variables, procedure parameters and procedure variables is only determined when the cursor is opened. Special literals and time functions that are used in query_expression are not evaluated until the cursor is opened.
ORDER BY
The ORDER BY clause indicates the columns according to which the derived table is to be sorted. The rows are sorted according to the values in the column specified first. If rows occur which have the same values in the first column according to the comparison rules (see section "Comparison of two rows"ff), these will be sorted according to the second column, and so on. In SESAM/SQL, NULL values are considered smaller than all non-NULL values for sorting purposes.
The order of rows with the same value in all the sort columns is undefined.
You can only specify ORDER BY if no FOR UPDATE clause was declared for the cursor description of cursor.
If you specify ORDER BY, cursor cannot be changed. The FOR READ ONLY clause applies implicitly.
ORDER BY omitted: The order of the rows in the cursor table is undefined.
column
Name of the column in query_expression according to which the table is to be sorted. column must be an unqualified column name, excluding the table name. It must belong to the derived table created by query_expression.
{column(pos_no), column[pos_no]}
Element of a multiple column according to which the table is to be sorted. pos_no is an unsigned integer which indicates the position number of the column element in the multiple column. Otherwise, the column element must belong to the derived table created by query_expression.
column_number
Number of the column to be used as the basis for sorting.
column_number is an unsigned integer where1 <= column_number <= number of derived columns.
By specifying a column number, you can also use columns that do not have a name, or which do not have a unique name, as the basis for sorting.
column_number can be an atomic column or a multiple column with the dimension 1.
expression
It is also possible to sort a table on the basis of expressions that are not present in the derived table, e.g. UPPER(column).
The following conditions must be satisfied:
query_expression must be a simple SELECT expression.
expression may not consist of just one literal.
expression must not contain any subqueries or aggregate functions.
Columns of tables specified in the FROM clause may be used in expression, even if they are not included in the SELECT list.
ASC
The values in the column involved are sorted in ascending order.
DESC
The values of the column involved are sorted in descending order.
FETCH FIRST max ROWS ONLY
Limits the number of hits returned by a cursor to max (unsigned integer > 0) sets of hits. If the cursor position is greater than max, an SQLSTATE is returned (no data, class 02xxx). A cursor with this clause is not updatable.
FOR READ ONLY
The FOR READ ONLY clause specifies that cursor can only be used to read the records of the derived table (read-only cursor).
If the relevant query expression is not updatable, the FOR READ ONLY clause applies implicitly (see section "Updatability of query expressions"). It also applies if SCROLL, ORDER BY or FETCH FIRST max ROWS ONLY was specified in the cursor declaration.
FOR UPDATE
You can only use the FOR UPDATE clause if the relevant query expression is updatable (see section "Updatability of query expressions") and neither SCROLL nor ORDER BY nor FETCH FIRST max ROWS ONLY was specified. You use a FOR UPDATE clause to specify which columns in the underlying table can be updated via the cursor with UPDATE...WHERE CURRENT OF.
If a PREFETCH pragma has been defined for the cursor concerned, the FOR UPDATE clause disables this pragma (see section "PREFETCH pragma").
FOR UPDATE omitted: If the cursor is updatable (see section "Defining a cursor") and the FOR READ ONLY clause is not specified, you can update all the columns of the underlying table with UPDATE...WHERE CURRENT.
OF column,...
Only the specified columns can be updated with UPDATE...WHERE CURRENT OF.For column, specify the name of a column in the table that the updatable cursor references. column is the unqualified name of the column in the underlying table, regardless of whether a new column name was defined in the query expression of the cursor description.
Example
In the example below, an updatable cursor cur is declared. The underlying table is TAB. Only column col in table TAB can be updated via cursor CUR. To do this, a FOR UPDATE clause with the column name COL is specified in the cursor description.
DECLARE cur CURSOR FOR SELECT corr.col AS column FROM tab AS corr FOR UPDATE OF col
The unqualified, original column name COL is used in the FOR UPDATE clause although the column is renamed in the SELECT list and the table is renamed in the FROM clause.
OF column,... omitted: Each column in the underlying table can be updated with UPDATE...WHERE CURRENT OF.
Examples
The cursor CUR_ORDER selects ORDER_NUM, CUST_NUM, CONTACT_NUM, ORDER_TEXT, TARGET and ORDER_STAT for orders numbered between 300 and 500. The derived table is then sorted on the basis of the order number in ascending order.
DECLARE cur_order CURSOR FOR SELECT order_num, cust_num, contact_num, order_text, actual, order_stat FROM orders WHERE order_num BETWEEN 300 AND 500 ORDER BY order_num ASC
The cursor CUR_ORDER1 selects ORDER_NUM, ORDER_DATE, ORDER_TEXT and ORDER_STAT for orders whose customer number is specified in the host variable CUSTOMER_NO.
DECLARE cur_order1 CURSOR FOR SELECT order_num, order_date, order_text, order_stat FROM orders WHERE cust_num= :CUSTOMER_NO
Use the cursor CUR_VAT to select all services for which no VAT is calculated. It is specified with WITH HOLD so that it remains open even after COMMIT WORK, provided it is open at the end of the transaction.
DECLARE CUR_VAT CURSOR WITH HOLD FOR SELECT service_num, service_text, vat FROM service WHERE vat=0.00 FOR UPDATE
Block mode for a static cursor is specified as follows:
--%PRAGMA PREFETCH blocking_factor DECLARE cursor CURSOR FOR cursor_description
See also
CLOSE, DELETE, FETCH, INSERT, OPEN, PREPARE, SELECT, UPDATE