4 – Un premier exemple, une hiérarchie basique

INSERT INTO T_VEHICULE VALUES (1, NULL, ‘ALL’)
INSERT INTO T_VEHICULE VALUES (2, 1, ‘SEA’)
INSERT INTO T_VEHICULE VALUES (3, 1, ‘EARTH’)
INSERT INTO T_VEHICULE VALUES (4, 1, ‘AIR’)
INSERT INTO T_VEHICULE VALUES (5, 2, ‘SUBMARINE’)
INSERT INTO T_VEHICULE VALUES (6, 2, ‘BOAT’)
INSERT INTO T_VEHICULE VALUES (7, 3, ‘CAR’)
INSERT INTO T_VEHICULE VALUES (8, 3, ‘TWO WHEELES’)
INSERT INTO T_VEHICULE VALUES (9, 3, ‘TRUCK’)
INSERT INTO T_VEHICULE VALUES (10, 4, ‘ROCKET’)
INSERT INTO T_VEHICULE VALUES (11, 4, ‘PLANE’)
INSERT INTO T_VEHICULE VALUES (12, 8, ‘MOTORCYCLE’)
INSERT INTO T_VEHICULE VALUES (13, 8, ‘BYCYCLE’)

Habituellement une hiérarchie se représente en utilisant une auto référence, c’est à dire à l’aide d’une clef étrangère provenant de la clef même de la table. Les données de cette table peuvent se voir de la sorte :

ALL
|–SEA
|     |–SUBMARINE
|     |–BOAT
|–EARTH
|     |–CAR
|     |–TWO WHEELES
|     |     |–MOTORCYCLE
|     |     |–BYCYCLE
|     |–TRUCK
|–AIR
    |–ROCKET
    |–PLANE

Commençons maintenant à exprimer une première interrogation et demandons-nous d’où vient la moto ? En d’autres termes, nous recherchons les ancêtres de MOTORCYCLE. Nous devons donc partir de la ligne qui contient la moto :

SELECT VHC_NAME, VHC_ID_FATHER FROM T_VEHICULE WHERE VHC_NAME = ‘MOTORCYCLE’

Nous devons récupérer la valeur de l’identifiant père (VHC_ID_FATHER) pour aller à l’étape suivante. La seconde requête, qui avance d’un pas dans l’arbre, doit être écrit comme suit :

SELECT VHC_NAME, VHC_ID_FATHER FROM T_VEHICULE

Comme on le voit, il n’y a aucune différence entre les deux requêtes à l’exception du filtre WHERE qui sert de point de départ. Souvenez-vous simplement que vous devez introduire un UNION ALL entre les deux requêtes afin d’assurer la liaison entre le départ et le pas suivant :

SELECT VHC_NAME, VHC_ID_FATHER FROM T_VEHICULE
WHERE VHC_NAME = ‘MOTORCYCLE’ UNION ALL SELECT VHC_NAME, VHC_ID_FATHER FROM T_VEHICULE

Plaçons maintenant cette requête composite dans code SQL qui bâtie l’expression de la CTE :

WITH
tree (data, id) AS (SELECT VHC_NAME, VHC_ID_FATHER FROM T_VEHICULE
WHERE VHC_NAME = ‘MOTORCYCLE’ UNION ALL
SELECT VHC_NAME, VHC_ID_FATHER FROM T_VEHICULE)

Nous sommes maintenant près de la récursion. La dernière étape de notre travail consiste à générer le cycle de récursion. Cela se fait en utilisant le nom de la CTE (ici "tree") à l’intérieur même de l’expression. Dans notre cas nous devons joindre à la seconde requête SELECT de l’expression CTE, la "table" tree à la table T_VEHICULE et assurer le chaînage par une jointure entre les identifiants : tree.id = (second query).VHC_ID. Cela se réalise ainsi :

WITH
tree (data, id)
AS (SELECT VHC_NAME, VHC_ID_FATHER
FROM T_VEHICULE
WHERE VHC_NAME = ‘MOTORCYCLE’
UNION ALL
SELECT VHC_NAME, VHC_ID_FATHER
FROM T_VEHICULE V
INNER JOIN tree t
ON t.id = V.VHC_ID)
SELECT * FROM tree

Il n’y a plus rien à faire que d’exprimer une requête finale, la plus simple possible, basée sur la CTE afin d’afficher les données :

data                                 id
 —————-                   ———–
MOTORCYCLE             8
TWO WHEELES           3
EARTH                            1
ALL                                   NULL

 Regardons maintenant la façon dont nous avons lié les tables et la CTE d’une façon pseudo graphique :

correlation
 ___________________________________
|                                                                                  |
v                                                                                 |
WITH tree (data, id)                                                |
AS (SELECT VHC_NAME, VHC_ID_FATHER |
FROM T_VEHICULE                                              |
WHERE VHC_NAME = ‘MOTORCYCLE’            |
UNION ALL                                                              |
SELECT VHC_NAME, VHC_ID_FATHER         |
FROM T_VEHICULE V                                          |
INNER JOIN tree t <—————————-——-
ON t.id = V.VHC_ID) SELECT * FROM tree

La question que l’on peut se poser est la suivante : qu’est ce qui a stoppé le processus de récursion ? C’est simplement le fait que plus rien ne peut être chaîné dès que l’identifiant atteint le marqueur NULL, ce qui est le cas dans notre exemple lorsque nous atteignons la colonne ou VHC_NAME = "ALL". Maintenant vous avez la technique. Veuillez simplement noter que pour une raison que je ne m’explique pas encore, MS SQL Server n’accepte pas la présence du mot clef RECURSIVE suivant le mot clef WITH, alors que la norme le préconise…