Oracle - Les requêtes hiérarchiques

---

Cet article se base sur des tests et recherches réalisés dans la version 8.1.5 d'Oracle.

Introduction

Tout au long de cet article, nous allons nous intéresser à une structure de données permettant de créer un Forum de discussion, matérialisé par la table suivante :

===
SQL> create table messages (
  2  id number,
  3  id_parent number,
  4  titre varchar2(50),
  5  auteur varchar2(30),
  6  dateheure date,
  7  texte long);

Table créée.
SQL> alter table messages add primary key (id);

Table modifiée.
SQL> alter table messages modify dateheure default sysdate;

Table modifiée.
===

La colonne id_parent contient NULL si le message est le premier message d'un fil de discussion, sinon elle contient l'id du message auquel le message répond. La colonne dateheure a pour valeur par défaut la date système du serveur de base de données. On pourra donc se permettre de l'ignorer lors de l'insertion des messages.

Nous n'allons pas nous attarder sur l'enregistrement des messages dans la table (ordre SQL "insert"). Nous n'allons nous intéresser qu'à la façon dont les données sont lues dans la table, donc aux ordres SQL "select" qu'il faudra exécuter pour obtenir les résultats escomptés.

Voici un exemple de contenu de la table messages :

insert into messages (id, id_parent, titre, auteur, texte)
values (1, NULL, 'Combien d''oeufs dans la pate à crêpes ?',
'John', 'Quelqu''un sait-il combien il faut d''oeufs ?');

insert into messages (id, id_parent, titre, auteur, texte)
values (2, NULL, 'Sondage : votre marque de lait préférée',
'John', 'Dites-moi quelle est votre marque de lait préférée ?');

insert into messages (id, id_parent, titre, auteur, texte)
values (3,1, 'Re : Combien d''oeufs dans la pate à crêpes ?',
'Martine', 'Je crois que c''est 2 par personne non ?');

insert into messages (id, id_parent, titre, auteur, texte)
values (4,3, 'C''est sûrement ça', 'John', 'Tu dois avoir raison, merci !');

insert into messages (id, id_parent, titre, auteur, texte)
values (5,2, 'Re : Sondage : votre marque de lait préférée',
'Fifi', 'Moi c''est Lait Blanc');

insert into messages (id, id_parent, titre, auteur, texte)
values (6,2, 'Re : Sondage : votre marque de lait préférée',
'Petrus', 'SuperMilk');

insert into messages (id, id_parent, titre, auteur, texte)
values (7,4, 'Re : C''est sûrement ça', 'Martine', 'De rien ;o)');

insert into messages (id, id_parent, titre, auteur, texte)
values (8,2, 'Re : Sondage : votre marque de lait préférée',
'Fifi', 'Au fait pourquoi ce sondage ?');

insert into messages (id, id_parent, titre, auteur, texte)
values (9,2, 'Re : Sondage : votre marque de lait préférée',
'Enrico', 'Le lait de chèvre');

insert into messages (id, id_parent, titre, auteur, texte)
values (10,8, 'Re : Sondage : votre marque de lait préférée',
'John', 'Je suis statisticien de formation et j''ai rien d''autre à faire');

insert into messages (id, id_parent, titre, auteur, texte)
values (11, NULL, 'Qui a vu le match de volley hier soir ?',
'Petrus', 'Si qq''un connaît le score, merci de nous le donner...');

insert into messages (id, id_parent, titre, auteur, texte)
values (12,10, 'Re : Sondage : votre marque de lait préférée', 'Fifi', 'Ok');

insert into messages (id, id_parent, titre, auteur, texte)
values (13,3, 'Re : Combien d''oeufs dans la pate à crêpes ?',
'Fifi', 'Oui c''est 2 par personne');

Ce qui nous donne le contenu suivant (par ordre chronologique de création des messages) :

 ID	ID_P	TITRE				 AUTEUR	HEURE
------	--------------------------------------------- ------------------
1	Combien d'oeufs dans la pate à crêpes ?		John	16:58:20
2	Sondage : votre marque de lait préférée		John	16:58:37
3 1	Re : Combien d'oeufs dans la pate à crêpes ?   Martine	16:58:51
4 3	C'est sûrement ça							  John	16:59:00
5 2	Re : Sondage : votre marque de lait préférée   Fifi	16:59:08
6 2	Re : Sondage : votre marque de lait préférée   Petrus	16:59:14
7 4	Re : C'est sûrement ça						 Martine	16:59:20
8 2	Re : Sondage : votre marque de lait préférée   Fifi	16:59:26
9 2	Re : Sondage : votre marque de lait préférée   Enrico	16:59:31
10 8	Re : Sondage : votre marque de lait préférée   John	16:59:45
11	Qui a vu le match de volley hier soir ?		Petrus	16:59:51
12 10	Re : Sondage : votre marque de lait préférée   Fifi	16:59:59
13 3	Re : Combien d'oeufs dans la pate à crêpes ?   Fifi	17:00:03

