오라클 계층형 쿼리 START WITH ~ CONNECT BY 구문

##계층형 쿼리란?

• 한 테이블에 담겨 있는 여러 레코드들이 서로 상하 관계(부모, 자식) 관게를 이루며 존재할 때, 이 관계에 따라 레코드를 hierarchical(상하위) 한 구조로 가져올 때 사용되는 SQL을 의미한다.

ex) 조직도, 부서 등..

위 그림의 관계를 테이블로 구성

CREATE TABLE TB_CMM_DEPT
( 
   DEPT_CD     VARCHAR2(8) NOT NULL PRIMARY KEY,
   PAR_DEPT_CD VARCHAR2(8), -- 상위 부서 코드
   DEPT_NM     VARCHAR2(50), -- 부서 코드
   USE_YN      CHAR(1),
   REG_DTM     DATE,
   REG_USER    VARCHAR2(30),
   MOD_DTM     DATE,
   MOD_USER    VARCHAR2(30)
);

START WITH

• 계층 질의의 루트(부모행)로 사용될 행을 지정한다.
• 서브 쿼리를 사용할 수도 있다.

START WITH 구문에서는 어떤 레코드를 최상위 레코드로 잡을지 지정한다.

ex) 최상위 부서를 최상위 레코드로 두는 경우

SELECT * FROM TB_CMM_DEPT
START WITH PAR_DEPT_CD IS NULL

CONNECT BY

• 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정할 수 있다.

• PRIOR 연산자와 함께 사용하여 계층구조로 표현할 수 있다.

• CONNECT BY PRIOR 자식컬럼 = 부모컬럼 -> 부모에서 자식으로 트리 구성 (Top Down)

• CONNECT BY PRIOR 부모컬럼 = 자식컬럼 -> 자식에서 부모로 트리 구성(Bottom Up)

• 부모행 방향으로 전개(Bottom-Up)
  

• 자식행 방향으로 전개(Top-Down)
  

방향은 'PRIOR'가 어디에 지정되어 있냐에 따라 달라지는데 PRIOR가 없는 곳에서 PRIOR가 있는 곳으로 전개된다.

LEVEL Pseudocolumn

• LEVEL은 계층구조 쿼리에서 수행 결과의 Depth를 표현하는 의사컬럼이다.

• 의사컬럼이란 '가짜 컬럼'을 의미한다. 즉, 테이블 생성 시에 DBA나 생성자가 별도로 정의한 컬럼이 아니다. 단순하게 레코드들이 최상의 레코드로부터 몇 단계 깊이에 있는지를 참고하는 용도로만 사용된다.

ex)

LVL	  DEPT_CD	 PAR_DEPT_CD	DEPT_NM
---  ---------  -----------   -------------
1	DEPT_002	DEPT_001	경영지원부
2	DEPT_004	DEPT_002	회계팀
2	DEPT_005	DEPT_002	인사팀
2	DEPT_006	DEPT_002	재무팀
1	DEPT_003	DEPT_001	영업본부
2	DEPT_007	DEPT_003	국내영업팀
2	DEPT_008	DEPT_003	해외영업팀
2	DEPT_009	DEPT_003	영업기획팀
2	DEPT_010	DEPT_003	영업지원팀

ORDER SIBLIGS BY + 컬럼명

• ORDER SIBLINGS BY 절을 사용하면 계층구조 쿼리에서 편하게 정렬 작업을 할 수 있다.

SYS_CONNECT_BY_PATH('컬럼명' , '구분자')

• 계층 쿼리의 해당 컬럼명을 구분자로 구분하면서 데이터 누적

ex)

SELECT SYS_CONNECT_BY_PATH('DEPT_NM',',') D_NAME
FROM A
START WITH TB_CMM_DEPT
START WITH PAR_DEPT_CD IS NULL
CONNECT BY PRIOR DEPT_CD = PAR_DEPT_CD

-- 결과
D_NAME
----------
,경영지원부
,경영지원부,회계팀
,경영지원부,인사팀
...

CONNECT BY의 실행 순서

• 첫째 START WITH절
• 둘째 CONNECT BY절
• 셋째 WHERE절