Recursive DECLARE CURSOR Statement  

The recursive DECLARE CURSOR statement (recursive_declare_cursor_statement) can be used to receive bills of material by means of a command.

Syntax

<recursive_declare_cursor_statement> ::=
DECLARE <result_table_name> CURSOR FOR WITH RECURSIVE <reference_name> (<alias_name>,...) AS
(<initial_select> UNION ALL <recursive_select>) <final_select>

<initial_select> ::= <query_spec>
<recursive_select> ::= <query_spec>
<final_select> ::= <select_statement>

DECLARE C CURSOR FOR
WITH RECURSIVE PX (MAJOR, MINOR, NUMBER, MAINMAJOR) AS
  (SELECT W,X,Y,W FROM T WHERE W = 'aaa' UNION ALL
   SELECT W,X,Y,MAINMAJOR FROM T, PX WHERE MINOR = T.W)
 SELECT MAINMAJOR,MINOR,NUMBER FROM PX ORDER BY NUMBER

Explanation

If a result table name with the specified reference name existed before the recursive DECLARE CURSOR statement was executed, the corresponding cursor is closed implicitly.

<initial_select>

The QUERY specification (initial_select) is executed and the result is entered in a temporary result table whose  name is defined by specifying the reference name (reference_name). The column names contained in it receive the names from the list of alias names. The number of output columns in the QUERY specification must be identical to the number of alias names.

<recursive_select>

The QUERY specification recursive select should comprise a SELECT statement that contains at least the reference name in the FROM clause and one JOIN predicate between this table and a different table from the FROM clause.

The QUERY specification recursive_select is repeated until it does not produce a result. The respective results are (logically) entered in the temporary result table whose name is defined by the reference name. This table is extended continuously. It is ensured, however, that the results of the n^th execution are used for the n+1^th execution to avoid an endless loop.

<final_select>

The SELECT statement final_select must only contain one QUERY expression that comprises a QUERY specification.

This is a SELECT statement across the table with the specified reference name in which the following elements can be used: set functions, GROUP clause, HAVING clause, ORDER clause, LOCK option