23.10.17. Oracle SQL 계층 검색(hierarchical query) START WITH-CONNECT BY PRIOR

■ 계층 검색(hierarchical query)

EX#) 조직도, 상품 카테고리 등

SELECT *
FROM hr.employees
START WITH employee_id = 101 /* 시작점 */
CONNECT BY PRIOR employee_id = manager_id ; /* 연결고리 조건, PRIOR기준 */

SELECT employee_id, manager_id
FROM hr.employees
START WITH employee_id = 101
CONNECT BY PRIOR employee_id = manager_id ;

# PRIOR 의 위치에 따라 실행 값이 다르다.

SELECT *
FROM hr.employees
START WITH employee_id = 101 /* 시작점 */
CONNECT BY employee_id = PRIOR manager_id ; /* 연결고리 조건 */

SELECT employee_id, manager_id
FROM hr.employees
START WITH employee_id = 101
CONNECT BY employee_id = PRIOR manager_id ;

# level 로 관계파악.

SELECT level, last_name
FROM hr.employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id ;

         
# level + lpad()         

SELECT level, lpad(' ', level*2-2, ' ')||last_name last_name
FROM hr.employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id ;

# level 로 인해 정렬

SELECT level, lpad(' ', level*2-2, ' ')||last_name as lastname
FROM hr.employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id 
ORDER SIBLINGS BY last_name;

a. 계층 검색에서 정렬을 수행하려면 ORDER SIBLINGS BY 로 작성해야 한다.
b. ORDER SIBLINGS BY 에서는 위치표기법, 열별칭을 사용할 수 없다.

# WHERE 절 사용

SELECT level, lpad(' ', level*2-2, ' ')||last_name as lastname
FROM hr.employees
WHERE employee_id != 101 /* 행만 제거 */
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id 
ORDER SIBLINGS BY last_name;

# 분기 제거, 조직도에서 101번의 조직을 제외하고 싶다면?

SELECT level, lpad(' ', level*2-2, ' ')||last_name as lastname, employee_id
FROM hr.employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id 
AND employee_id != 101 /* 분기 제거 */
ORDER SIBLINGS BY last_name;