Ainsi : - les deux premiers messages sont des débuts de fil de discussion - le message ID=3 répond au message ID=1 car son ID_PARENT=1 - le message ID=13 répond au message ID=3 car son ID_PARENT=3 - etc...

Notre but est donc d'obtenir une hiérarchie de la forme :

ID=1
|-ID=3
| |-ID=4
| | |-ID=7
| |-ID=13
ID=2
|-ID=5
|-ID=6
|-ID=8
| |-ID=10
| | |-ID=12
|-ID=9
ID=11

Le lien entre un message et son parent est bien sûr fait avec les colonnes ID et ID_PARENT. Voici la syntaxe de l'ordre SQL permettant de récupérer la hiérarchie souhaitée :

===
SQL> select id, id_parent, titre
  2  from messages
  3  start with id_parent is null
  4  connect by id_parent = prior id;

	   ID ID_PARENT TITRE
--------- --------- --------------------------------------------------
		1		   Combien d'oeufs dans la pate à crêpes ?
		3		 1 Re : Combien d'oeufs dans la pate à crêpes ?
		4		 3 C'est sûrement ça
		7		 4 Re : C'est sûrement ça
	   13		 3 Re : Combien d'oeufs dans la pate à crêpes ?
		2		   Sondage : votre marque de lait préférée
		5		 2 Re : Sondage : votre marque de lait préférée
		6		 2 Re : Sondage : votre marque de lait préférée
		8		 2 Re : Sondage : votre marque de lait préférée
	   10		 8 Re : Sondage : votre marque de lait préférée
	   12		10 Re : Sondage : votre marque de lait préférée
		9		 2 Re : Sondage : votre marque de lait préférée
	   11		   Qui a vu le match de volley hier soir ?
13 ligne(s) sélectionnée(s).
===

Notre objectif est atteint : nous avons les messages dans le bon ordre. Une requête hiérarchique permet donc de trier des enregistrements en utilisant des notions d'arborescence inter-enregistrement.

La pseudo-colonne Level

Dans une hiérarchie, on peut avoir un élément A qui a pour enfant un élément B, qui lui-même a pour enfant un élément C, etc... On peut alors dire que l'élément C est au niveau n+2 par rapport au niveau n de l'élément A. Afin de mieux voir la profondeur de hiérarchie, nous allons utiliser la pseudo-colonne LEVEL, qui permet de savoir à quel niveau hiérarchique on se trouve :

===
SQL> select level, titre, auteur
  2  from  messages
  3  start with id_parent is null
  4  connect by id_parent = prior id;

	LEVEL TITRE											  AUTEUR
--------- -------------------------------------------------- ---------
		1 Combien d'oeufs dans la pate à crêpes ?			 John
		2 Re : Combien d'oeufs dans la pate à crêpes ?		Martine
		3 C'est sûrement ça								  John
		4 Re : C'est sûrement ça							 Martine
		3 Re : Combien d'oeufs dans la pate à crêpes ?		Fifi
		1 Sondage : votre marque de lait préférée			John
		2 Re : Sondage : votre marque de lait préférée	   Fifi
		2 Re : Sondage : votre marque de lait préférée	   Petrus
		2 Re : Sondage : votre marque de lait préférée	   Fifi
		3 Re : Sondage : votre marque de lait préférée	   John
		4 Re : Sondage : votre marque de lait préférée	   Fifi
		2 Re : Sondage : votre marque de lait préférée	   Enrico
		1 Qui a vu le match de volley hier soir ?			Petrus
13 ligne(s) sélectionnée(s).
===

Pour plus de clarté, nous allons faire précéder chaque titre d'un nombre d'espaces égal au level qui lui correspond. Cela génèrera une indentation du plus bel effet !

===
SQL>
  1  select id, id_parent, rpad(' ', level-1) || titre as titre, auteur
  2  from messages
  3  start with id_parent is null
  4  connect by id_parent = prior id;

 ID ID_PARENT	TITRE							AUTEUR
--- ---------	------------------------------------------------------	----------
  1		Combien d'oeufs dans la pate à crêpes ?			John
  3		 1	 Re : Combien d'oeufs dans la pate à crêpes ?		Martine
  4		 3	  C'est sûrement ça					John
  7		 4	   Re : C'est sûrement ça				Martine
 13		 3	  Re : Combien d'oeufs dans la pate à crêpes ?		Fifi
  2		Sondage : votre marque de lait préférée			John
  5		 2	 Re : Sondage : votre marque de lait préférée		Fifi
  6		 2	 Re : Sondage : votre marque de lait préférée		Petrus
  8		 2	 Re : Sondage : votre marque de lait préférée		Fifi
 10		 8	  Re : Sondage : votre marque de lait préférée		John
 12		10	   Re : Sondage : votre marque de lait préférée		Fifi
  9		 2	 Re : Sondage : votre marque de lait préférée		Enrico
 11		Qui a vu le match de volley hier soir ?			Petrus


