Requêtes récursives utilisant des expressions de table communes
Une expression de table commune (CTE, Common Table Expression) présente notamment l'avantage de pouvoir se référencer elle-même, créant ainsi une expression CTE récursive. Une expression CTE récursive est une expression dans laquelle une expression CTE initiale est exécutée de façon répétée pour retourner des sous-ensembles de données jusqu'à l'obtention de l'ensemble de résultats complet.
Une requête est qualifiée de récursive lorsqu’elle référence une expression CTE récursive. Le retour de données hiérarchiques est une utilisation courante de requêtes récursives, comme dans les cas suivants : affichage des employés dans un organigramme ou de données dans un scénario de nomenclatures dans lequel un produit parent possède un ou plusieurs composants qui eux-mêmes peuvent détenir des sous-composants ou être des composants d'autres parents.
Une expression CTE récursive peut sensiblement simplifier le code nécessaire à l'exécution d'une requête récursive dans une instruction SELECT, INSERT, UPDATE, DELETE ou CREATE VIEW. Dans les versions antérieures de SQL Server, une requête récursive requiert généralement l'utilisation de tables temporaires, de curseurs et d'une logique pour contrôler le flux des étapes récursives. Pour plus d'informations sur les expressions de table communes, consultez Utilisation d'expressions de table communes.
Structure d'une expression CTE récursive
La structure de l'expression CTE récursive dans Transact-SQL est similaire aux routines récursives dans les autres langages de programmation. Bien qu'une routine récursive dans les autres langages retourne une valeur scalaire, une expression CTE récursive peut retourner plusieurs lignes.
Une expression CTE récursive se compose de trois éléments :
Invocation de la routine.
La première invocation de l'expression CTE récursive comprend une ou plusieurs définitions CTE_query_definitions jointes par des opérateurs UNION ALL, UNION, EXCEPT ou INTERSECT. Étant donné que ces définitions de requête forment l'ensemble de résultats de base de la structure de l'expression CTE, elles sont désignées par le terme « membres d'ancrage ».
Les CTE_query_definitions sont considérées comme des membres d'ancrage sauf si elles référencent l'expression CTE elle-même. Toutes les définitions de requête de type membre d'ancrage doivent être positionnées avant la première définition de membre récursif. En outre, un opérateur UNION ALL doit être utilisé pour joindre le dernier membre d'ancrage au premier membre récursif.
Invocation récursive de la routine.
L'invocation récursive comprend une ou plusieurs définitions CTE_query_definitions jointes par des opérateurs UNION ALL qui référencent l'expression CTE elle-même. Ces définitions de requête sont désignées par le terme « membres récursifs ».
Contrôle de l'arrêt.
Le contrôle de l'arrêt est implicite ; la récursivité s'arrête lorsque aucune ligne n'est retournée par l'invocation précédente.
Notes
Une expression CTE récursive mal composée peut générer une boucle infinie. Par exemple, si la définition de requête de type membre récursif retourne les mêmes valeurs pour la colonne parent et la colonne enfant, une boucle infinie est créée. Lorsque vous testez les résultats d'une requête récursive, vous pouvez limiter le nombre de niveaux de récursivité autorisés pour une instruction spécifique, à l'aide de l'indicateur MAXRECURSION et d'une valeur comprise entre 0 et 32 767 dans la clause OPTION de l'instruction INSERT, UPDATE, DELETE ou SELECT. Pour plus d'informations, consultez Indicateurs de requête (Transact-SQL) et WITH common_table_expression (Transact-SQL).
Pseudo-code et sémantique
La structure de l'expression CTE récursive doit contenir au moins un membre d'ancrage et un membre récursif. Le pseudo-code suivant montre les composants d'une expression CTE récursive simple qui contient un seul membre d'ancrage et un seul membre récursif.
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
La sémantique de l'exécution récursive est la suivante :
Scinder l'expression CTE en membres d'ancrage et en membres récursifs.
Exécuter le(s) membre(s) d'ancrage créant la première invocation ou le premier ensemble de résultats de base (T0).
Exécuter le(s) membre(s) récursif(s) avec Ti comme entrée et Ti+1 comme sortie.
Répéter l'étape 3 jusqu'à ce qu'un ensemble vide soit retourné.
Retourner l'ensemble de résultats. Il s'agit d'une opération UNION ALL de T0 à Tn.
Exemple
L'exemple ci-dessous montre la sémantique de la structure de l'expression CTE récursive en retournant une liste hiérarchique d'employés, qui commence par l'employé de rang le plus élevé au sein de la société Adventure Works Cycles. L'exemple est suivi de la chronologie de l'exécution du code.
-- 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
Chronologie de l'exemple de code
L'expression CTE récursive, DirectReports, définit un membre d'ancrage et un membre récursif.
Le membre d'ancrage retourne l'ensemble de résultats de base T0. Il s'agit de l'employé de rang le plus élevé au sein de la société, c'est-à-dire d'un employé qui n'est sous les ordres d'aucun responsable.
L'ensemble de résultats retourné par le membre d'ancrage est le suivant :
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ NULL 1 Chief Executive Officer 0Le membre récursif retourne le ou les subordonnés directs de l'employé indiqué dans l'ensemble de résultats du membre d'ancrage. Pour ce faire, une opération de jointure est réalisée entre la table Employee et l'expression CTE DirectReports. C'est cette référence à l'expression CTE elle-même qui établit l'invocation récursive. En utilisant comme entrée l'employé indiqué dans l'expression CTE DirectReports (Ti), la jointure (MyEmployees.ManagerID = DirectReports.EmployeeID) retourne comme sortie (Ti+1) les employés ayant comme responsable (Ti). Par conséquent, la première itération du membre récursif retourne l'ensemble de résultats suivant :
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ 1 273 Vice President of Sales 1Le membre récursif est activé de façon répétée. La deuxième itération du membre récursif utilise le jeu de résultats à ligne unique de l'étape 3 (contenant EmployeeID273) comme valeur d'entrée et retourne le jeu de résultats suivant :
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ 273 16 Marketing Manager 2 273 274 North American Sales Manager 2 273 285 Pacific Sales Manager 2La troisième itération du membre récursif utilise comme valeur d'entrée le jeu de résultats ci-dessus et retourne le jeu de résultats suivant :
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ 16 23 Marketing Specialist 3 274 275 Sales Representative 3 274 276 Sales Representative 3 285 286 Sales Representative 3Le jeu de résultats final retourné par la requête en cours d'exécution est l'union de tous les jeux de résultats générés par le membre d'ancrage et le membre récursif.
L'ensemble de résultats complet retourné par l'exemple est le suivant :
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