CONNECT BY, PRIOR and START WITH
La classica relazione parent-child prevede due tabelle, una padre e uno figlio in join tra loro, invece nel caso di HIERACHICAL DATA abbiamo una sola tabella (o vista) in self-join ed è possibile avere una relazione parent–child–grandchild–great-grandchild e così via.
Vediamo un esempio di una tale relazione gerarchica.
Consideriamo la seguente tabella ORGANIGRAMMA.
S i n t a s s i
SELECT LEVEL, EMPLOYEE_ID, TITLE
FROM ORGANIGRAMMA
START WITH EMPLOYEE_ID = 1
CONNECT BY REPORTS_TO = PRIOR EMPLOYEE_ID;
L'istruzione START WITH definisce la radice (root node); la colonna specificata deve identificare in maniera univoca un record pertanto conviene che sulla colonna sia definita una primary key.
CONNECT BY definisce la relazione di self-join e cioè individuano le colonne in join.
La parola chiave PRIOR può essere posta davanti ad una delle due colonne e definisce come è attraversato l'albero:
CONNECT BY REPORTS_TO = PRIOR EMPLOYEE_ID l'albero è attraversato dalla root (individuato da START WITH) alle leaf-node
CONNECT BY PRIOR REPORTS_TO = EMPLOYEE_ID l'albero è attraversato dalle foglie (individuate da START WITH) alla radice.
Esempio
SELECT level, EMPLOYEE_ID, TITLE
FROM ORGANIGRAMMA
START WITH EMPLOYEE_ID = 9
CONNECT BY prior REPORTS_TO = EMPLOYEE_ID;
LEVEL è una pseudo-colonna che restituisce i livelli della gerarchia e può essere utilizzata per facilitare la visualizzazione dei livelli della gerarchia.
SELECT LEVEL, EMPLOYEE_ID, LPAD(' ', LEVEL*2) || TITLE
FROM ORGANIGRAMMA
START WITH EMPLOYEE_ID = 1
CONNECT BY REPORTS_TO = PRIOR EMPLOYEE_ID;
Attenzione all'uso della clausola Order by ; per ordinare le righe all'interno di ogni livello e non tra livelli usare la clausola ORDER SIBLINGS BY.
La funzione SYS_CONNECT_BY_PATH permette di formattare la colonna di output e visualizzare il path completo dei nodi a partire dalla radice; ha due parametri, uno è la colonna da visualizzare e il secondo un separatore di caratteri.
Ad esempio SYS_CONNECT_BY_PATH(colonnai, '/')
L'operatore CONNECT_BY_ROOT visualizza la root di una qualsiasi riga.
SELECT LEVEL, EMPLOYEE_ID, LPAD(' ', LEVEL*2) || TITLE, SYS_CONNECT_BY_PATH(TITLE, '\') , CONNECT_BY_ROOT TITLE
FROM ORGANIGRAMMA
START WITH EMPLOYEE_ID =1
CONNECT BY REPORTS_TO = PRIOR EMPLOYEE_ID
ORDER SIBLINGS BY TITLE;
Escludere un ramo dell'albero
La clausola CONNECT BY permette anche di selezionare solo alcuni rami (branches) dell'albero aggiungendo la condizione che la colonna emplyee_id sia diversa dal nodo che non si vuole visualizzare.
Esempio: escludere il ramo che inizia con il nodo SENIOR VICE PRESIDENT
SELECT LEVEL, EMPLOYEE_ID, LPAD(' ', LEVEL*2) || TITLE
FROM ORGANIGRAMMA
START WITH EMPLOYEE_ID =1
CONNECT BY REPORTS_TO = PRIOR EMPLOYEE_ID AND EMPLOYEE_ID <> 3;
oppure
CONNECT BY REPORTS_TO = prior EMPLOYEE_ID and TITLE <> 'SENIOR VICE PRESIDENT';
La clausola WHERE può essere inserita solo prima della clausola START WITH e CONNCET BY. La where può essere usata per escludere un intero ramo ma è formalmente più corretto utilizzare la clausola CONNECT BY.
Le clausole START WITH e CONNECT BY possono apparire in qualsiasi ordine nella Select.
Vediamo un esempio di una tale relazione gerarchica.
SELECT LEVEL, EMPLOYEE_ID, TITLE
FROM ORGANIGRAMMA
START WITH EMPLOYEE_ID = 1
CONNECT BY REPORTS_TO = PRIOR EMPLOYEE_ID;
L'istruzione START WITH definisce la radice (root node); la colonna specificata deve identificare in maniera univoca un record pertanto conviene che sulla colonna sia definita una primary key.
CONNECT BY definisce la relazione di self-join e cioè individuano le colonne in join.
La parola chiave PRIOR può essere posta davanti ad una delle due colonne e definisce come è attraversato l'albero:
CONNECT BY REPORTS_TO = PRIOR EMPLOYEE_ID l'albero è attraversato dalla root (individuato da START WITH) alle leaf-node
CONNECT BY PRIOR REPORTS_TO = EMPLOYEE_ID l'albero è attraversato dalle foglie (individuate da START WITH) alla radice.
Esempio
SELECT level, EMPLOYEE_ID, TITLE
FROM ORGANIGRAMMA
START WITH EMPLOYEE_ID = 9
CONNECT BY prior REPORTS_TO = EMPLOYEE_ID;
LEVEL è una pseudo-colonna che restituisce i livelli della gerarchia e può essere utilizzata per facilitare la visualizzazione dei livelli della gerarchia.
SELECT LEVEL, EMPLOYEE_ID, LPAD(' ', LEVEL*2) || TITLE
FROM ORGANIGRAMMA
START WITH EMPLOYEE_ID = 1
CONNECT BY REPORTS_TO = PRIOR EMPLOYEE_ID;
Attenzione all'uso della clausola Order by ; per ordinare le righe all'interno di ogni livello e non tra livelli usare la clausola ORDER SIBLINGS BY.
La funzione SYS_CONNECT_BY_PATH permette di formattare la colonna di output e visualizzare il path completo dei nodi a partire dalla radice; ha due parametri, uno è la colonna da visualizzare e il secondo un separatore di caratteri.
Ad esempio SYS_CONNECT_BY_PATH(colonnai, '/')
L'operatore CONNECT_BY_ROOT visualizza la root di una qualsiasi riga.
SELECT LEVEL, EMPLOYEE_ID, LPAD(' ', LEVEL*2) || TITLE, SYS_CONNECT_BY_PATH(TITLE, '\') , CONNECT_BY_ROOT TITLE
FROM ORGANIGRAMMA
START WITH EMPLOYEE_ID =1
CONNECT BY REPORTS_TO = PRIOR EMPLOYEE_ID
ORDER SIBLINGS BY TITLE;
Escludere un ramo dell'albero
La clausola CONNECT BY permette anche di selezionare solo alcuni rami (branches) dell'albero aggiungendo la condizione che la colonna emplyee_id sia diversa dal nodo che non si vuole visualizzare.
Esempio: escludere il ramo che inizia con il nodo SENIOR VICE PRESIDENT
SELECT LEVEL, EMPLOYEE_ID, LPAD(' ', LEVEL*2) || TITLE
FROM ORGANIGRAMMA
START WITH EMPLOYEE_ID =1
CONNECT BY REPORTS_TO = PRIOR EMPLOYEE_ID AND EMPLOYEE_ID <> 3;
oppure
CONNECT BY REPORTS_TO = prior EMPLOYEE_ID and TITLE <> 'SENIOR VICE PRESIDENT';
La clausola WHERE può essere inserita solo prima della clausola START WITH e CONNCET BY. La where può essere usata per escludere un intero ramo ma è formalmente più corretto utilizzare la clausola CONNECT BY.
Le clausole START WITH e CONNECT BY possono apparire in qualsiasi ordine nella Select.
