오라클 계층형 쿼리, 트리구조로 살펴보기(정렬, 최하위노드 찾기)

예제로 사용할 데이터의 트리구조

위 부서 정보와 같은 구조를 우리는 트리형태로 표현할 수 있다. 부서정보뿐만 아니라 디렉토리구조, HTML태그 구조등이 모두 상하관계를 갖는 트리형태로 표현될 수 있다.

 

이처럼 상하관계, 계층구조를 갖는 데이터베이스 정보를 오라클에서 어떻게 표현하는지 알아보자.

오라클은 CONNECT BY 절을 사용하여 계층형 데이터를 표현할 수 있다. 아래 샘플을 보자.

WITH DEPT AS (
    SELECT '0000' DEPT_CD, 'ROOT' PARENT_DEPT_CD, 'ROOT_DEPT' DEPT_NM FROM DUAL
    UNION ALL
    SELECT '1000' DEPT_CD, '0000' PARENT_DEPT_CD, '임원실' DEPT_NM FROM DUAL
    UNION ALL
    SELECT '1001', '1000', '회장실' FROM DUAL
    UNION ALL
    SELECT '1002', '1000', '사장실' FROM DUAL
    UNION ALL
    SELECT '2000', '0000', '마케팅' FROM DUAL
    UNION ALL
    SELECT '2001', '2000', '전략기획팀' FROM DUAL
    UNION ALL
    SELECT '2010', '2000', '온라인마케팅' FROM DUAL
    UNION ALL
    SELECT '2011', '2010', '소셜마케팅' FROM DUAL
    UNION ALL
    SELECT '3000', '0000', 'IT' FROM DUAL    
    UNION ALL
    SELECT '3010', '3000', '정보보안' FROM DUAL
    UNION ALL
    SELECT '3020', '3000', 'IT혁신' FROM DUAL
    UNION ALL
    SELECT '3021', '3020', '외주개발' FROM DUAL        
    UNION ALL
    SELECT '9000', 'ROOT_A', '독립부서' FROM DUAL
    UNION ALL
    SELECT '9010', '9000', '독립부서1' FROM DUAL
)
SELECT * FROM DEPT;

부서 테이블은 부서코드(DEPT_CD), 상위부서코드(PARENT_DEPT_CD)를 갖는다. 이미지 트레이닝을 위해 다시 상단의 이미지를 상기해보자. ROOT 노드(1레벨)부터 시작하여 하위의 임원실, 마케팅, IT(2레벨)로 관계를 맺고 있는 구조이다.

CONNECT BY

CONNECT BY를 사용하여 위 데이터의 상하관계를 연결해본다.

SELECT DEPT_CD
     , PARENT_DEPT_CD
     , DEPT_NM
     , LEVEL
     , REGEXP_REPLACE(SYS_CONNECT_BY_PATH(PARENT_DEPT_CD, ' -> '), '^\s+\-\>\s+', '') as "PATH"
  FROM DEPT 
CONNECT BY PRIOR DEPT_CD = PARENT_DEPT_CD

PRIOR절의 컬럼(DEPT_CD)과 연결된 컬럼(PARENT_DEPT_CD)의 데이터를 반복적(RECURSIVE)으로 탐색하여 연결된 데이터를 찾는다. CONNECT BY는 계층적으로 탐색하면서 깊이 혹은 계층레벨인 LEVEL을 갖는다.

위의 결과만 봤을때 의아할 수 있다. PATH 컬럼에 보이는 것 처럼 트리의 모든 노드가 루트(root)노드로서 반복된 결과가 나왔기 때문이다. ( SYS_CONNECT_BY_PATH를 사용하여 CONNECT BY절에 기술한 컬럼의 연결관계를 알수있다. )

START WITH

위 결과에서 원하는 루트노드를 지정하여 계층구조를 나타내려면 START WITH 절을 사용한다.

SELECT DEPT_CD
     , PARENT_DEPT_CD
     , LPAD(' ', (LEVEL-1)*4) || DEPT_NM AS DEPT_NAME
     , LEVEL
     , REGEXP_REPLACE(SYS_CONNECT_BY_PATH(PARENT_DEPT_CD, ' -> '), '^\s+\-\>\s+', '') as "PATH"
     , CONNECT_BY_ISLEAF AS "IS_LEAF"
  FROM DEPT 
 START WITH DEPT_CD = '0000'
CONNECT BY PRIOR DEPT_CD = PARENT_DEPT_CD

결과처럼, DEPT_CD = '0000'을 갖는 루트노드부터 연결된 모든 데이터가 출력되었다.

CONNECT_BY_ISLEAF는 단말노드(하위노드를 갖지않는 노드) 여부를 0, 1로 리턴한다.

ORDER SIBLINGS BY

계층구조 쿼리에서 일반적인 ORDER BY 를 사용하여 정렬하면 계층구조가 틀어진다.

ORDER SIBLINGS BY를 사용하여 계층구조를 유지하며, 같은 레벨(형제, SIBLING)에 대해 정렬할 수 있다.

위 결과에서 DEPT_NM으로 정렬해보자.

SELECT DEPT_CD
     , PARENT_DEPT_CD
     , LPAD(' ', (LEVEL-1)*4) || DEPT_NM AS DEPT_NAME
     , LEVEL
     , REGEXP_REPLACE(SYS_CONNECT_BY_PATH(PARENT_DEPT_CD, ' -> '), '^\s+\-\>\s+', '') as "PATH"
     , CONNECT_BY_ISLEAF AS "IS_LEAF"
  FROM DEPT 
 START WITH DEPT_CD = '0000'
CONNECT BY PRIOR DEPT_CD = PARENT_DEPT_CD
 ORDER SIBLINGS BY DEPT_NM ASC

최하위(단말노드) 계층 데이터 찾기

위에서 살펴본것 처럼 CONNECT_BY_ISLEAF를 사용하여 최하위계층 데이터를 찾을 수 있다.

SELECT DEPT_CD
     , PARENT_DEPT_CD
     , LPAD(' ', (LEVEL-1)*4) || DEPT_NM AS DEPT_NAME
     , LEVEL
     , REGEXP_REPLACE(SYS_CONNECT_BY_PATH(PARENT_DEPT_CD, ' -> '), '^\s+\-\>\s+', '') as "PATH"
     , CONNECT_BY_ISLEAF AS "IS_LEAF"
  FROM DEPT 
 WHERE CONNECT_BY_ISLEAF = 1
 START WITH DEPT_CD = '0000'
CONNECT BY PRIOR DEPT_CD = PARENT_DEPT_CD