Oracle PL/SQL/Hierarchical Query/Root

Output full path of hierarchy

    
SQL>
SQL> create table emp
  2  ( empno      NUMBER(4)    constraint E_PK primary key
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , sal        NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2)    default 10
 11  ) ;
Table created.
SQL> insert into emp values(1,"Tom","N",   "TRAINER", 13,date "1965-12-17",  800 , NULL,  20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20",  1600, 300,   30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" ,  "Tester",6,date "1962-02-22",  1250, 500,   30);
1 row created.
SQL> insert into emp values(4,"Jane","JM",  "Designer", 9,date "1967-04-02",  2975, NULL,  20);
1 row created.
SQL> insert into emp values(5,"Mary","P",  "Tester",6,date "1956-09-28",  1250, 1400,  30);
1 row created.
SQL> insert into emp values(6,"Black","R",   "Designer", 9,date "1963-11-01",  2850, NULL,  30);
1 row created.
SQL> insert into emp values(7,"Chris","AB",  "Designer", 9,date "1965-06-09",  2450, NULL,  10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26",  3000, NULL,  20);
1 row created.
SQL> insert into emp values(9,"Peter","CC",   "Designer",NULL,date "1952-11-17",  5000, NULL,  10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28",  1500, 0,     30);
1 row created.
SQL> insert into emp values(11,"Ana","AA",  "TRAINER", 8,date "1966-12-30",  1100, NULL,  20);
1 row created.
SQL> insert into emp values(12,"Jane","R",   "Manager",   6,date "1969-12-03",  800 , NULL,  30);
1 row created.
SQL> insert into emp values(13,"Fake","MG",   "TRAINER", 4,date "1959-02-13",  3000, NULL,  20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager",   7,date "1962-01-23",  1300, NULL,  10);
1 row created.
SQL>
SQL> column  full_path format a40
SQL>
SQL> select  ename
  2  ,       connect_by_root ename            as Designer
  3  ,       sys_connect_by_path(ename," > ") as full_path
  4  from    emp
  5  start   with job = "Designer"
  6  connect by prior empno = mgr;
ENAME    DESIGNER FULL_PATH
-------- -------- ----------------------------------------
Jane     Jane      > Jane
Smart    Jane      > Jane > Smart
Ana      Jane      > Jane > Smart > Ana
Fake     Jane      > Jane > Fake
Tom      Jane      > Jane > Fake > Tom
Black    Black     > Black
Jack     Black     > Black > Jack
Wil      Black     > Black > Wil
Mary     Black     > Black > Mary
Take     Black     > Black > Take
Jane     Black     > Black > Jane
Chris    Chris     > Chris
Mike     Chris     > Chris > Mike
Peter    Peter     > Peter
Jane     Peter     > Peter > Jane
Smart    Peter     > Peter > Jane > Smart
Ana      Peter     > Peter > Jane > Smart > Ana
Fake     Peter     > Peter > Jane > Fake
Tom      Peter     > Peter > Jane > Fake > Tom
ENAME    DESIGNER FULL_PATH
-------- -------- ----------------------------------------
Black    Peter     > Peter > Black
Jack     Peter     > Peter > Black > Jack
Wil      Peter     > Peter > Black > Wil
Mary     Peter     > Peter > Black > Mary
Take     Peter     > Peter > Black > Take
Jane     Peter     > Peter > Black > Jane
Chris    Peter     > Peter > Chris
Mike     Peter     > Peter > Chris > Mike
27 rows selected.
SQL>
SQL>
SQL> drop table emp;
Table dropped.

Starting at a Node Other than the Root

 

SQL>
SQL> CREATE TABLE employee (
  2    employee_id INTEGER,
  3    manager_id INTEGER,
  4    first_name VARCHAR2(10) NOT NULL,
  5    last_name VARCHAR2(10) NOT NULL,
  6    title VARCHAR2(20),
  7    salary NUMBER(6, 0)
  8  );
Table created.
SQL>
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 1         ,0            , "James"  ,"Smith"  ,"CEO",800000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 2         , 1         ,"Ron"     ,"Johnson","Sales Manager",600000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 3         , 2         ,"Fred"    ,"Hobbs"  ,"Sales Person",200000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 4         , 1         ,"Susan"   ,"Jones"  ,"Support Manager",500000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 5         , 2         ,"Rob"     ,"Green"  ,"Sales Person", 40000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 6         , 4         ,"Jane"    ,"Brown"  ,"Support Person",45000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 7         , 4         ,"John"    ,"Grey"   ,"Support Manager",30000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 8         , 7         ,"Jean"    ,"Blue"   ,"Support Person",29000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 9         , 6         ,"Henry"   ,"Heyson" ,"Support Person",30000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 10        , 1         ,"Kevin"   ,"Black"  ,"Ops Manager",100000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 11        , 10        ,"Keith"   ,"Long"   ,"Ops Person",50000);
1 row created.
SQL>
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 12        , 10        ,"Frank"   ,"Howard" ,"Ops Person",45000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 13        , 10        ,"Doreen"  ,"Penn"   ,"Ops Person",47000);
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> select * from employee;
EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME  TITLE                    SALARY
----------- ---------- ---------- ---------- -------------------- ----------
          1          0 James      Smith      CEO                      800000
          2          1 Ron        Johnson    Sales Manager            600000
          3          2 Fred       Hobbs      Sales Person             200000
          4          1 Susan      Jones      Support Manager          500000
          5          2 Rob        Green      Sales Person              40000
          6          4 Jane       Brown      Support Person            45000
          7          4 John       Grey       Support Manager           30000
          8          7 Jean       Blue       Support Person            29000
          9          6 Henry      Heyson     Support Person            30000
         10          1 Kevin      Black      Ops Manager              100000
         11         10 Keith      Long       Ops Person                50000
         12         10 Frank      Howard     Ops Person                45000
         13         10 Doreen     Penn       Ops Person                47000
13 rows selected.
SQL>
SQL> --Starting at a Node Other than the Root
SQL>
SQL> SELECT LEVEL,
  2   LPAD(" ", 2 * LEVEL - 1) || first_name || " " ||
  3   last_name AS employee
  4  FROM employee
  5  START WITH last_name = "Jones"
  6  CONNECT BY PRIOR employee_id = manager_id;
     LEVEL EMPLOYEE
---------- -------------------------
         1  Susan Jones
         2    Jane Brown
         3      Henry Heyson
         2    John Grey
         3      Jean Blue
SQL>
SQL>
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>