[OracleDB] 계층형 쿼리

계층형 쿼리

이름 그대로 결과를 계층형으로 조회할 수 있는 쿼리이다. 회사의 상사 - 부하 관계, 또는 게시글의 댓글 - 대댓글 등의 상하 관계가 존재하는 데이터를 계층형으로 조회할 때 사용할 수 있다.

 

 

계층형 쿼리의 기본 구성

 SELECT *
   FROM EMPLOYEES
  START WITH MANAGER_ID IS NULL
CONNECT BY [NOCYCLE] PRIOR EMPLOYEE_ID = MANAGER_ID
  ORDER SIBLINGS BY EMPLOYEE_ID;

 

START WITH

계층형 쿼리를 어디서부터 시작할지 정의한다. 위 예시처럼 MANAGER_ID IS NULL 로 지정하면 상사가 없는 사원, 즉 가장 높은 사원부터 계층을 생성하기 시작한다.

 

 

CONNECT BY ... PRIOR

계층형 쿼리의 진행 방향을 정의한다. PRIOR의 위치에 따라 계층은 위에서 아래로, 아래에서 위로 연결된다. 사장부터 시작해서 아래 사원들까지 순방향으로 조회할 수도 있고 특정 사원에서부터 그 상사들을 거슬러오르는 역방향 계층으로도 조회할 수도 있다.

 

위 예시처럼 CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID 로 지정하면 앞선 행의 사원 번호가 현재 행의 상사 번호와 같은 행끼리 연결한다. 즉, 위 쿼리는 위에서 아래로 연결되는 순방향 계층이다.

 

반대로 PRIOR MANAGER_ID = EMPLOYEE_ID 로 지정하면 앞선 행의 상사 번호가 현재 행의 사원 번호와 동일할 때, 즉 계층이 역방향으로 연결된다.

 

PRIOR의 개념이 잘 이해되지 않는다면 프자부순(PRIOR 자식 = 부모 : 순방향), 프부자역(PRIOR 부모 = 자식 : 역방향)으로 외우면 편하다.

 

 

NOCYCLE

계층형 쿼리의 루프를 방지한다. 부모행과 자식행이 양방향으로 연결되면 특정 행들만 중복되어 출력될 수 있는데 NOCYCLE을 사용하면 이미 전개한 행에 대해서는 중복하여 출력되지 않도록 한다.

 

ORA-01436: CONNECT BY loop in user data

 

계층형 쿼리에서 루프가 발생할 경우 위와 같은 오류가 발생한다. 이 때 NOCYCLE 옵션을 사용하면 루프 없이 데이터를 조회할 수 있다.

 

 

ORDER SIBLINGS BY

기존 ORDER BY에 SIBLINGS가 추가되었다. SIBLINGS는 형제라는 뜻으로 계층형 쿼리에서 형제 노드 간의 정렬을 정의할 수 있다. 예를들어 사장 아래에 몇 명의 부장들이 있는데 이 부장들을 사원 번호 순으로 정렬할지, 이름 순으로 정렬할지 지정할 수 있다.

 

 

계층형 쿼리에서 사용할 수 있는 의사 코드와 특수 함수

계층형 쿼리에서는 계층 단계를 가리키는 LEVEL이라는 의사(Pseudo; 가짜) 컬럼과 각 노드의 상태나 관계를 조회하는 함수를 사용할 수 있다.

 

SELECT LPAD(' ', (LEVEL - 1) * 2, ' ') || EMPLOYEE_ID AS id,
       FIRST_NAME || ' ' || LAST_NAME AS name,
       LEVEL AS depth,
       CONNECT_BY_ISLEAF AS leaf,
       CONNECT_BY_ROOT(EMPLOYEE_ID) AS root,
       LTRIM(SYS_CONNECT_BY_PATH(EMPLOYEE_ID, '-'), '-') AS path
  FROM EMPLOYEES
 START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
 ORDER SIBLINGS BY EMPLOYEE_ID;

 

 

 

LEVEL

현재 노드의 계층 단계를 가리킨다. Oracle의 LEVEL은 1부터 시작하며 하위 노드로 내려가면서 1씩 증가한다.

 

 

CONNECT_BY_ISLEAF

자식 노드 여부를 확인할 수 있다. 자식 노드가 있을 경우 0, 없을 경우 1을 반환한다.

 

 

CONNECT_BY_ROOT (컬럼)

현재 노드의 최상위 노드의 특정 컬럼 값을 가져온다.

 

 

SYS_CONNECT_BY_PATH(컬럼, 구분자)

현재 노드의 계층 경로를 지정한 컬럼 값과 구분자로 표현한다. 위 예시의 경우 컬럼은 EMPLOYEE_ID, 구분자는 '-' 를 사용하여 계층 경로(path)가 표현된 것을 알 수 있다.

 

이 함수의 경우 결과 값의 맨 앞에 구분자가 붙는데 이를 제거하기 위해 LTRIM을 사용했다.