• Статья

Рекурсивные запросы, использующие обобщенные табличные выражения

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

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

Рекурсивное ОТВ может существенно упростить код, требуемый для запуска рекурсивного запроса в рамках инструкций SELECT, INSERT, UPDATE, DELETE или CREATE VIEW. В более ранних версиях SQL Server, чтобы контролировать поток рекурсивных шагов, рекурсивный запрос обычно требует использование временных таблиц, курсоров и логики. Дополнительные сведения об обобщенных табличных выражениях см. в разделе Применение обобщенных табличных выражений.

Структура рекурсивного ОТВ

Структура рекурсивного ОТВ в Transact-SQL аналогична рекурсивным процедурам в других языках программирования. Но рекурсивные процедуры в других языках возвращают скалярное значение, а рекурсивное ОТВ может возвращать несколько строк.

Рекурсивное ОТВ состоит из трех элементов.

  1. Вызов процедуры.

    Первый вызов рекурсивного ОТВ состоит из одного или более параметров CTE_query_definitions, соединенных операторами UNION ALL, UNION, EXCEPT или INTERSECT. Так как данные определения запроса формируют базовый результирующий набор структуры ОТВ, они называются закрепленными элементами.

    Параметры CTE_query_definitions считаются закрепленными элементами, если они не ссылаются на само ОТВ. Все определения запросов закрепленных элементов должны размещаться перед определением первого рекурсивного элемента, а оператор UNION ALL должен использоваться для соединения последнего закрепленного элемента с первым рекурсивным элементом.

  2. Рекурсивный вызов процедуры.

    Рекурсивный вызов включает в себя от одного или более параметров CTE_query_definitions, которые соединены операторами UNION ALL, ссылающимися на само ОТВ. Данные определения запросов называются рекурсивными элементами.

  3. Проверка завершения.

    Проверка завершения происходит неявно; рекурсия останавливается, если из предыдущего вызова не вернулась ни одна строка.

ПримечаниеПримечание

Неправильно составленное рекурсивное ОТВ может привести к бесконечному циклу. Например, если определение запроса рекурсивного члена возвращает одинаковые значения как для родительского, так и для дочернего столбца, то образуется бесконечный цикл. При тестировании результатов рекурсивного запроса можно ограничить число уровней рекурсии, допустимое для конкретных инструкций, используя подсказку 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

Рекурсивное выполнение имеет следующую семантику:

  1. разбиение ОТВ на закрепленный и рекурсивный элементы;

  2. запуск закрепленных элементов с созданием первого вызова или базового результирующего набора (T0);

  3. запуск рекурсивных элементов, где Ti — это вход, а Ti+1 — это выход;

  4. повторение шага 3 до тех пор, пока не вернется пустой набор;

  5. возвращение результирующего набора. Результирующий набор получается с помощью инструкции UNION ALL от T0 до Tn.

Пример

Следующий пример показывает семантику структуры рекурсивного ОТВ, возвращая иерархический список служащих компании Adventure Works Cycles, начиная с высшего должностного лица. За примером следует анализ выполнения кода.

-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
    EmployeeID smallint NOT NULL,
    FirstName nvarchar(30)  NOT NULL,
    LastName  nvarchar(40) NOT NULL,
    Title nvarchar(50) NOT NULL,
    DeptID smallint NOT NULL,
    ManagerID int NULL,
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES 
 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16,  N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23,  N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

USE AdventureWorks2008R2;
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 dbo.MyEmployees AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.BusinessEntityID 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 dbo.MyEmployees AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
    INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, DeptID, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
    ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Sales and Marketing' OR Level = 0;
GO

Анализ примера кода

  1. Рекурсивное ОТВ DirectReports определяет один закрепленный элемент и один рекурсивный элемент.

  2. Закрепленный элемент возвращает базовый результирующий набор T0. Это самое главное должностное лицо компании; значит, этот служащий не отчитывается перед управляющим.

    Ниже приведен результирующий набор, возвращенный закрепленным элементом.

    ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
NULL      1          Chief Executive Officer        0

  3. Рекурсивный элемент возвращает прямых подчиненных служащего в результирующий набор закрепленного элемента. Это получается при соединении таблицы Employee и DirectReports ОТВ. Это ссылка на само ОТВ, которое устанавливает рекурсивный вызов. В зависимости от служащего в ОТВ DirectReports в качестве входа (Ti) соединение (MyEmployees.ManagerID = DirectReports.EmployeeID) возвращает выход (Ti+1) — это служащие, руководителем которых является (Ti). Таким образом, первый шаг цикла рекурсивного элемента возвращает данный результирующий набор:

    ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
1         273        Vice President of Sales       1

  4. Рекурсивный элемент постоянно активируется. Второй шаг цикла рекурсивного элемента использует однострочный результирующий набор в шаге 3 (содержащий EmployeeID273) в качестве входного значения и возвращает следующий результирующий набор:

    ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
273       16         Marketing Manager             2
273       274        North American Sales Manager  2
273       285        Pacific Sales Manager         2

    Третий шаг цикла рекурсивного элемента использует вышеупомянутый результирующий набор в качестве входного значения и возвращает данный результирующий набор:

    ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
16        23         Marketing Specialist          3
274       275        Sales Representative          3
274       276        Sales Representative          3
285       286        Sales Representative          3

  5. Конечный результирующий набор, возвращенный запущенным запросом, представляет собой объединение всех результирующих наборов, сформированных закрепленным и рекурсивным элементами.

    Ниже приведен полный результирующий набор, возвращенный примером.

    ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
NULL      1          Chief Executive Officer       0
1         273        Vice President of Sales       1
273       16         Marketing Manager             2
273       274        North American Sales Manager  2
273       285        Pacific Sales Manager         2
16        23         Marketing Specialist          3
274       275        Sales Representative          3
274       276        Sales Representative          3
285       286        Sales Representative          3