Requêtes hiérarchiques dans Oracle

Qu'est ce qu'une requête hiérarchique?
La base de données Démo
Des requêtes hiérarchiques
Les colonnes virtuelles utilisées dans la requête hiérarchique

1. Qu'est ce qu'une requête hiérarchique?

Une requête hiérarchique (hierarchical query) est une requête SQL- "langage de requête structurée" ou "langage d'interrogation structurée" (SQL = Structured Query Language) qui manipule d'une base de données relationnelles hiérarchiques. Elle est un cas spéciale des requêtes récursives de jointure (recursive fixpoint queries).

2. La base de données Démo

Dans ce poste, je vais utiliser le Schéma (schema) de SCOTT afin d' illustrer des exemples. Dans le cas où vous n'avez pas ce schéma (shema), vous pouvez le créer en utilisant le Script suivant:

** Create Table **

-- Create table
create table EMP
(
 EMPNO    NUMBER(4) not null,
 ENAME    VARCHAR2(10),
 JOB      VARCHAR2(9),
 MGR      NUMBER(4),
 HIREDATE DATE,
 SAL      NUMBER(7,2),
 COMM     NUMBER(7,2),
 DEPTNO   NUMBER(2)
);
alter table EMP
 add constraint EMP_PK primary key (EMPNO);

alter table EMP
 add constraint EMP_E_FK foreign key (MGR)
 references EMP (EMPNO);

** Insert Data **

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, null, 10);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, null, 20);


insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, null, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450, null, 10);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, null, 20);


insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, null, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, null, 10);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);


insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);

commit;

Dans la table EMP:

EMPNO est le nom d'utilisateur- l'ID des employés (ainsi la clé primaire de la table)
MRG là ID du admistrateur directe de ces employés.

3. Des requêtes hiérarchiques

La syntaxe de la requête hiérarchique:

** Syntax **

Select Column1, Column2, ...

From <Table1>, <Table2>, ...

Where <Condition3>

Connect By <Codition2>

Start With <Condition1>

Des principes de l'opération

D'abord, l'instruction va tirer toutes les lignes dans la table qui correspondent des conditions dans la clause de start with (condition 1) comme la racine de l'arbre (root or level 1).
Ensuite, il est nécessaire de scanner la table entière afin de prendre des enregistrements suivants qu'ils sont satifait des conditions de la clause connect by (condition 2) (level 2 node- noeud à niveau 2). Il est similaire pour chaque noeud à niveau 2 va balayer la table entière en vue de sortir des enregistrements suivants qui répondent aux conditions dans la clause connect by (level 3 node). Il va continuer comme ça jusqu'à quand il n'y a plus d’enregistrement qui ne correspond aux demandes de la clause connect by. Le précédent nœud est donc le niveau de l'arbre.
Enfin, il faut vérifier la condition de la clause where (condition 3) pour tirer des enregistrements de la clause "select tree".

Par exemple, l'arborescence hiérarchique commence par ceux qui n'ont pas de manager (Mrg is null).

Select Level
     ,Emp.Empno
     ,Emp.Ename
     
     , -- Manager no
      Emp.Mgr Manager_No
     
     , -- Name of manager
     -- Prior: The Operator point to prior record.
      Prior Emp.Ename Manager_Name
     
From   Emp
Connect By Prior Emp.Empno = Emp.Mgr
Start  With Emp.Mgr Is Null;

Les résultats de la requête:

La modèle de l'arbre:

Suppression d'un noeud ou d'une branche d'arborescence

Basé sur le mécanisme opérationnel, vous pouvez résoudre le problème de comment déplacer n'importe quel noeud (node) ou une branche de l'abre.

Le déplacement d'un NODE:

Afin de déplacer un noeud quelconque: il vous faut que l'abre est successivement créé d'abord (connect by achevé) et mettre en oeuvre des conditions de la clause where en vue de déplacer le noeud.

Select Level
     ,Emp.Empno Emp_No
     ,Lpad(' '
          ,4 * (Level - 1)) || Emp.Ename Emp_Name
     ,Emp.Mgr Manager_No
     ,Prior Emp.Ename Manager_Name
From   Emp
Where  Emp.Ename != 'JONES'
Connect By Prior Emp.Empno = Emp.Mgr
Start  With Emp.Mgr Is Null;

Le déplacement une branche:

En vue de déplacer une branche quelconque, vous devez mettre en oeuvre des conditions dans le proccessus de la création de l'abre. C'est-à-dire de contenir la clause de connect by.

Select Level
     ,Emp.Empno Emp_No
     ,Lpad(' '
          ,4 * (Level - 1)) || Emp.Ename Emp_Name
     ,Emp.Mgr Manager_No
     ,Prior Emp.Ename Manager_Name
From   Emp
Connect By Prior Emp.Empno = Emp.Mgr
   And    Emp.Ename != 'JONES'
Start  With Emp.Mgr Is Null;

4. Les colonnes virtuelles utilisées dans la requête hiérarchique

Mot- clé/Tuple	Signification
Level	Indique la profondeur du noeud par rapport à la racine- node (depth level), la racine du node (root) a du level 1
Prior	Parle du tuple précédent et de le relier au tuple courant
Connect_By_Isleaf	Est la feuille d'un noeud ou pas? Si c'est la feuille d'un noeud, il rend 1, si non, il donne 0
CONNECT_BY_ROOT(Colonne)	Renvoie la colonne la valeur de la racine d'un noeud (level = 1)
NOCYCLE	Typle dans la clause connect by afin de déplacer des boucles illimitées. Par exemple, A gère B, B gère C, C gère A. Dans ce moment, connect by identifie que l'administrateur va être répéter infinitivement et le typle NOCYCLE va être utile dans ce cas.
CONNECT_BY_ISCYCLE	La colonne détermine si la valeur soit dupliquée ou pas? Si oui, la valeur donne le 1 et si non, elle rend à 0. Cette colonne doit être utilisée simultanément avec le typle NOCYCLE dans la clause de connect by
SYS_CONNECT_BY_PATH(Colonne, valeur séparée)	Crée une trajet de colonne de valeur de la racine de noeud du noeud courrante. Chaque noeud est séparé par " la valeur séparée".
ORDER SIBLINGS BY	Arrange des enfants de noeuds dans le même niveau (level)

Par exemple:

Select Level
     ,Emp.Empno Emp_No
     ,Lpad(' '
          ,4 * (Level - 1)) || Emp.Ename Emp_Name
     ,Emp.Mgr Mgr_No
     ,Prior Emp.Ename Mgr_Name
     ,Connect_By_Isleaf Is_Leaf -- 
     ,Connect_By_Root(Emp.Ename) Root_Mgr_Name -- Name of root employee
     ,Connect_By_Iscycle Iscycle -
     ,Sys_Connect_By_Path(Emp.Empno
                         ,':') Path -- The path
From   Emp
Connect By Nocycle Prior Emp.Empno = Emp.Mgr
Start  With Emp.Mgr Is Null
Order  Siblings By Emp.Ename;