Oracle PL/SQL/Hierarchical Query/Start With Connect
Hierarchical Queries: START WITH and CONNECT BY PRIOR clauses
-- Hierarchical Queries
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> --START WITH and CONNECT BY PRIOR clauses.
SQL>
SQL> SELECT employee_id, manager_id, first_name, last_name
2 FROM employee
3 START WITH employee_id = 1
4 CONNECT BY PRIOR employee_id = manager_id;
EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME
----------- ---------- ---------- ----------
1 0 James Smith
2 1 Ron Johnson
3 2 Fred Hobbs
5 2 Rob Green
4 1 Susan Jones
6 4 Jane Brown
9 6 Henry Heyson
7 4 John Grey
8 7 Jean Blue
10 1 Kevin Black
11 10 Keith Long
12 10 Frank Howard
13 10 Doreen Penn
13 rows selected.
SQL>
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>
Including Other Conditions in a Hierarchical Query
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> -- Including Other Conditions in a Hierarchical Query
SQL>
SQL> SELECT LEVEL,
2 LPAD(" ", 2 * LEVEL - 1) || first_name || " " ||
3 last_name AS employee, salary
4 FROM employee
5 WHERE salary <= 50000
6 START WITH employee_id = 1
7 CONNECT BY PRIOR employee_id = manager_id;
LEVEL EMPLOYEE SALARY
---------- ------------------------- ----------
3 Rob Green 40000
3 Jane Brown 45000
4 Henry Heyson 30000
3 John Grey 30000
4 Jean Blue 29000
3 Keith Long 50000
3 Frank Howard 45000
3 Doreen Penn 47000
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>
Using a Subquery in a START WITH Clause
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> --Using a Subquery in a START WITH Clause
SQL>
SQL> SELECT LEVEL,
2 LPAD(" ", 2 * LEVEL - 1) || first_name || " " ||
3 last_name AS employee
4 FROM employee
5 START WITH employee_id = (
6 SELECT employee_id
7 FROM employee
8 WHERE first_name = "Kevin"
9 AND last_name = "Black"
10 )
11 CONNECT BY PRIOR employee_id = manager_id;
LEVEL EMPLOYEE
---------- -------------------------
1 Kevin Black
2 Keith Long
2 Frank Howard
2 Doreen Penn
SQL>
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>