CONNECT BY Oracle : Requêtes Hiérarchiques SQL Expliquées

CONNECT BY Oracle : Guide Complet des Requêtes Hiérarchiques

La clause CONNECT BY est l’une des fonctionnalités les plus puissantes et distinctives d’Oracle SQL. Elle permet de parcourir des données organisées en hiérarchie — comme un organigramme d’entreprise ou une arborescence de catégories — en une seule requête SQL. Dans ce cours, vous allez découvrir comment exploiter CONNECT BY pour naviguer efficacement dans des structures parent-enfant complexes, avec des exemples concrets et des bonnes pratiques Oracle.

Définition et utilisation de CONNECT BY

La clause CONNECT BY est une extension propriétaire d’Oracle SQL qui permet d’effectuer des requêtes hiérarchiques, c’est-à-dire des requêtes qui traversent des données structurées sous forme d’arbre. Elle est utilisée conjointement avec la clause START WITH pour définir le point de départ de la traversée.

Cette fonctionnalité est disponible dans Oracle Database depuis ses premières versions et constitue une alternative puissante aux expressions de table communes récursives (CTE récursives avec WITH ... CONNECT BY ou WITH RECURSIVE dans d’autres SGBD).

Cas d’usage en entreprise

Organigrammes RH : afficher la chaîne hiérarchique des employés et leurs managers.
Arborescences de produits : naviguer dans des catégories et sous-catégories d’un catalogue.
Structures de comptes comptables : consolider des données financières par niveau de plan comptable.
Nomenclatures industrielles : décomposer un produit fini en ses composants et sous-composants.
Arborescences de menus : construire dynamiquement des menus applicatifs hiérarchiques.

En résumé, dès qu’une table contient une relation parent-enfant auto-référencée (une colonne faisant référence à une autre ligne de la même table), CONNECT BY est l’outil idéal sous Oracle.

Syntaxe complète de CONNECT BY

Voici la syntaxe générale d’une requête hiérarchique Oracle avec CONNECT BY :

SELECT [colonnes], LEVEL, [pseudo-colonnes hiérarchiques]
FROM   nom_table
[WHERE condition_filtre]
START WITH condition_racine
CONNECT BY [NOCYCLE] PRIOR colonne_enfant = colonne_parent
[ORDER SIBLINGS BY colonne];

Explication des paramètres essentiels

Élément	Description
`START WITH`	Définit la ou les lignes racines (point de départ de la hiérarchie). Sans cette clause, toutes les lignes sont considérées comme racines potentielles.
`CONNECT BY`	Spécifie la relation entre une ligne parent et ses lignes enfants.
`PRIOR`	Opérateur obligatoire qui désigne la valeur de la ligne parente dans la relation. Il peut être placé des deux côtés de l’égalité selon le sens de traversée (descendant ou ascendant).
`LEVEL`	Pseudo-colonne Oracle renvoyant le niveau de profondeur dans la hiérarchie (1 pour la racine, 2 pour ses enfants, etc.).
`NOCYCLE`	Empêche Oracle de lever une erreur en cas de cycle détecté dans les données. Utilisé avec `CONNECT_BY_ISCYCLE`.
`ORDER SIBLINGS BY`	Trie les nœuds frères (même niveau, même parent) sans perturber l’ordre hiérarchique.
`SYS_CONNECT_BY_PATH`	Fonction retournant le chemin complet depuis la racine jusqu’au nœud courant.
`CONNECT_BY_ROOT`	Opérateur renvoyant la valeur de la colonne au niveau de la racine de l’arbre.

Exemples pratiques de CONNECT BY

Exemple 1 — Afficher l’organigramme hiérarchique des employés

Contexte métier : Une table EMPLOYES stocke les informations des salariés d’une entreprise. Chaque employé possède un identifiant (EMP_ID) et l’identifiant de son manager direct (MANAGER_ID). L’objectif est d’afficher l’intégralité de la hiérarchie en partant du PDG.

-- Création de la table et insertion des données de démonstration
CREATE TABLE employes (
    emp_id      NUMBER PRIMARY KEY,
    nom         VARCHAR2(50),
    poste       VARCHAR2(50),
    manager_id  NUMBER
);

INSERT INTO employes VALUES (1, 'Dupont',   'PDG',              NULL);
INSERT INTO employes VALUES (2, 'Martin',   'Directeur RH',     1);
INSERT INTO employes VALUES (3, 'Bernard',  'Directeur Financier', 1);
INSERT INTO employes VALUES (4, 'Lefebvre', 'Responsable Paie', 2);
INSERT INTO employes VALUES (5, 'Moreau',   'Comptable',        3);
INSERT INTO employes VALUES (6, 'Simon',    'Assistant RH',     2);
COMMIT;

-- Requête hiérarchique descendante avec CONNECT BY
SELECT
    LEVEL                                          AS niveau,
    LPAD(' ', (LEVEL - 1) * 4) || nom             AS nom_indenté,
    poste,
    SYS_CONNECT_BY_PATH(nom, ' > ')               AS chemin_hierarchique,
    CONNECT_BY_ROOT nom                            AS racine
