Pour cet exemple, j'ai choisit une table contenant une typologie de véhicules :
-- creation de la table
CREATE TABLE T_VEHICULE (VHC_ID INTEGER NOT NULL PRIMARY KEY, VHC_ID_FATHER INTEGER
FOREIGN KEY REFERENCES T_VEHICULE (VHC_ID), VHC_NAME VARCHAR(16))
-- population
4 – Un premier exemple, une hiérarchie basique
![4 – Un premier exemple, une hiérarchie basique 4 – Un premier exemple, une hiérarchie basique](https://www.itpro.fr/wp-content/uploads/2015/09/19a63393e526dc25f232727bfa83c84e.jpg)
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…
Téléchargez cette ressource
![Comment lutter contre le Phishing ?](https://www.itpro.fr/wp-content/uploads/2024/07/Special-Report-Eviden-Securite-Phishing-Juillet-2024.png)
Comment lutter contre le Phishing ?
Dans un environnement cyber en constante mutation, le phishing évolue vers des attaques toujours plus sophistiquées combinant IA, automatisation et industrialisation. Une réalité complexe qui exige des mesures de sécurité avancées et repensées au-delà de l’authentification multifacteur. Découvrez les réponses technologiques préconisées par les experts Eviden et les perspectives associées à leur mise en œuvre.
Les articles les plus consultés
Les plus consultés sur iTPro.fr
- IA : les PME devraient adopter des outils NoCode appropriés
- Guide des certifications Microsoft
- Transition vers le Cloud : l’approche stratégique pour répondre aux exigences de cybersécurité NIS 2
- Vers une meilleure qualité de données : l’IA vient en renfort
- Stimuler l’innovation durable grâce à la transformation numérique et aux principes ESG
![Revue Smart DSI](https://www.itpro.fr/wp-content/uploads/2024/07/SMART-DSI-Numero-34-Juin-2024.jpg)