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.
[!REMARQUE]
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'instruction qui exécute l'expression CTE limite l'ensemble de résultats aux employés du groupe Recherche et développement. L'exemple est suivi de la chronologie de l'exécution du code.
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
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 109 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 (Employee.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 --------- ---------- --------------------------------------- ------ 109 12 Vice President of Engineering 1Le membre récursif est activé de façon répétée. La deuxième itération du membre récursif utilise l'ensemble de résultats à ligne unique de l'étape 3 (contenant EmployeeID12) comme valeur d'entrée et retourne l'ensemble de résultats suivant :
ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ 12 3 Engineering Manager 2La troisième itération du membre récursif utilise comme valeur d'entrée l'ensemble de résultats à ligne unique ci-dessus (contenant EmployeeID3)) et retourne l'ensemble de résultats suivant :
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 3La quatrième itération du membre récursif utilise comme valeur d'entrée l'ensemble de lignes précédent correspondant aux valeurs EmployeeID4, 9, 11, 158, 263, 267 et 270.
Ce processus est répété jusqu'à ce que le membre récursif retourne un ensemble de résultats vide.
L'ensemble de résultats final retourné par la requête en cours d'exécution est l'union de tous les ensembles 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 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)