Рекурсивные запросы, использующие обобщенные табличные выражения
Обобщенное табличное выражение (ОТВ) имеет значительное преимущество, так как оно может ссылаться на себя, создавая рекурсивное ОТВ. Рекурсивное ОТВ является выражением, в котором начальное ОТВ многократно выполняется, чтобы возвращать подмножество данных до тех пор, пока не получится конечный результирующий набор.
Запрос именуется рекурсивным, если он ссылается на рекурсивное ОТВ. Обычно рекурсивные запросы используются для возвращения иерархических данных, например: отображение сотрудников в структуре организации или данных в сценарии ведомости материалов, в котором родительский продукт состоит из одного или более компонентов, и эти компоненты могут, в свою очередь, состоять из вспомогательных компонентов или являться компонентами других родителей.
Рекурсивное ОТВ может существенно упростить код, требуемый для запуска рекурсивного запроса в рамках инструкций SELECT, INSERT, UPDATE, DELETE или CREATE VIEW. В более ранних версиях SQL Server, чтобы контролировать поток рекурсивных шагов, рекурсивный запрос обычно требует использование временных таблиц, курсоров и логики. Дополнительные сведения об обобщенных табличных выражениях см. в разделе Применение обобщенных табличных выражений.
Структура рекурсивного ОТВ
Структура рекурсивного ОТВ в Transact-SQL аналогична рекурсивным процедурам в других языках программирования. Но рекурсивные процедуры в других языках возвращают скалярное значение, а рекурсивное ОТВ может возвращать несколько строк.
Рекурсивное ОТВ состоит из трех элементов.
Вызов процедуры.
Первый вызов рекурсивного ОТВ состоит из одного или более параметров CTE_query_definitions, соединенных операторами UNION ALL, UNION, EXCEPT или INTERSECT. Так как данные определения запроса формируют базовый результирующий набор структуры ОТВ, они называются закрепленными элементами.
Параметры CTE_query_definitions считаются закрепленными элементами, если они не ссылаются на само ОТВ. Все определения запросов закрепленных элементов должны размещаться перед определением первого рекурсивного элемента, а оператор UNION ALL должен использоваться для соединения последнего закрепленного элемента с первым рекурсивным элементом.
Рекурсивный вызов процедуры.
Рекурсивный вызов включает в себя от одного или более параметров CTE_query_definitions, которые соединены операторами UNION ALL, ссылающимися на само ОТВ. Данные определения запросов называются рекурсивными элементами.
Проверка завершения.
Проверка завершения происходит неявно; рекурсия останавливается, если из предыдущего вызова не вернулась ни одна строка.
.gif "Примечание") Примечание |
|---|
Неправильно составленное рекурсивное ОТВ может привести к бесконечному циклу. Например, если определение запроса рекурсивного члена возвращает одинаковые значения как для родительского, так и для дочернего столбца, то образуется бесконечный цикл. При тестировании результатов рекурсивного запроса можно ограничить число уровней рекурсии, допустимое для конкретных инструкций, используя подсказку MAXRECURSION и значение от 0 до 32 767 в предложении OPTION инструкций INSERT, UPDATE, DELETE или SELECT. Дополнительные сведения см. в разделах Подсказки в запросах (Transact-SQL) и WITH обобщенное_табличное_выражение (Transact-SQL). |
Псевдокод и семантика
Структура рекурсивного ОТВ должна содержать минимум один закрепленный элемент и один рекурсивный элемент. Следующий псевдокод отображает компоненты простого рекурсивного ОТВ, которое содержит один закрепленный элемент и один рекурсивный элемент.
WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member is defined.
UNION ALL
CTE_query_definition –- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name
Рекурсивное выполнение имеет следующую семантику:
разбиение ОТВ на закрепленный и рекурсивный элементы;
запуск закрепленных элементов с созданием первого вызова или базового результирующего набора (T0);
запуск рекурсивных элементов, где Ti — это вход, а Ti+1 — это выход;
повторение шага 3 до тех пор, пока не вернется пустой набор;
возвращение результирующего набора. Результирующий набор получается с помощью инструкции UNION ALL от T0 до Tn.
Пример
Следующий пример показывает семантику структуры рекурсивного ОТВ, возвращая иерархический список служащих компании Adventure Works Cycles, начиная с высшего должностного лица. Инструкция, выполняющая ОТВ, сокращает результирующий набор до служащих отдела исследований и разработок. За примером следует анализ выполнения кода.
USE AdventureWorks;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
0 AS Level
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
Level + 1
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Research and Development' OR Level = 0;
GO
Анализ примера кода
Рекурсивное ОТВ DirectReports определяет один закрепленный элемент и один рекурсивный элемент.
Закрепленный элемент возвращает базовый результирующий набор T0. Это самое главное должностное лицо компании; значит, этот служащий не отчитывается перед управляющим.
Ниже приведен результирующий набор, возвращенный закрепленным элементом.
ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ NULL 109 Chief Executive Officer 0Рекурсивный элемент возвращает прямых подчиненных служащего в результирующий набор закрепленного элемента. Это получается при соединении таблицы Employee и DirectReports ОТВ. Это ссылка на само ОТВ, которое устанавливает рекурсивный вызов. В зависимости от служащего в ОТВ DirectReports в качестве входа (Ti), соединение (Employee.ManagerID = DirectReports.EmployeeID) возвращает выход (Ti+1) — это служащие, чьим управляющим является (Ti). Таким образом, первый шаг цикла рекурсивного элемента возвращает данный результирующий набор:
ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ 109 12 Vice President of Engineering 1Рекурсивный элемент постоянно активируется. Второй шаг цикла рекурсивного элемента использует однострочный результирующий набор в шаге 3 (содержащий EmployeeID12) в качестве входного значения и возвращает следующий результирующий набор:
ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ 12 3 Engineering Manager 2Третий шаг цикла рекурсивного элемента использует вышеупомянутый однострочный результирующий набор (содержащий EmployeeID3)) в качестве входного значения и возвращает данный результирующий набор:
ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ 3 4 Senior Tool Designer 3 3 9 Design Engineer 3 3 11 Design Engineer 3 3 158 Research and Development Manager 3 3 263 Senior Tool Designer 3 3 267 Senior Design Engineer 3 3 270 Design Engineer 3Четвертый шаг цикла рекурсивного элемента использует предыдущий результирующий набор для EmployeeID значений 4, 9, 11, 158, 263, 267 и 270 в качестве входного значения.
Данный процесс повторяется до тех пор, пока рекурсивный элемент не вернет пустой результирующий набор.
Конечный результирующий набор, возвращенный запущенным запросом, представляет собой объединение всех результирующих наборов, сформированных закрепленным и рекурсивным элементами.
Ниже приведен полный результирующий набор, возвращенный примером.
ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ NULL 109 Chief Executive Officer 0 109 12 Vice President of Engineering 1 12 3 Engineering Manager 2 3 4 Senior Tool Designer 3 3 9 Design Engineer 3 3 11 Design Engineer 3 3 158 Research and Development Manager 3 3 263 Senior Tool Designer 3 3 267 Senior Design Engineer 3 3 270 Design Engineer 3 263 5 Tool Designer 4 263 265 Tool Designer 4 158 79 Research and Development Engineer 4 158 114 Research and Development Engineer 4 158 217 Research and Development Manager 4 (15 row(s) affected)