[SQL] 오라클 계층형쿼리 START WITH ~ CONNECT BY

 

계층형쿼리란?

한 테이블에 레코드들이 계층관계(상위,하위)를 이루며 존재할 때, 이 관계에 따라 레코드를 계층관계(상위,하위)의 한 구조로 데이터를 가져올 때 사용되는 쿼리

---

 

계층형쿼리설명

 

예제데이터생성

 

[DEPTH_1] 동물
--[DEPTH_2] 포유류

----[DEPTH_4] 사자

----[DEPTH_5] 호랑이

----[DEPTH_6] 치타
--[DEPTH_3] 조류

----[DEPTH_7] 닭

----[DEPTH_8] 비둘기

----[DEPTH_9] 독수리

/* 예제테이블생성 */
CREATE TABLE  ANI_DEPTH_TAB
( 
   ANIMAL_DEPTH_CD       VARCHAR2(8) NOT NULL PRIMARY KEY,
   PAR_ANIMAL_DEPTH_CD   VARCHAR2(8),
   ANIMAL_NM   			 VARCHAR2(50)
);

/* 예제데이터INSERT */
INSERT INTO ANI_DEPTH_TAB(ANIMAL_DEPTH_CD,PAR_ANIMAL_DEPTH_CD,ANIMAL_NM)VALUES('DEPTH_1',NULL,'동물');
INSERT INTO ANI_DEPTH_TAB(ANIMAL_DEPTH_CD,PAR_ANIMAL_DEPTH_CD,ANIMAL_NM)VALUES('DEPTH_2','DEPTH_1','포유류');
INSERT INTO ANI_DEPTH_TAB(ANIMAL_DEPTH_CD,PAR_ANIMAL_DEPTH_CD,ANIMAL_NM)VALUES('DEPTH_3','DEPTH_1','조류');
INSERT INTO ANI_DEPTH_TAB(ANIMAL_DEPTH_CD,PAR_ANIMAL_DEPTH_CD,ANIMAL_NM)VALUES('DEPTH_4','DEPTH_2','사자');
INSERT INTO ANI_DEPTH_TAB(ANIMAL_DEPTH_CD,PAR_ANIMAL_DEPTH_CD,ANIMAL_NM)VALUES('DEPTH_5','DEPTH_2','호랑이');
INSERT INTO ANI_DEPTH_TAB(ANIMAL_DEPTH_CD,PAR_ANIMAL_DEPTH_CD,ANIMAL_NM)VALUES('DEPTH_6','DEPTH_2','치타');
INSERT INTO ANI_DEPTH_TAB(ANIMAL_DEPTH_CD,PAR_ANIMAL_DEPTH_CD,ANIMAL_NM)VALUES('DEPTH_7','DEPTH_3','닭');
INSERT INTO ANI_DEPTH_TAB(ANIMAL_DEPTH_CD,PAR_ANIMAL_DEPTH_CD,ANIMAL_NM)VALUES('DEPTH_8','DEPTH_3','비둘기');
INSERT INTO ANI_DEPTH_TAB(ANIMAL_DEPTH_CD,PAR_ANIMAL_DEPTH_CD,ANIMAL_NM)VALUES('DEPTH_9','DEPTH_3','독수리');

SELECT *
FROM ANI_DEPTH_TAB;

 

 

START WITH ~ CONNECT BY

 

- START WITH ~

어떤 레코드를 최상위레코드로 잡을지 지정.

서브쿼리 사용 가능.

 

- CONNECT BY ~

상위계층(부모행)과 하위계층의 관계를 지정.

서브쿼리 사용  불가능.

* CONNECT BY PRIOR 자식컬럼 = 부모컬럼 : 부모컬럼 -> 자식컬럼

* CONNECT BY 자식컬럼 = PRIOR  부모컬럼 : 자식컬럼 -> 부모컬럼

* CONNECT BY NOCYCLE PRIOR : NOCYCLE 파라미처를 이용하여 무한루프 방지

PRIOR가 어디에 지정되어 있느냐에 따라 PRIOR가 없는 곳에서 PRIOR가 있는 곳으로 관계가 형성됨

---

1.  Top-Down 출력

[관계]  PAR_ANIMAL_DEPTH_CD(부모컬럼) -> ANIMAL_DEPTH_CD(자식컬럼)

            CONNECT BY PRIOR ANIMAL_DEPTH_CD =  PAR_ANIMAL_DEPTH_CD

SELECT *
FROM ANI_DEPTH_TAB
START WITH PAR_ANIMAL_DEPTH_CD = 'DEPTH_1'
CONNECT BY PRIOR ANIMAL_DEPTH_CD =  PAR_ANIMAL_DEPTH_CD;

 

2.  Bottom-Up 출력

