Recursive CTE page 1 |
||
A CTE (common table expression) allows to determine a table in framework of a query for multiple referencings.CTE has one more important. With it, you can write a recursive query, i.e. request, which, once written, will be repeated many times until some condition is true. A recursive CTE is as follows:
A recursive CTE-query differs from ordinary CTE in recursive part only that introduced by UNION ALL statement. Note that a there is reference to CTE name in recursive part, i.e., CTE refers to itself within itself. This is, in fact, there is recursion. Naturally, anchor and recursive parts must have identical column set. We turn to the example. Consider the task of getting the alphabet, i.e. table of alphabetic characters - uppercase Latin letters. To be a base for comparison, we solve this problem, first by generating a numerical sequence, which was seen in section 8.1.
The decision via the recursive CTE:
In anchor part of query we define ASCII-code of the first letters of the alphabet and the corresponding symbol. In recursive part of query we simply increase the ASCII-code by one, referring to the CTE in the FROM clause. As a result, the lines with next letters will be added sequentially (UNION ALL) after line with first letter, in the order of their ASCII-code. Iteration will continue as long as the condition code +1 <= ascii ('Z') is true, i.e. until a letter Z will not be added. The statement actually intended to refer to the CTE, start recursion and output results. Everything else can be regarded as a definition. Note that by default is allowed 100 iterations. This means that if the termination condition is not fulfilled earlier iterations, then the recursion is stopped after 100 iterations. Maximum number of iterations can be changed using "hint"
The statement terminated. The maximum recursion N has been exhausted before statement completion. |