FROM employes
START WITH manager_id IS NULL          -- On commence par le PDG (pas de manager)
CONNECT BY PRIOR emp_id = manager_id   -- Relation parent -> enfant (descendant)
ORDER SIBLINGS BY nom;                 -- Trier les frères alphabétiquement

Résultat attendu (simplifié) :

NIVEAU  NOM_INDENTÉ               POSTE                   CHEMIN_HIERARCHIQUE
------  ------------------------  ----------------------  ---------------------------------
1       Dupont                    PDG                     > Dupont
2           Bernard               Directeur Financier     > Dupont > Bernard
3               Moreau            Comptable               > Dupont > Bernard > Moreau
2           Martin                Directeur RH            > Dupont > Martin
3               Lefebvre          Responsable Paie        > Dupont > Martin > Lefebvre
3               Simon             Assistant RH            > Dupont > Martin > Simon

La fonction LPAD combinée à LEVEL crée une indentation visuelle très lisible pour représenter les niveaux hiérarchiques.

Exemple 2 — Remonter la hiérarchie d’un employé vers la racine

Contexte métier : Le service juridique souhaite connaître la chaîne hiérarchique complète au-dessus d’un employé donné (ex. : Simon, Assistant RH) pour valider une signature de document.

-- Requête hiérarchique ascendante avec CONNECT BY
-- On inverse PRIOR pour remonter de l'enfant vers le parent
SELECT
    LEVEL                          AS niveau_remontee,
    emp_id,
    nom,
    poste,
    manager_id
FROM employes
START WITH nom = 'Simon'            -- On part de l'employé ciblé
CONNECT BY PRIOR manager_id = emp_id  -- Relation ascendante (enfant -> parent)
ORDER BY LEVEL;

Résultat attendu :

NIVEAU_REMONTEE  EMP_ID  NOM      POSTE
---------------  ------  -------  ---------------
1                6       Simon    Assistant RH
2                2       Martin   Directeur RH
3                1       Dupont   PDG

En inversant la position de PRIOR, on remonte la hiérarchie au lieu de la descendre. C’est un cas typique pour les workflows de validation ou d’approbation.

Erreurs courantes avec CONNECT BY

Erreur ORA-01436 : Boucle dans les données (cycle)

Description : Oracle lève l’erreur ORA-01436: CONNECT BY loop in user data lorsqu’une ligne est à la fois ancêtre et descendante d’elle-même, créant un cycle infini dans la traversée.

Exemple de situation problématique : L’employé A est manager de B, et B est manager de A (données incohérentes en base).

Solution : Utilisez le mot-clé NOCYCLE dans la clause CONNECT BY, et exploitez la pseudo-colonne CONNECT_BY_ISCYCLE pour identifier les lignes cycliques afin de les corriger.

-- Utilisation de NOCYCLE pour gérer les cycles sans erreur
SELECT
    LEVEL,
    nom,
    CONNECT_BY_ISCYCLE AS est_cyclique  -- Vaut 1 si cette ligne crée un cycle
FROM employes
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR emp_id = manager_id;

Bonne pratique : Après correction des données, supprimez NOCYCLE pour que la contrainte d’intégrité reste active à la requête.

Résumé

Point clé	Détail
Objectif	Parcourir des structures de données hiérarchiques (arbre parent-enfant)
Clause obligatoire	`CONNECT BY PRIOR` avec définition de la relation parent-enfant
Point de départ	`START WITH` définit le ou les nœuds racines
Sens de traversée	Descendant : `PRIOR parent = enfant` / Ascendant : `PRIOR enfant = parent`
Pseudo-colonne de niveau	`LEVEL` indique la profondeur dans l’arbre
Chemin complet	`SYS_CONNECT_BY_PATH(col, séparateur)`
Gestion des cycles	`NOCYCLE` + `CONNECT_BY_ISCYCLE`
Tri sans casser la hiérarchie	`ORDER SIBLINGS BY`
Compatibilité	Oracle Database uniquement (fonctionnalité propriétaire)

2 Bonnes pratiques Oracle essentielles

Toujours indexer les colonnes de relation : Créez des index sur les colonnes utilisées dans START WITH et CONNECT BY (emp_id et manager_id dans nos exemples). Cela améliore considérablement les performances sur de grandes tables hiérarchiques.
Utiliser WHERE avec précaution : La clause WHERE filtre les lignes après la construction de l’arbre complet, ce qui peut produire des résultats inattendus (nœuds supprimés mais leurs enfants conservés). Pour filtrer des branches entières, préférez intégrer les conditions directement dans le CONNECT BY.

Aller plus loin

Pour approfondir vos compétences sur les requêtes avancées Oracle, consultez ces ressources complémentaires disponibles sur courssql.com :

Les CTE récursives avec la clause WITH en Oracle — Découvrez l’alternative SQL standard aux requêtes hiérarchiques et comprenez quand choisir l’une ou l’autre approche.
Les fonctions analytiques Oracle avec OVER et PARTITION BY — Maîtrisez les calculs avancés sur des partitions de données pour compléter vos analyses hiérarchiques.
La clause MODEL en Oracle SQL — Explorez cette fonctionnalité Oracle puissante pour les calculs inter-lignes et les simulations de feuilles de calcul directement en SQL.