[관계] ANIMAL_DEPTH_CD(자식컬럼) ->PAR_ANIMAL_DEPTH_CD(부모컬럼) 

            CONNECT BY ANIMAL_DEPTH_CD = PRIOR  PAR_ANIMAL_DEPTH_CD

SELECT *
FROM ANI_DEPTH_TAB
START WITH PAR_ANIMAL_DEPTH_CD = 'DEPTH_1'
CONNECT BY ANIMAL_DEPTH_CD = PRIOR  PAR_ANIMAL_DEPTH_CD

 

 

LEVEL

 

- LEVEL

계층구조 쿼리에서 수행 결과의 Depth를 표현하는 의사컬럼(의사컬럼은 '가짜컬럼'을 의미) .

---

SELECT LEVEL
       , PAR_ANIMAL_DEPTH_CD
       , ANIMAL_DEPTH_CD
       , ANIMAL_NM
FROM ANI_DEPTH_TAB
START WITH PAR_ANIMAL_DEPTH_CD = 'DEPTH_1'
CONNECT BY PRIOR ANIMAL_DEPTH_CD =  PAR_ANIMAL_DEPTH_CD

 

 

ORDER SIBLINGS BY

 

- ORDER SIBLINGS BY 

계층구조 내에서 정렬하도록 함.

계층구조를 그대로 유지하면서 상위계층을 가진 하위계층들 간의 정렬기준을 줌.

---

1. ORDER BY 단순정렬시

/* ORDER BY 정렬시 :  PAR_ANIMAL_DEPTH_CD 기준 'A,B,C,...'순 단순정렬*/
SELECT LEVEL
       , PAR_ANIMAL_DEPTH_CD
       , ANIMAL_DEPTH_CD
       , ANIMAL_NM
FROM ANI_DEPTH_TAB
START WITH PAR_ANIMAL_DEPTH_CD = 'DEPTH_1'
CONNECT BY PRIOR ANIMAL_DEPTH_CD =  PAR_ANIMAL_DEPTH_CD
ORDER BY PAR_ANIMAL_DEPTH_CD;

 

2.  ORDER SIBLINGS BY 정렬시

/* ORDER SIBLINGS BY : 계층구조 내에서 정렬 */
SELECT LEVEL
       , PAR_ANIMAL_DEPTH_CD
       , ANIMAL_DEPTH_CD
       , ANIMAL_NM
FROM ANI_DEPTH_TAB
START WITH PAR_ANIMAL_DEPTH_CD = 'DEPTH_1'
CONNECT BY PRIOR ANIMAL_DEPTH_CD =  PAR_ANIMAL_DEPTH_CD
ORDER SIBLINGS BY PAR_ANIMAL_DEPTH_CD;

 

 

SYS_CONNECT_BY_PATH

 

- SYS_CONNECT_BY_PATH

루트 노드에서 시작하여 자신의 행까지 연결된 경로 정보를 반환.

계층형 쿼리에서만 사용가능한 함수.

---

SELECT LEVEL
       , LPAD(' ', 2*LEVEL-1) || SYS_CONNECT_BY_PATH(ANIMAL_NM, '--') DEPTH
       , PAR_ANIMAL_DEPTH_CD
       , ANIMAL_DEPTH_CD
       , ANIMAL_NM
FROM ANI_DEPTH_TAB
START WITH PAR_ANIMAL_DEPTH_CD IS NULL
CONNECT BY PRIOR ANIMAL_DEPTH_CD =  PAR_ANIMAL_DEPTH_CD
ORDER SIBLINGS BY PAR_ANIMAL_DEPTH_CD

 

 

계층형쿼리 실행순서

 

1. START WITH ~

2. CONNECT BY ~

3. WHERE ~

 

---

+예제쿼리

--동물
---- 동물/포유류 , / 동물/조류
------ 동물/포유류:사자,호랑이,치타
------ 동물/조류:닭,비둘기,독수리

/* 예제테이블생성 */
CREATE TABLE  ANI_DEPTH_TAB
( 
   ANIMAL_DEPTH_CD       VARCHAR2(8) NOT NULL PRIMARY KEY,
   PAR_ANIMAL_DEPTH_CD   VARCHAR2(8),
   ANIMAL_NM   VARCHAR2(50)
);

