OPEN Statement

Cursors in SQL

UPDATE CURRENT OF Statement

FETCH Statement

statement_FETCH ::= FETCH [ [ order ] FROM ] cursor_name [ INTO target [ {, target } … ] ];
order ::= NEXT | PRIOR | FIRST | LAST | ABSOLUTE expression | RELATIVE expression

The FETCH statement sets the specified cursor on the row specified by the order clause and writes values to columns consecutively into the specified targets. Setting the cursor onto the specified row is affected by the following statements: DELETE CURRENT OF and UPDATE CURRENT OF.

Cursor_name must either be declared in a cursor declaration or designate a query that belongs to the application stored in the database.

If order is not set, NEXT is used. Order has the following meaning:

• NEXT: Next row (first row, if the cursor is set before the first row)
• PRIOR: Previous row (last row, if the cursor is set after the last row)
• FIRST: First row of the result
• LAST: Last row of the result
• ABSOLUTE: The row whose ordinal number in the result is set by the expression (rows are numbered from 1)
• RELATIVE: The row whose distance from the common row is the number of rows specified by the expression (negative value specifies a row before the common row)

The expression must be an integer type.

The FETCH statement without specifying a target will only change the cursor position.

The target must be a variable whose type allows assigning the value of the corresponding cursor column. The number of targets must be the same as the number of cursor columns.

If the specified cursor is not opened or is closed, the error sqlstate 24000 (SQ_INVALID_CURSOR_STATE) occurs. If the cursor is not declared or does not exist in an application, the error sql state 34000 (SQ_INVALID_CURSOR_NAME) occurs. If the specified row does not exist, the error sqlstate 02000 (NOT_FOUND) occurs. If the column cannot be assigned to the target because of a type difference (after automatic conversion fails), the error sqlstate W0141 (CANNOT_CONVERT_DATA) occurs.

Example:

DECLARE reccount INT;
DECLARE sumamount REAL;
...
OPEN sum_archive;          // a fixed cursor stored in the application
 FETCH FIRST FROM sum_archive INTO reccount, sumamount;
CLOSE sum_archive; 

Query sum_archive is defined as follows:

SELECT COUNT(*),SUM(price)
FROM Archive

OPEN Statement

Cursors in SQL

UPDATE CURRENT OF Statement