Recursive DECLARE CURSOR Statement (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.

Structure

Syntax 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>

End of the code.

Example Example

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

End of the example.

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. 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 nth execution are used for the n+1th 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

More Information