CONNECT BY PRIOR : Requête hiérarchique

SQL> CREATE TABLE articles(
  2     categorie VARCHAR2(30),
  3     categorie_sup VARCHAR2(30)
  4  );

Table created.
 

SQL> INSERT INTO articles VALUES('articles',null);

1 row created.

SQL> INSERT INTO articles VALUES('véhicules','articles');

1 row created.

SQL> INSERT INTO articles VALUES('prêt-à-porter','articles');

1 row created.

SQL> INSERT INTO articles VALUES('moto','véhicules');

1 row created.

SQL> INSERT INTO articles VALUES('automobile','véhicules');

1 row created.

SQL> INSERT INTO articles VALUES('fazer fz6','moto');

1 row created.

SQL> INSERT INTO articles VALUES('honda hornet 600','moto');

1 row created.

SQL> INSERT INTO articles VALUES('kawazaki z1000','moto');

1 row created.

SQL> INSERT INTO articles VALUES('peugeot 306','automobile');

1 row created.

SQL> INSERT INTO articles VALUES('chaussures','prêt-à-porter');

1 row created.

SQL> SELECT lpad(' ',2*level)||categorie as "ARTICLES"
  2  FROM articles
  3  CONNECT BY PRIOR categorie=categorie_sup
  4  START WITH categorie = 'articles'
  5  ;

ARTICLES
----------------------------
  articles
    prêt-à-porter
      chaussures
    véhicules
      automobile
        peugeot 306
      moto
        fazer fz6
        honda hornet 600
        kawazaki z1000

10 rows selected.

La fonction Oracle SYS_CONNECT_BY_PATH permet d'afficher la concaténation du chemin hiérarchique pour chaque élément listé. Bien pratique pour visualiser le contenu de la table..

  

SQL> SELECT SYS_CONNECT_BY_PATH(categorie, ' * ')
  2  FROM articles
  3  CONNECT BY PRIOR categorie=categorie_sup
  4  START WITH categorie = 'articles'
  5  ;

SYS_CONNECT_BY_PATH(CATEGORIE,'*')
--------------------------------------------------
 * articles
 * articles * prêt-à-porter
 * articles * prêt-à-porter * chaussures
 * articles * véhicules
 * articles * véhicules * automobile
 * articles * véhicules * automobile * peugeot 306
 * articles * véhicules * moto
 * articles * véhicules * moto * fazer fz6
 * articles * véhicules * moto * honda hornet 600
 * articles * véhicules * moto * kawazaki z1000

10 rows selected.