Les requêtes hiérarchiques
Les requêtes hiérarchiques extraient des données provenant d’une structure arborescente. Les enregistrements d’une structure arborescente appartiennent, en général, à la même table et sont reliés entre eux par des associations auto-récursives à plusieurs niveaux.
L’exemple détaille un arbre qui comprend quatre niveaux décrits par la table EMPLOYES. Les enregistrements de cette table peuvent être assemblés dans une structure arborescente à l’aide des deux colonnes qui assurent l’association, celle qui désigne le supérieur hiérarchique REND_COMPTE et celle qui désigne le numéro de l’employé NO_EMPLOYE. L’employé Giroux qui est le président est ainsi considéré comme racine de l’arbre.
La syntaxe de mise en œuvre des requêtes hiérarchiques est :
SELECT * FROM NOM_TABLE
CONNECT BY [NOCYCLE]
[PRIOR] colonne01 = [PRIOR] colonne02 [AND ...]
[START WITH condition] ;
La requête suivante permet de visualiser les données de la branche gauche de l’arbre présenté dans l’image précédente.
SQL> SELECT NOM, NO_EMPLOYE, REND_COMPTE FROM EMPLOYES WHERE NOM
2 IN ( 'Splingart','Fuller','Giroux') OR REND_COMPTE = 23
3 ORDER BY REND_COMPTE,NO_EMPLOYE;
NOM NO_EMPLOYE REND_COMPTE
-------------- ---------- -----------
Splingart 23 14
Cleret 6 23
Poidatz 7 23
Capharsie 10 23
Chaussende 25 23
Hanriot 26 23
Jenny 39 23
Steiner 43 23
Maurousset 88 23
Montesinos 90 23
Marchand 96 23
Burst 98 23
Damas 99 23
Viry 105 23
Fuller 14 37
Giroux 37
Dans l’exemple suivant vous pouvez voir le parcours ascendant et descendant de l’arbre partant de l’employé Splingart. Il faut remarquer l’emplacement de l’argument « PRIOR » qui détermine l’enregistrement parent et ainsi le sens du parcours de l’arbre.
SQL> SELECT 'Ascendant' TYPE, NOM, NO_EMPLOYE, REND_COMPTE
2 FROM EMPLOYES CONNECT BY PRIOR NO_EMPLOYE = REND_COMPTE
3 START WITH NOM = 'Splingart'
4 UNION ALL
5 SELECT 'Descendant' TYPE, NOM, NO_EMPLOYE, REND_COMPTE
6 FROM EMPLOYES CONNECT BY NO_EMPLOYE = PRIOR REND_COMPTE
7 START WITH NOM = 'Splingart';
TYPE NOM NO_EMPLOYE REND_COMPTE
---------- -------------- ---------- -----------
Ascendant Splingart 23 14
Ascendant Cleret 6 23
Ascendant Poidatz 7 23
Ascendant Capharsie 10 23
Ascendant Chaussende 25 23
Ascendant Hanriot 26 23
Ascendant Jenny 39 23
Ascendant Steiner 43 23
Ascendant Maurousset 88 23
Ascendant Montesinos 90 23
Ascendant Marchand 96 23
Ascendant Burst 98 23
Ascendant Damas 99 23
Ascendant Viry 105 23
Descendant Splingart 23 14
Descendant Fuller 14 37
Descendant Giroux 37
Vous pouvez utiliser une pseudo-colonne « LEVEL » pour déterminer le niveau de positionnement dans l’arbre. Dans l’exemple suivant la requête ne comporte pas de condition « START WITH » et ainsi tous les enregistrements sont considérés comme la racine d’un arbre. Vous pouvez suivre les différents niveaux dans la première colonne, le niveau 1 est toujours la racine d’un arbre.
SQL> SELECT LEVEL N, NOM, NO_EMPLOYE, REND_COMPTE
2 FROM EMPLOYES
3 CONNECT BY PRIOR NO_EMPLOYE = REND_COMPTE;
N NOM NO_EMPLOYE REND_COMPTE
--- -------------- ---------- -----------
1 Cazade 74 11
...
1 Buchanan 24 14
2 Herve 72 24
2 Bodard 66 24
2 Urbaniak 63 24
...
1 Fuller 14 37
2 Devie 75 14
2 Guerdon 64 14
2 Callahan 30 14
2 Buchanan 24 14
3 Herve 72 24
...
1 Giroux 37
2 Fuller 14 37
3 Devie 75 14
3 Guerdon 64 14
3 Callahan 30 14
3 Buchanan 24 14
4 Herve 72 24
4 Bodard 66 24
...
3 Splingart 23 14
4 Maurousset 88 23
4 Steiner 43 23
4 Jenny 39 23
4 Hanriot 26 23
4 Chaussende 25 23
4 Capharsie 10 23
4 Poidatz 7 23
4 Cleret 6 23
...
Plusieurs fonctions peuvent être utilisées pour travailler avec les données des requêtes hiérarchiques comme suit :
SYS_CONNECT_BY_PATH
La fonction, spécifique aux requêtes hiérarchiques, permet de constituer une chaîne qui représente le parcours des enregistrements depuis la racine jusqu'à l’enregistrement courant.
SQL> SELECT LEVEL N, NOM, SYS_CONNECT_BY_PATH(NOM,'/') LN,
2 SYS_CONNECT_BY_PATH(NO_EMPLOYE,'->') LNO
3 FROM EMPLOYES
4 CONNECT BY PRIOR NO_EMPLOYE = REND_COMPTE
5 START WITH NOM = 'Fuller';
N NOM LN LNO
--- -------------- ------------------------------ -------------
1 Fuller /Fuller ->14
2 Splingart /Fuller/Splingart ->14->23
3 Cleret /Fuller/Splingart/Cleret ->14->23->6
3 Poidatz /Fuller/Splingart/Poidatz ->14->23->7
3 Capharsie /Fuller/Splingart/Capharsie ->14->23->10
3 Chaussende /Fuller/Splingart/Chaussende ->14->23->25
3 Hanriot /Fuller/Splingart/Hanriot ->14->23->26
3 Jenny /Fuller/Splingart/Jenny ->14->23->39
3 Steiner /Fuller/Splingart/Steiner ->14->23->43
3 Maurousset /Fuller/Splingart/Maurousset ->14->23->88
3 Montesinos /Fuller/Splingart/Montesinos ->14->23->90
3 Marchand /Fuller/Splingart/Marchand ->14->23->96
3 Burst /Fuller/Splingart/Burst ->14->23->98
3 Damas /Fuller/Splingart/Damas ->14->23->99
3 Viry /Fuller/Splingart/Viry ->14->23->105
2 Buchanan /Fuller/Buchanan ->14->24
3 Gardeil /Fuller/Buchanan/Gardeil ->14->24->9
...
CONNECT_BY_ROOT
La fonction, spécifique aux requêtes hiérarchiques, permet de retourner la valeur de la colonne argument pour l’enregistrement racine de l’arborescence.
SQL> SELECT LEVEL N, NOM, SYS_CONNECT_BY_PATH(NOM,'/') LN,
2 CONNECT_BY_ROOT NOM M, CONNECT_BY_ROOT NO_EMPLOYE NO
3 FROM EMPLOYES WHERE LEVEL > 2
4 CONNECT BY PRIOR NO_EMPLOYE = REND_COMPTE
5 START WITH NOM = 'Fuller';
N NOM LN M NO
--- ----------- ---------------------------- -------- ----
3 Cleret /Fuller/Splingart/Cleret Fuller 14
3 Poidatz /Fuller/Splingart/Poidatz Fuller 14
3 Capharsie /Fuller/Splingart/Capharsie Fuller 14
3 Chaussende /Fuller/Splingart/Chaussende Fuller 14
3 Hanriot /Fuller/Splingart/Hanriot Fuller 14
3 Jenny /Fuller/Splingart/Jenny Fuller 14
3 Steiner /Fuller/Splingart/Steiner Fuller 14
3 Maurousset /Fuller/Splingart/Maurousset Fuller 14
3 Montesinos /Fuller/Splingart/Montesinos Fuller 14
3 Marchand /Fuller/Splingart/Marchand Fuller 14
3 Burst /Fuller/Splingart/Burst Fuller 14
3 Damas /Fuller/Splingart/Damas Fuller 14
3 Viry /Fuller/Splingart/Viry Fuller 14
3 Gardeil /Fuller/Buchanan/Gardeil Fuller 14
3 Perny /Fuller/Buchanan/Perny Fuller 14
3 Gerard /Fuller/Buchanan/Gerard Fuller 14
3 Brunet /Fuller/Buchanan/Brunet Fuller 14
3 Jacquot /Fuller/Buchanan/Jacquot Fuller 14
3 Jeandel /Fuller/Buchanan/Jeandel Fuller 14
3 Urbaniak /Fuller/Buchanan/Urbaniak Fuller 14
3 Bodard /Fuller/Buchanan/Bodard Fuller 14
3 Herve /Fuller/Buchanan/Herve Fuller 14
3 Rollet /Fuller/Buchanan/Rollet Fuller 14
3 Silberreiss /Fuller/Buchanan/Silberreiss Fuller 14
3 Coutou /Fuller/Buchanan/Coutou Fuller 14
Dans l’exemple précédent vous pouvez voir l’utilisation de la pseudo-colonne LEVEL pour filtrer le retour de la requête. Le filtre n’impacte pas les résultats des fonctions spécifiques aux requêtes hiérarchiques.
CONNECT_BY_ISLEAF
La fonction, spécifique aux requêtes hiérarchiques, permet de vérifier que l’enregistrement est une feuille en retournant la valeur 1 et sinon 0. Dans le cas des requêtes hiérarchiques, une feuille est un enregistrement final qui n’est plus une racine pour d’autres enregistrements. Dans l’exemple suivant seuls les enregistrements qui ont un niveau égal à 3 sont des feuilles, les autres non.
SQL> SELECT LEVEL N, NOM, SYS_CONNECT_BY_PATH(NOM,'/') LN,
2 CONNECT_BY_ROOT NOM M, CONNECT_BY_ROOT NO_EMPLOYE NO,
3 CONNECT_BY_ISLEAF F
4 FROM EMPLOYES
5 CONNECT BY PRIOR NO_EMPLOYE = REND_COMPTE
6 START WITH NOM = 'Fuller';
N NOM LN M NO F
--- ----------- ---------------------------- -------- ---- ---
1 Fuller /Fuller Fuller 14 0
2 Splingart /Fuller/Splingart Fuller 14 0
3 Cleret /Fuller/Splingart/Cleret Fuller 14 1
3 Poidatz /Fuller/Splingart/Poidatz Fuller 14 1
...
2 Buchanan /Fuller/Buchanan Fuller 14 0
3 Gardeil /Fuller/Buchanan/Gardeil Fuller 14 1
3 Perny /Fuller/Buchanan/Perny Fuller 14 1
...
2 Callahan /Fuller/Callahan Fuller 14 1
2 Guerdon /Fuller/Guerdon Fuller 14 1
...
CONNECT_BY_ISCYCLE
La fonction, spécifique aux requêtes hiérarchiques, permet de vérifier que le parcours de l’arborescence n’est pas cyclique. Dans l’exemple suivant vous pouvez voir la l’erreur dans le cas d’un parcours d’arbre qui ne contrôle pas le caractère cyclique du parcours.
SQL> SELECT NOM, NO_EMPLOYE, REND_COMPTE
2 FROM EMPLOYES WHERE NOM IN ('Fuller','Splingart');
NOM NO_EMPLOYE REND_COMPTE
----------- ---------- -----------
Fuller 14 37
Splingart 23 14
2 ligne(s) sélectionnée(s).
SQL> UPDATE EMPLOYES SET REND_COMPTE = 23 WHERE NOM = 'Fuller';
1 ligne mise à jour.
SQL> SELECT LEVEL N, NOM, NO_EMPLOYE, REND_COMPTE
2 FROM EMPLOYES CONNECT BY PRIOR NO_EMPLOYE = REND_COMPTE
3 START WITH NOM = 'Fuller';
ERROR:
ORA-01436: boucle CONNECT BY dans les données utilisateur
SQL> SELECT LEVEL N, NOM, NO_EMPLOYE, REND_COMPTE
2 FROM EMPLOYES CONNECT BY NOCYCLE PRIOR NO_EMPLOYE = REND_COMPTE
3 START WITH NOM = 'Fuller';
N NOM NO_EMPLOYE REND_COMPTE
--- ----------- ---------- -----------
1 Fuller 14 23
2 Splingart 23 14
3 Cleret 6 23
...
SQL> SELECT LEVEL N, NOM, NO_EMPLOYE NO, REND_COMPTE RC,
2 SYS_CONNECT_BY_PATH(NOM,'/') LN, CONNECT_BY_ISCYCLE IC
3 FROM EMPLOYES CONNECT BY NOCYCLE PRIOR NO_EMPLOYE = REND_COMPTE
4 START WITH NOM = 'Fuller';
N NOM NO RC LN IC
--- ----------- ---- ---- ---------------------------- ---
1 Fuller 14 23 /Fuller 0
2 Splingart 23 14 /Fuller/Splingart 1
3 Cleret 6 23 /Fuller/Splingart/Cleret 0
3 Poidatz 7 23 /Fuller/Splingart/Poidatz 0
3 Capharsie 10 23 /Fuller/Splingart/Capharsie 0
3 Chaussende 25 23 /Fuller/Splingart/Chaussende 0
3 Hanriot 26 23 /Fuller/Splingart/Hanriot 0
...
ORDER SIBLINGS
L’utilisation des clauses « ORDER BY » ou « GROUP BY » est incompatible avec le parcours hiérarchique de l’arbre. Pour classer des enregistrements d’une hiérarchie, il faut utiliser la clause « ORDER SIBLINGS BY ». Cette clause trie les enregistrements en tenant compte du niveau dans l’arborescence de chaque enregistrement.
SQL> SELECT LEVEL N, LPAD(' ',3*LEVEL-3)||NOM M,
2 SYS_CONNECT_BY_PATH(NOM,'/') LN
3 FROM EMPLOYES CONNECT BY NOCYCLE PRIOR NO_EMPLOYE = REND_COMPTE
4 START WITH NOM = 'Fuller' ORDER SIBLINGS BY NOM;
N M LN
--- -------------------- ----------------------------
1 Fuller /Fuller
2 Buchanan /Fuller/Buchanan
3 Bodard /Fuller/Buchanan/Bodard
3 Brunet /Fuller/Buchanan/Brunet
3 Coutou /Fuller/Buchanan/Coutou
3 Gardeil /Fuller/Buchanan/Gardeil
3 Gerard /Fuller/Buchanan/Gerard
3 Herve /Fuller/Buchanan/Herve
3 Jacquot /Fuller/Buchanan/Jacquot
3 Jeandel /Fuller/Buchanan/Jeandel
3 Perny /Fuller/Buchanan/Perny
3 Rollet /Fuller/Buchanan/Rollet
3 Silberreiss /Fuller/Buchanan/Silberreiss
3 Urbaniak /Fuller/Buchanan/Urbaniak
2 Callahan /Fuller/Callahan
2 Devie /Fuller/Devie
2 Guerdon /Fuller/Guerdon
2 Lampis /Fuller/Lampis
2 Pouetre /Fuller/Pouetre
2 Splingart /Fuller/Splingart
3 Burst /Fuller/Splingart/Burst
3 Capharsie /Fuller/Splingart/Capharsie
3 Chaussende /Fuller/Splingart/Chaussende
3 Cleret /Fuller/Splingart/Cleret
3 Damas /Fuller/Splingart/Damas
3 Hanriot /Fuller/Splingart/Hanriot
3 Jenny /Fuller/Splingart/Jenny
3 Marchand /Fuller/Splingart/Marchand
3 Maurousset /Fuller/Splingart/Maurousset
3 Montesinos /Fuller/Splingart/Montesinos
3 Poidatz /Fuller/Splingart/Poidatz
3 Steiner /Fuller/Splingart/Steiner
3 Viry /Fuller/Splingart/Viry