[SQL] 오라클 계층형 쿼리

계층형 쿼리

한 테이블에 담겨 있는 데이터들이 서로 상하관계가 있을 때
이 관계에 따라 데이터를 HIERARCHICAL 구조로 가져오는 쿼리

예시: 원피스 해군 조직도

계층구조를 이렇게 생성할 수 있음.

CREATE TABLE TB_CMM_DEPT
( 
   DEPT_ID     VARCHAR2(8) NOT NULL PRIMARY KEY, -- 조직ID
   PAR_DEPT_CD VARCHAR2(8), -- 상위 조직
   DEPT_NM     VARCHAR2(50) -- 조직 이름
);

START WITH

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

"오로성" 최상위로 잡을때

SELECT * 
	FROM TB_CMM_DEPT
	START WITH PAR_DEPT_CD IS NULL

"세계정부 총수" 최상위로 잡을때

SELECT * 
	FROM TB_CMM_DEPT
	START WITH PAR_DEPT_CD ='WRLD_GOV'

서브쿼리 사용할 때

SELECT *
	FROM TB_CMM_DEPT
    START WITH PAR_DEPT_CD = (
  		SELECT DEPT_CD
        FROM TB_CMM_DEPT
        WHERE DEPT_NM = '세계정부 총수'
    )

CONNECT BY

• 트리를 뻗어나가고 싶을 때 어떻게 할지 정해줌.
  세계정부 총수가 ROOT일때, 오로성으로 나아갈지, 그아래 자식들로 나아갈지 방향지정.
• 상위계층과 하위계층의 관계 규정 가능
• PRIOR 계층구조 표현 가능

CONNECT BY PRIOR 자식컬럼 = 부모컬럼 -- 자식행으로
CONNECT BY PRIOR 부모컬럼 = 자식컬럼 -- 부모행으로

세계정부 총수 이하의 레코드를 조회하고 싶다면,,

SELECT *
FROM TB_CMM_DEPT
START WITH PAR_DEPT_CD = 'WRLD_GOV'
CONNECT BY PAR_DEPT_CD = PRIOR DEPT_CD; 

실행 순서

• 1. START WITH절
• 2. CONNECT BY절
• 3. WHERE절 순서로 풀리게 되어있다.

LEVEL Pseudocolumn

LEVEL은 계층구조 쿼리에서 DEPTH를 나타내는 의사컬럼 (가짜 컬럼, 몇단계 깊이인지 알려주는 용도)

SELECT  LEVEL AS LVL,
        TCD.DEPT_CD,
        TCD.PAR_DEPT_CD,
        TCD.DEPT_NM
  FROM  TB_CMM_DEPT TCD
  START WITH TCD.PAR_DEPT_CD = 'WRLD_GOV'
  CONNECT BY TCD.PAR_DEPT_CD = PRIOR TCD.DEPT_CD;

ORDER SIBLINGS BY + 컬럼명