Oracle PL/SQL/Hierarchical Query/Indent
employee tree: who is my manager
<source lang="sql">
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.
</source>
LEVEL and the LPAD(): Formatting the Results from a Hierarchical Query
<source lang="sql">
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>
</source>
