2. Рекурсивные запросы.

Выражения CTE можно использовать для реализации рекурсивных алгоритмов. Рекурсивная логика удобна, когда требуется написать алгоритм, вызывающий самого себя — это часто делается для прохода по вложенному набору данных. Написание рекурсивной логики может оказаться сложной задачей, особенно на таком языке, как T-SQL, однако это одна из тех специальных задач, для решения которых были разработаны выражения CTE. Простейший рецепт создания рекурсивного выражения CTE выглядит следующим образом:

• Создать запрос, возвращающий верхний уровень (это закрепленный элемент, начальная точка рекурсии).

• Написать рекурсивный запрос (это рекурсивный элемент, показывает как перейти к следующему шагу рекурсии).

• Выполнить операцию UNION для первого и рекурсивного запросов.

• Разместить UNION в СТЕ.

• Связать CTE и рекурсивный запрос внутри CTE, чтобы обеспечить пошаговое выполнение.

В результате рекурсивное выражение CTE будет выглядеть приблизительно следующим образом.

WITH

myRecursiveCTE(col1, col2, ... coln)

AS

( -- Anchor Member Query

UNION ALL

-- Recursive Member Query that references myRecursiveCTE

)

При написании пользовательской рекурсивной процедуры, не содержащей выражений CTE, необходимо включить явное завершающее предложение. Завершающее предложение отвечает за обеспечение завершения (в конце концов) рекурсивного алгоритма и возвращение наверх по стеку рекурсивных вызовов. Без этого предложения ваш код может уйти в бесконечный цикл.

Выражения CTE обладают двумя свойствами, которые облегчают работу с завершающими предложениями. Первое состоит в том, что если рекурсивный член возвращает пустые записи, возникает неявное завершающее предложение. В таком случае запрос рекурсивного члена не обращается рекурсивно к выражению CTE, а вместо этого возвращает наверх стек вызовов. Другая особенность состоит в том, что имеется возможность явно установить уровень MAXRECURSION.

Уровень MAXRECURSION можно установить явно в пакете, содержащем CTE, или посредством параметра на серверной стороне (по умолчанию в рамках сервера действует значение 100, если только оно не было изменено). Этот параметр ограничивает число вызовов выражения CTE, обращенных к нему самому. При достижении предельного значения создается исключение. В синтаксисе для установки уровня MAXRECURSION необходимо использовать предложение OPTION в операторе SELECT, следующим за выражением CTE, как показано далее

SELECT * FROM CTE

OPTION (MAXRECURSION 7);

Существуют некоторые другие правила, о которых следует помнить при построении рекурсивных выражений CTE. В рекурсивное выражение CTE должен входить как закрепленный, так и рекурсивный элемент. Оба элемента должны иметь одинаковое число столбцов, и столбцы, принадлежащие обоим элементам, должны иметь одинаковые типы данных. Рекурсивный элемент может ссылаться на выражение CTE только один раз, и в элементах не разрешается использовать следующие предложения или ключевые слова.

• SELECT DISTINCT

• GROUP BY

• HAVING

• TOP

• LEFT/RIGHT OUTER JOIN

Пример простой иерархии [Frederic BROUARD (оригинал: Recursive Queries in SQL:1999 and SQL Server 2005) Перевод Моисеенко С.И. ]

Создадим таблицу, которая содержит типологию транспортных средств:

CREATE TABLE Tree

(ID INTEGER NOT NULL PRIMARY KEY,