13 ligne(s) sélectionnée(s).
===

Nous venons donc de voir à quoi peut servir une requête hiérarchique. Parmis les applications possible de cet outil puissant : - les forums avec fil de discussion (notre exemple) - hiérarchie d'un groupe de personnes (chefs de service - employés) - gestion de catégories et sous-catégories pour organiser des informations - ...

Quelques précisions sur la syntaxe des requêtes hiérarchiques

La clause START WITH permet d'indiquer la condition qui détermine quels enregistrements sont au niveau 1 (aucun enregistrement au-dessus d'eux dans la hiérarchie). Si on n'avait voulu lister que les fils de discussion initiés par John, avec leurs réponses, voici la requête qu'il aurait fallu exécuter :

===
SQL>
  1  select id, id_parent, rpad(' ', level-1) || titre as titre, auteur
  2  from messages
  3  start with id_parent is null and auteur = 'John'
  4* connect by id_parent = prior id
 ID ID_PARENT	TITRE							AUTEUR
--- ---------	------------------------------------------------------	----------
  1		Combien d'oeufs dans la pate à crêpes ?			John
  3		 1	 Re : Combien d'oeufs dans la pate à crêpes ?		Martine
  4		 3	  C'est sûrement ça					John
  7		 4	   Re : C'est sûrement ça				Martine
 13		 3	  Re : Combien d'oeufs dans la pate à crêpes ?		Fifi
  2		Sondage : votre marque de lait préférée			John
  5		 2	 Re : Sondage : votre marque de lait préférée		Fifi
  6		 2	 Re : Sondage : votre marque de lait préférée		Petrus
  8		 2	 Re : Sondage : votre marque de lait préférée		Fifi
 10		 8	  Re : Sondage : votre marque de lait préférée		John
 12		10	   Re : Sondage : votre marque de lait préférée		Fifi
  9		 2	 Re : Sondage : votre marque de lait préférée		Enrico
12 ligne(s) sélectionnée(s).
===

Le 13ème et dernier message n'a pas été sélectionné car il ne fait pas partie d'un fil de discussion initié par John. Les requêtes hiérarchiques sont donc non seulement un moyen de trier les enregistrements, mais aussi un moyen de sélectionner des enregistrements (même sans utilisation de la clause where).

La clause CONNECT BY permet d'indiquer la condition qui lie un enregistrement à son enregistrement père. "connect by id_parent = prior id" doit se lire "la relation père-fils entre deux enregistrements est définie par le fait que la colonne id_parent de l'enregistrement fils est égale à la colonne id de l'enregistrement père" (c'est le mot clé PRIOR qui indique que l'on fait référence au père).

Article écrit par Tittom

Trucs & astuces pertinents trouvés dans la base de connaissances

22/09 18h56	Connexion à une base Oracle en php (Oracle)
Plus d'astuces sur « Oracle »

Discussions pertinentes trouvées dans le forum

20/06 17h48	Import base de donnees oracle	Développement	17/02 09h48->abdou	9
31/05 16h27	[SQL][ORACLE] Liste des champs	Développement	18/09 13h00->yannick	8
09/02 14h57	[ORACLE, UML] modéliser une base préexistante	Développement	05/12 01h55->artaud	8
24/03 13h41	personal oracle 8	Logiciels/Pilotes	26/03 10h17->yanban	7
07/04 02h10	Pb installation oracle 8i sous windows XP	Windows	22/01 15h32->loulou	7
02/12 15h59	[oracle] comparaison d'heures	Développement	09/02 14h03->Gilles M	5
08/03 12h04	[oracle bd] document.form.submit()	Développement	12/03 11h36->renisaac	5
08/02 11h08	[Access] import données oracle	Logiciels/Pilotes	10/02 18h27->random	4
11/11 17h06	[oracle] developer	Développement	11/11 18h16->fagy	4
15/02 20h08	Question dans Bases de Dnnées ORACLE	Développement	23/02 12h21->Alex75	4
Discussion fermée Problème résolu		Plus de discussions sur « Oracle »

Ce document intitulé « Oracle - Les requêtes hiérarchiques » issu de l'encyclopédie informatique Comment Ça Marche (www.commentcamarche.net) est mis à disposition sous les termes de la licence Creative Commons. Vous pouvez copier, modifier des copies de cette page, dans les conditions fixées par la licence, tant que cette note apparaît clairement.