Table des matières

I Introduction 1

II Les requêtes récursives 2

II.1 Expression de Table Commune . . . . . . . . . . . . . . . . . . . . 2II.2 Structure d'une expression CTE récursive . . . . . . . . . . . . . . 2II.3 Syntaxe d'une requête récursive . . . . . . . . . . . . . . . . . . . . 3II.4 Etapes de contruction d'une requête récurssive . . . . . . . . . . . . 4

II.4.1 Sémantique d'exécution . . . . . . . . . . . . . . . . . . . . 5II.5 Avantages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5

IIIConclusion 7

i

I

Introduction

En informatique la récursion est une technique particulière, capable dans cer-tains cas de traiter des problèmes complexes : quelques lignes su�sent à e�ectuerun travail considérable. Mais la récursion induit certains e�ets indésirables : les res-sources pour e�ectuer le traitement sont maximisées par le fait que chaque appelréentrant du processus nécessite l'ouverture d'un environnement de travail completce qui possède un coût généralement très élevé en mémoire. Ceci est en générale,pour SQL Server en particulier, une question qui se pose est : Comment e�ectuerdes requêtes récursives ?. C'est une problématique classique en développement quiest souvent déportée sur le langage externe, ce qui provoque une multiplication dunombre des requêtes envoyées au serveur. Ce problème est ajouté dans la normeSQL :1999, et donc a été intégré dans SQL Server 2005.

1

II

Les requêtes récursives

Introduite avec la norme SQL :1999, la technique des requêtes récursives permetde résoudre des problèmes de parcours d'arbres (hiérarchie par exemple) ou degraphes. L'écriture de requête récursive nécessite l'utilisation d'une nouvelle formede requêtes introduite avec le concept d'Expression de Table Commune.[1]

II.1 Expression de Table Commune

Common Table Expressions (CTE) ou les Expressions de Table Communes enfrançais sont comparables à des tables temporaires qui peuvent être appelées encours de requête mais dont l'existence n'est pas réelle. Il permet aussi d'éviter lacréation d'une VUE pour le cas d'un besoin très ponctuel.

un CTE peut être représenté comme une table virtuelle qui permet par exemplede faire une boucle récursive dont les résultats seront stockés dans ce CTE.[1]

Une expression CTE peut être utilisée pour [2] :� Créer une requête récursive.� Remplacer une vue lorsque l'usage général d'une vue n'est pas né-cessaire, c'est-à-dire que la dé�nition n'a pas besoin d'être stockéedans des métadonnées ;

� Permettre le groupement par une colonne dérivée d'une sous-sélectionscalaire, ou d'une fonction non déterministe ou à accès externe ;

� Faire plusieurs fois référence à la table résultante dans la mêmeinstruction.

II.2 Structure d'une expression CTE récursive

La structure de l'expression CTE récursive dans Transact-SQL est similaire auxroutines récursives dans les autres langages de programmation. Bien qu'une routine

2

II. Les requêtes récursives

récursive dans les autres langages retourne une valeur scalaire, une expression CTErécursive peut retourner plusieurs lignes.

Une expression CTE récursive se compose de trois éléments :[2]

1. Invocation de la routine. La première invocation de l'expression CTE récursivecomprend une ou plusieurs dé�nitions CTE_query_de�nitions jointes par desopérateurs UNION ALL, UNION, EXCEPT ou INTERSECT. Étant donnéque ces dé�nitions de requête forment l'ensemble de résultats de base de lastructure de l'expression CTE, elles sont désignées par le terme � membresd'ancrage �.Les CTE_query_de�nitions sont considérées comme des membres d'ancragesauf si elles référencent l'expression CTE elle-même. Toutes les dé�nitions derequête de type membre d'ancrage doivent être positionnées avant la premièredé�nition de membre récursif. En outre, un opérateur UNION ALL doit êtreutilisé pour joindre le dernier membre d'ancrage au premier membre récursif.

2. Invocation récursive de la routine. L'invocation récursive comprend une ouplusieurs dé�nitions CTE_query_de�nitions jointes par des opérateurs UNIONALL qui référencent l'expression CTE elle-même. Ces dé�nitions de requêtesont désignées par le terme � membres récursifs �.

3. Contrôle de l'arrêt. Le contrôle de l'arrêt est implicite ; la récursivité s'arrêtelorsque aucune ligne n'est retournée par l'invocation précédente.

II.3 Syntaxe d'une requête récursive

Un CTE est toujours dé�ni par le mot WITH associé avec le nom de celui-ci.Les colonnes retournées sont ensuite transmise par celui-ci. Cette déclaration sefait automatiquement lors de l'exécution. Ensuite, la dé�nition de la sélection queretourne ce CTE, dans l'exemple suivant la valeur de retour est 1234567.[1]

Figure II.1 � Syntaxe CTE

3

II. Les requêtes récursives

La conception d'un CTE simple est similaire à une vue. il est donc possible defaire des agrégations de valeurs directement utilisables dans la suite d'un script SQLavec ce procédé.

II.4 Etapes de contruction d'une requête récurssive