/* 예제데이터INSERT */
INSERT INTO ANI_DEPTH_TAB(ANIMAL_DEPTH_CD,PAR_ANIMAL_DEPTH_CD,ANIMAL_NM)VALUES('DEPTH_1',NULL,'동물');
INSERT INTO ANI_DEPTH_TAB(ANIMAL_DEPTH_CD,PAR_ANIMAL_DEPTH_CD,ANIMAL_NM)VALUES('DEPTH_2','DEPTH_1','포유류');
INSERT INTO ANI_DEPTH_TAB(ANIMAL_DEPTH_CD,PAR_ANIMAL_DEPTH_CD,ANIMAL_NM)VALUES('DEPTH_3','DEPTH_1','조류');
INSERT INTO ANI_DEPTH_TAB(ANIMAL_DEPTH_CD,PAR_ANIMAL_DEPTH_CD,ANIMAL_NM)VALUES('DEPTH_4','DEPTH_2','사자');
INSERT INTO ANI_DEPTH_TAB(ANIMAL_DEPTH_CD,PAR_ANIMAL_DEPTH_CD,ANIMAL_NM)VALUES('DEPTH_5','DEPTH_2','호랑이');
INSERT INTO ANI_DEPTH_TAB(ANIMAL_DEPTH_CD,PAR_ANIMAL_DEPTH_CD,ANIMAL_NM)VALUES('DEPTH_6','DEPTH_2','치타');
INSERT INTO ANI_DEPTH_TAB(ANIMAL_DEPTH_CD,PAR_ANIMAL_DEPTH_CD,ANIMAL_NM)VALUES('DEPTH_7','DEPTH_3','닭');
INSERT INTO ANI_DEPTH_TAB(ANIMAL_DEPTH_CD,PAR_ANIMAL_DEPTH_CD,ANIMAL_NM)VALUES('DEPTH_8','DEPTH_3','비둘기');
INSERT INTO ANI_DEPTH_TAB(ANIMAL_DEPTH_CD,PAR_ANIMAL_DEPTH_CD,ANIMAL_NM)VALUES('DEPTH_9','DEPTH_3','독수리');

/* 예제데이터 기본조회 */
SELECT *
FROM ANI_DEPTH_TAB
;

/* 최상위 '동물'기준 Top-Down 조회 */
SELECT *
FROM ANI_DEPTH_TAB
START WITH PAR_ANIMAL_DEPTH_CD IS NULL
CONNECT BY PRIOR ANIMAL_DEPTH_CD =  PAR_ANIMAL_DEPTH_CD;

/* 최상위 '동물'기준 Bottom-Up 조회 */
SELECT *
FROM ANI_DEPTH_TAB
START WITH PAR_ANIMAL_DEPTH_CD IS NULL
CONNECT BY ANIMAL_DEPTH_CD = PRIOR  PAR_ANIMAL_DEPTH_CD


SELECT *
FROM ANI_DEPTH_TAB
START WITH PAR_ANIMAL_DEPTH_CD = 'DEPTH_1'
CONNECT BY PRIOR ANIMAL_DEPTH_CD =  PAR_ANIMAL_DEPTH_CD;

SELECT *
FROM ANI_DEPTH_TAB
START WITH PAR_ANIMAL_DEPTH_CD = 'DEPTH_1'
CONNECT BY ANIMAL_DEPTH_CD = PRIOR  PAR_ANIMAL_DEPTH_CD

--
SELECT *
FROM ANI_DEPTH_TAB
START WITH PAR_ANIMAL_DEPTH_CD = 'DEPTH_3'
CONNECT BY PRIOR ANIMAL_DEPTH_CD =  PAR_ANIMAL_DEPTH_CD;

SELECT *
FROM ANI_DEPTH_TAB
START WITH PAR_ANIMAL_DEPTH_CD = 'DEPTH_2'
CONNECT BY ANIMAL_DEPTH_CD = PRIOR  PAR_ANIMAL_DEPTH_CD


SELECT LEVEL
       , PAR_ANIMAL_DEPTH_CD
       , ANIMAL_DEPTH_CD
       , ANIMAL_NM
FROM ANI_DEPTH_TAB
START WITH PAR_ANIMAL_DEPTH_CD = 'DEPTH_1'
CONNECT BY PRIOR ANIMAL_DEPTH_CD =  PAR_ANIMAL_DEPTH_CD;



SELECT LEVEL
       , PAR_ANIMAL_DEPTH_CD
       , ANIMAL_DEPTH_CD
       , ANIMAL_NM
FROM ANI_DEPTH_TAB
START WITH PAR_ANIMAL_DEPTH_CD = 'DEPTH_1'
CONNECT BY PRIOR ANIMAL_DEPTH_CD =  PAR_ANIMAL_DEPTH_CD
ORDER BY PAR_ANIMAL_DEPTH_CD;


SELECT LEVEL
       , LPAD(' ', 2*LEVEL-1) || SYS_CONNECT_BY_PATH(ANIMAL_NM, '--') DEPTH
       , PAR_ANIMAL_DEPTH_CD
       , ANIMAL_DEPTH_CD
       , ANIMAL_NM
FROM ANI_DEPTH_TAB
START WITH PAR_ANIMAL_DEPTH_CD IS NULL
CONNECT BY PRIOR ANIMAL_DEPTH_CD =  PAR_ANIMAL_DEPTH_CD
ORDER SIBLINGS BY PAR_ANIMAL_DEPTH_CD