[ Oracle SQL ] 계층형쿼리 (Hierarchy Query)

계층형 쿼리란?

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

 

쉽게말하자면 계층형 쿼리는 말그대로 계층 관계를 나타내는 쿼리문을 말한다.

 

 

예를 들어

 

회사 조직도 (예)

위 회사 조직도는 최상위는 회사를 두고 그것을 중심으로 개발부분과 영업부문으로 나뉜다.

 

- 회사 : 최상위

- 개발부문 

          상위 : 회사

          하위 : 개발부, 부설연구소

- 영업부문

          상위 : 회사

          하위 : 해외영업부, 국내영업부, 영업기획팀 

 

 

실습 해보기

1.  해당 테이블 생성

CREATE TABLE TB_DEPT
( 
   DEPT_CD     VARCHAR2(8) NOT NULL PRIMARY KEY,
   PAR_DEPT_CD VARCHAR2(8),
   DEPT_NM     VARCHAR2(50)
);

 

2.  해당 테이블에 값 INSERT

INSERT INTO TB_DEPT(DEPT_CD,PAR_DEPT_CD,DEPT_NM)VALUES('DE_001',NULL,'회사');
INSERT INTO TB_DEPT(DEPT_CD,PAR_DEPT_CD,DEPT_NM)VALUES('DE_002','DE_001','개발부문');
INSERT INTO TB_DEPT(DEPT_CD,PAR_DEPT_CD,DEPT_NM)VALUES('DE_003','DE_001','영업부문');
INSERT INTO TB_DEPT(DEPT_CD,PAR_DEPT_CD,DEPT_NM)VALUES('DE_004','DE_002','개발부');
INSERT INTO TB_DEPT(DEPT_CD,PAR_DEPT_CD,DEPT_NM)VALUES('DE_005','DE_002','부설연구소');
INSERT INTO TB_DEPT(DEPT_CD,PAR_DEPT_CD,DEPT_NM)VALUES('DE_006','DE_003','해외영업부');
INSERT INTO TB_DEPT(DEPT_CD,PAR_DEPT_CD,DEPT_NM)VALUES('DE_007','DE_003','국내영업부');
INSERT INTO TB_DEPT(DEPT_CD,PAR_DEPT_CD,DEPT_NM)VALUES('DE_008','DE_003','영업기획팀');

 

3.  테이블에 값이 잘 들어 갔는지 확인

SELECT * FROM TB_DEPT;

 

4.  계층 쿼리

SELECT 
    LEVEL LEV,
    DEPT_CD,
    DEPT_NM,
    PAR_DEPT_CD
FROM TB_DEPT
START WITH PAR_DEPT_CD IS NULL -- 시작 위치를 정함
CONNECT BY PAR_DEPT_CD = PRIOR DEPT_CD -- 자식데이터를 지정
ORDER SIBLINGS BY DEPT_CD;

START WITH 절

    - 계층 구조 전개의 시작 위치를 지정 :  해당 실습 데이터상으로 상위를 IS NULL로 설정하면  회사가 최상위레벨이 된다.

 

CONNECT BY 절

    -  하위 데이터를 지정 : PRIOR를 사용해서 지정함.

     - 순방향 전개 : 상위 = PRIOR 하위

     - 역 방향 전개: 하위 =  PRIOR 상위

 

ORDER SIBLINGS BY 절

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

      즉, 계층내에서 정렬 할 수 있는 방법이다.

    * ORDER BY로 SORT를 하면 되지 않냐고 생각하겠지만. 그렇게 정렬을 할 경우 계층구조가 흐트러진다.

 

결과

위 쿼리문의 결과 값

 

5.  계층구조를 표현 확실히 보고싶다면! 

SELECT 
    LEVEL,
    DEPT_NM,
    LPAD(' ', 2*LEVEL-1) || SYS_CONNECT_BY_PATH(DEPT_NM, '/') PATH,
    DEPT_CD,
    PAR_DEPT_CD
FROM TB_DEPT
START WITH PAR_DEPT_CD IS NULL
CONNECT BY PAR_DEPT_CD = PRIOR DEPT_CD
ORDER SIBLINGS BY DEPT_CD;

 

LPAD절

    - LPAD는 왼쪽부터 총길이 만큼 지정한 문자를 채움.

 

SYS_CONNECT_BY_PATH(DEPT_NM, '/') 절

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

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

 

 

결과