Oracle PL/SQL/Hierarchical Query/Indent

employee tree: who is my manager

    
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> select  lpad(" ",2*level-1)||ename as ename
  2  from    emp
  3  start   with mgr is null
  4  connect by nocycle prior empno = mgr;
ENAME
--------------------------------------------------------------------------------
 Peter
   Jane
     Smart
       Ana
     Fake
       Tom
   Black
     Jack
     Wil
     Mary
     Take
     Jane
   Chris
     Mike
14 rows selected.
SQL>
SQL>
SQL> drop table emp;
Table dropped.

LEVEL and the LPAD(): Formatting the Results from a Hierarchical Query

 

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>
SQL> -- LEVEL and the LPAD(): Formatting the Results from a Hierarchical Query
SQL>
SQL> SET PAGESIZE 999
SQL>
SQL> COLUMN employee FORMAT A25
SQL>
SQL> SELECT LEVEL,
  2   LPAD(" ", 2 * LEVEL - 1) || first_name || " " ||
  3   last_name AS employee
  4  FROM employee
  5  START WITH employee_id = 1
  6  CONNECT BY PRIOR employee_id = manager_id;
     LEVEL EMPLOYEE
---------- -------------------------
         1  James Smith
         2    Ron Johnson
         3      Fred Hobbs
         3      Rob Green
         2    Susan Jones
         3      Jane Brown
         4        Henry Heyson
         3      John Grey
         4        Jean Blue
         2    Kevin Black
         3      Keith Long
         3      Frank Howard
         3      Doreen Penn
13 rows selected.
SQL>
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>