14. Oracle 계층형쿼리

13.HierarchyQuery.sql

0.00MB

계층형 쿼리란?

Oracle 8i버전부터 제공하였습니다 . 일반적인 DBMS에서 제공하지 않고 Oracle 만의 특별한 기능이며

평등한 관계가 아닌 수직적인 구조를 가집니다.

수직적 구조로 보았을때, 최상위(부모 노드) - 중위(자식 노드) - 하위(리프) 형태로 이뤄져있습니다

그렇다면 테이블에서는 아래와 같이 표현됩니다.

Item-Id	Parent-Id	Child-Id	Comment
100	null	Phone	핸드폰의 최상위입니다. 최상위 노드는 부모 키를 가지지 않습니다.
101	100	Main Board	중위 노드에 속하며, 핸드폰의 구성요소 중 하나입니다.
102	100	Battery	중위 노드에 속하며, 핸드폰의 구성요소 중 하나입니다.
103	101	CPU	중위 노드에 속하며, 메인보드 구성요소 중 하나입니다.

테스트를 위한 샘플 데이터 생성

CREATE TABLE BOM_TABLE( 
    ITEM_ID     NUMBER(3) NOT NULL, 
    PARENT_ID   NUMBER(3), 
    ITEM_NAME   VARCHAR2(20) NOT NULL, 
    PRIMARY KEY (ITEM_ID) 
); 

INSERT INTO BOM_TABLE VALUES(100,NULL,'PHONE'); 
INSERT INTO BOM_TABLE VALUES(101,100,'Main Board'); 
INSERT INTO BOM_TABLE VALUES(102,100,'Battery'); 
INSERT INTO BOM_TABLE VALUES(103,101,'CPU'); 
INSERT INTO BOM_TABLE VALUES(104,101,'Memory Card'); 
INSERT INTO BOM_TABLE VALUES(105,101,'Bluetooth'); 

SELECT * FROM BOM_TABLE; 

 

계층형 데이터가 없다면, Self Join, Outer Join 등을 사용하여 Select 구문을 사용해보지만 원하는 대로 출력되지 않습니다.

SELECT S1.ITEM_NAME, S1.ITEM_ID, S2.ITEM_NAME PARENT_NAME 
FROM BOM_TABLE S1, BOM_TABLE S2 
WHERE S1.PARENT_ID = S2.ITEM_ID (+) 
ORDER BY S1.ITEM_ID; 

 

---

이를 위하여, 계층형쿼리로 만들기 START WITH , CONNECT BY 절을 이용 합니다.

LPAD와 같이 많이 사용되며, 레벨별로 들여쓰기하여 보기 좋게 사용합니다.

START WITH (기준이 될 최상위 노드)

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

SELECT LPAD(' ',2*(LEVEL-1)) || ITEM_NAME 부품명 
FROM BOM_TABLE 
START WITH PARENT_ID IS NULL                -- 최상위 노드를 지정해줍니다. 
CONNECT BY PRIOR ITEM_ID = PARENT_ID;       -- PRIOR는 부모 노드를 찾아서 연결하는 역활을 합니다. 
/*CONNECT BY PARENT_ID = PRIOR ITEM_ID;*/   -- 아래와 같이 사용할 수 있습니다. 




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




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