Background documentationDECLARE CURSOR Statement (declare_cursor_statement) Locate this document in the navigation structure

 

A DECLARE CURSOR statement (declare_cursor_statement) defines and generates a named results table with the name result_table_name.

Structure

Syntax Syntax

  1. <declare_cursor_statement> ::=
      DECLARE <result_table_name> CURSOR FOR <select_statement>
End of the code.
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 whether the system searches for all the rows in the results table and generates the results table physically, or – in the case of a FETCH statement – searches for the next relevant row in the result table but does not store it physically.

If the results table is empty, you will receive a message to this effect.

More Information

QUERY Statement