12. 계층형 쿼리(Hierarchical Query)

계층형 쿼리란? 

상위계층, 하위계층이 있는 구조와 관련한 쿼리

상-하위 관계 표현이 가능

---

SELECT employee_id, first_name, LPAD(first_name, level*1 + length(first_name), '*') as "단계"
FROM EMPLOYEES
CONNECT BY  PRIOR employee_id = manager_id
start with employee_id = 100;

SELECT employee_id, first_name, 
LPAD(first_name, level*3 + length(first_name), '*') as "단계",
SYS_CONNECT_BY_PATH(first_name, '->') as "Order(low->high)"
FROM EMPLOYEES
CONNECT BY prior employee_id = manager_id;

CONNECT_BY_ISLEAF = 0/1

*마지막 단계(최하위 레벨) 생략

SELECT employee_id, first_name, 
LPAD(first_name, level*3 + length(first_name), '*') as "단계",
SYS_CONNECT_BY_PATH(first_name, '->') as "Order(low->high)"
FROM EMPLOYEES
WHERE CONNECT_BY_isleaf = 0
CONNECT BY prior employee_id = manager_id;

*마지막 단계(최하위 레벨)만 출력

SELECT employee_id, first_name, 
LPAD(first_name, level*3 + length(first_name), '*') as "단계",
SYS_CONNECT_BY_PATH(first_name, '->') as "Order(low->high)"
FROM EMPLOYEES
WHERE CONNECT_BY_isleaf = 1
CONNECT BY prior employee_id = manager_id;