[Oracle] 계층형 쿼리 ( Hierarchical Queries )

 

Hierarchical Queries

If a table contains hierarchical data, then you can select rows in a hierarchical order using the hierarchical query clause

 

---

 

 

• 오라클에서만 사용할 수 있는 쿼리
• 계층형 정보를 표현하고자 오라클8부터 지원

 

 

 

[ 문법 ]

 

START WITH condition1 CONNECT BY condition2

 

SELECT LPAD(' ', 2*(LEVEL-1)) || item_name item_names 
FROM BOM_SPHONE
START WITH parent_id IS null
CONNECT BY PRIOR item_id = parent_id

 

START WITH condition1

condition1을 만족하는 row가 root ( 생략 시 모든 row가 root )

 

CONNECT BY PRIOR condition2

condition2에서 부모노드와 자식노드 연결

CONNECT BY PRIOR 자식노드 = 부모노드

 

 

 

 

[ 활용 ]

 

• LEVEL에 따른 들여쓰기

SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || FIRST_NAME || ' ' || LAST_NAME "name"
FROM EMPLOYEES e
START WITH e.MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

 

 

• 계층형 쿼리 + Join

SELECT j.JOB_TITLE 직위, LPAD(' ', 4*(LEVEL-1)) || FIRST_NAME || ' ' || LAST_NAME "name"
FROM EMPLOYEES e, JOBS j
WHERE e.JOB_ID = j.JOB_ID
START WITH e.MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

 

 

 

 

[ 스마트폰 BOM 예시 ]

 

• bom_sphone 테이블 생성

CREATE TABLE bom_sphone(
	item_id number(3) NOT NULL,
	parent_id number(3),
	item_name varchar2(20)	NOT NULL,
	PRIMARY KEY (item_id)
);

 

• 계층을 가진 데이터 insert

INSERT INTO bom_sphone VALUES (100, NULL, '스마트폰');
INSERT INTO bom_sphone VALUES (101, 100, 'pcb');
INSERT INTO bom_sphone VALUES (102, 100, 'battery');
INSERT INTO bom_sphone VALUES (103, 101, 'cpu');
INSERT INTO bom_sphone VALUES (104, 101, 'memory');
INSERT INTO bom_sphone VALUES (105, 101, 'bluetooth');

 

• join을 사용한 쿼리

SELECT s1.item_name, s1.item_id, s2.item_name parent_name
FROM BOM_SPHONE s1, BOM_SPHONE s2
WHERE s1.parent_id = s2.item_id (+)
ORDER BY s1.item_id;

parent_id가 null인 '스마트폰' 데이터(root)도 추출해야하므로 outer join

 

 

• 계층형 쿼리 ( LEVEL에 따라 들여쓰기 )

SELECT LPAD(' ', 2*(LEVEL-1)) || item_name item_names 
FROM BOM_SPHONE
START WITH parent_id IS null
CONNECT BY PRIOR item_id = parent_id;