L'exemple suivant décrit comment construire une requête récurssive compléte.

1. Décrire le nom de l'expression CTE ainsi que les colonnes associées :WITH [DirectReports] ([ManagerID], [EmployeeID],

[FirstName], [LastName], [Title])

2. Ensuite, il faut écrire ce qui s'appelle l'accroche, c'est à dire la condition dedépart.SELECT e.[ManagerID], e.[EmployeeID],

e.[FirstName], e.[LastName], e.[Title]

FROM [dbo].[MyEmployees] AS e

WHERE [ManagerID] IS NULL

3. La troisième partie correspond à la dé�nition de la récursivité. Pour cela, ilfaut utiliser le nom de l'expression CTE dans le corps de l'expression CTElui-même. Dans ce cas ; l'instruction est de lister les employés qui dépendentdu manager renvoyé dans l'expression CTE :UNION ALL

SELECT e.[ManagerID], e.[EmployeeID],

e.[FirstName], e.[LastName], e.[Title]

FROM [dbo].[MyEmployees] AS e

INNER JOIN [DirectReports] AS d

ON e.[ManagerID] = d.[EmployeeID]

Les deux instructions dans le corps de l'expression CTE sont jointes par uneclause UNION ALL.

4. La quatrième et dernière partie est donc la construction de l'instruction prin-cipale. Ici, il su�t de faire la mise en forme du résultat de l'expression CTE.L'instruction principale pourrait être nettement plus complexe avec notam-ment des jointures avec d'autres tables :SELECT [EmployeeID], [FirstName] + ' ' + [LastName] As

[EmployeeName], [Title], [ManagerID]

FROM [DirectReports]

ORDER BY [EmployeeID]

La composition de ces quatres parties donne la requête suivante :

4

II. Les requêtes récursives

WITH [DirectReports] ([ManagerID], [EmployeeID],

[FirstName], [LastName], [Title])

AS (

-- Point de départ

SELECT e.[ManagerID], e.[EmployeeID],

e.[FirstName], e.[LastName], e.[Title]

FROM [dbo].[MyEmployees]

AS e

WHERE [ManagerID] IS NULL UNION ALL

-- Définition de la récursivité

SELECT e.[ManagerID], e.[EmployeeID],

e.[FirstName], e.[LastName], e.[Title]

FROM [dbo].[MyEmployees] AS e

INNER JOIN [DirectReports] AS d

ON e.[ManagerID] = d.[EmployeeID] )

-- Table de sortie référençant la table CTE

SELECT [EmployeeID], [FirstName] + ' ' +

[LastName] As [EmployeeName], [Title],

[ManagerID] FROM [DirectReports] ORDER BY

[EmployeeID];

II.4.1 Sémantique d'exécution

La sémantique de l'exécution récursive est la suivante [2] :� 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 premierensemble de résultats de base (T0).

� Exécuter le(s) membre(s) récursif(s) avec Ti comme entrée et Ti+1 commesortie.

� 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 deT0 à Tn.

II.5 Avantages

L'utilisation des expressions de tables communes présente les avantages suivant[2] :

1. Une requête est quali�ée de récursive lorsqu'elle référence une expression CTErécursive. Le retour de données hiérarchiques est une utilisation courante derequêtes récursives, comme dans les cas suivants : a�chage des employés dansun organigramme ou de données dans un scénario de nomenclatures dans

5

II. Les requêtes récursives

lequel un produit parent possède un ou plusieurs composants qui eux-mêmespeuvent détenir des sous-composants ou être des composants d'autres parents.

2. Une expression CTE récursive peut sensiblement simpli�er 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 deSQL Server, une requête récursive requiert généralement l'utilisation de tablestemporaires, de curseurs et d'une logique pour contrôler le �ux des étapesrécursives.

3. Permet de traiter des problèmes complexes. À noter que dans une instruction,il est possible d'ajouter plusieurs expressions CTE ;

4. Une seule instruction est utilisée (aucun autre objet n'est nécessaire commedes vues ou des tables temporaires) ;

5. Les performances des instructions utilisant des expressions CTE sont géné-ralement les plus performantes. Ceci vient du fait que l'expression CTE estincluse dans le plan d'exécution avec le reste de l'instruction principale et estoptimisée par l'optimiseur statistique.

6

III

Conclusion

La récursivité est un besoin courant dans les développements d'application. Onpeut retrouver cela dans les TreeView, les menus en cascades, les organigrammes,etc.

La norme SQL :1999 propose des compléments syntaxiques pour piloter votreSQL récursif. Par exemple il est possible de naviguer dans les données DEPTHFIRST ou BREADTH FIRST (en profondeur ou en largeur en premier lieu) etaussi constituer une colonne contenant toutes les données des étapes intermédiairesdans un tableau de ligne (ARRAY of ROW) dont la taille doit être "su�sante"pour couvrir tous les cas de �gure.

7

Bibliographie

[1] R. Fabrice, �Les CTE ou la récursivité avec SQL Server 2005 Comment exécuterdes requêtes récursives en SQL�, Article, ilem SA.

[2] https ://technet.microsoft.com

8