DECLARE CURSOR statement (declare_cursor_statement) 

The DECLARE CURSOR statement (declare_cursor_statement) defines a named results table with a result_table_name.

Syntax

<declare_cursor_statement> ::= DECLARE <result_table_name> CURSOR FOR <select_statement>

Explanation

Existing results tables are implicitly deleted when a results table is generated with the same name.

All results tables generated within the current transaction are deleted implicitly at the end of the transaction using the ROLLBACK statement.

All results tables are deleted implicitly at the end of the session using the RELEASE statement. A CLOSE statement can be used to delete them explicitly first.

If the name of a results table is identical to that of a base table, view table, or a synonym, these tables cannot be accessed as long as the results table exists. If an INSERT, UPDATE, DELETE or LOCK statement is carried out for a results table whose name is identical with the name of a base table, view table or synonym, the corresponding non-results table is accessed.

Whenever a results table is processed, the cursor position can be before the first row, on a row, after the last row or between two rows. After generating the results table, the cursor is positioned before the first row of the results table.

The search strategy used determines how the search is carried out. When the SELECT statement is executed, the system searches for all the rows in the results table and generates the results table physically. When a FETCH statement is executed, the system searches for the next relevant row in the result table but does not store it physically.

If the results table is empty, you receive the return code 100 – row not found.