오라클 계층쿼리 두번째!! CONNECT BY PRIOR

Oracle의 CONNECT BY 구문은 계층 구조를 표현하고, 쉽게 탐색할 수 있게 하는 강력한 도구입니다.

예를 들어, 직원 테이블이 있다고 가정해봅시다. 각 직원은 자신의 직속 상사가 있으며, 이 관계를 이용해 계층 구조를 형성할 수 있습니다.

아래에서는 계층적 데이터를 다루는 방법과 CONNECT BY 구문을 쉽게 사용하는 방법을 살펴보겠습니다.

 

1. 계층적 쿼리란?

계층적 쿼리는 데이터를 부모-자식 관계로 표현하는 쿼리입니다. 예를 들어, 다음과 같은 직원 테이블을 생각해볼 수 있습니다.

직원	직속상사	직급
개똥		사장
영희	개똥	부장
순희	영희	과장
철수	순희	대리
길동	순희	대리

여기서 개똥은 사장이며, 영희는 개똥의 부하인 부장입니다. 순희는 영희의 부하, 철수와 길동은 순희의 부하로 계층 구조를 이루고 있습니다.

 

2. CONNECT BY를 사용하여 계층 구조 쿼리하기

우선, CONNECT BY 구문은 계층적 관계를 표현하기 위해 사용됩니다. 이 구문에서 START WITH 는 루트 노드를 지정하고, CONNECT BY PRIOR 는 상하위 관계를 설정합니다.

예제: 직원 테이블의 계층 구조 출력

사장을 기준으로 직원들의 계층 구조를 출력하고 싶다면 다음과 같이 쿼리할 수 있습니다.

SELECT 
    LPAD(' ', (LEVEL - 1) * 2, ' ') || 직원 AS 직원, 
    직급
FROM 직원
START WITH 직속상사 IS NULL
CONNECT BY PRIOR 직원 = 직속상사
ORDER SIBLINGS BY 직원;

 

결과:

개똥       사장
  영희     부장
    순희   과장
      철수 대리
      길동 대리

여기서 LEVEL은 계층의 깊이를 나타내며, 루트 노드는 1로 표시됩니다. ORDER SIBLINGS BY 구문을 추가하여 동일한 계층에 속한 직원들을 알파벳순으로 정렬할 수 있습니다.

 

3. 루트에서 끝까지의 경로 표시하기: SYS_CONNECT_BY_PATH

루트 노드에서 시작해 각 직원에 이르는 경로를 보고 싶다면 SYS_CONNECT_BY_PATH 함수를 사용합니다. 예를 들어, 개똥에서 출발해 철수에 이르는 경로를 나타낼 수 있습니다.

SELECT 
    LPAD(' ', (LEVEL - 1) * 2, ' ') || 직원 AS 직원, 
    직급, 
    SYS_CONNECT_BY_PATH(직원, ' -> ') AS 경로
FROM 직원
START WITH 직속상사 IS NULL
CONNECT BY PRIOR 직원 = 직속상사;

 

결과:

개똥         사장       -> 개똥
  영희       부장       -> 개똥 -> 영희
    순희     과장       -> 개똥 -> 영희 -> 순희
      철수   대리       -> 개똥 -> 영희 -> 순희 -> 철수
      길동   대리       -> 개똥 -> 영희 -> 순희 -> 길동

SYS_CONNECT_BY_PATH를 사용하면 각 노드가 루트에서 현재 위치까지 어떤 경로를 통해 도달했는지 쉽게 볼 수 있습니다.

 

4. 특정 깊이까지만 출력하기: LEVEL

LEVEL 조건을 사용해 특정 계층까지만 조회할 수 있습니다. 예를 들어, 부장 직급까지만 보이도록 LEVEL <= 2 조건을 사용할 수 있습니다.

SELECT 
    LPAD(' ', (LEVEL - 1) * 2, ' ') || 직원 AS 직원, 
    직급, 
    LEVEL
FROM 직원
START WITH 직속상사 IS NULL
CONNECT BY PRIOR 직원 = 직속상사
AND LEVEL <= 2;

 

결과:

개똥     사장     1
  영희   부장     2

이 쿼리는 CONNECT BY 구문 내에서 LEVEL을 제한하기 때문에 쿼리 성능이 향상됩니다.

 

5. 상하 관계를 반대로 탐색하기

기본적으로 CONNECT BY PRIOR를 사용해 부모에서 자식 방향으로 탐색합니다. 하지만 상하 관계를 반대로 탐색하려면 PRIOR 위치를 반대로 지정할 수 있습니다.

예를 들어, 철수에서 시작하여 상위 관리자를 쭉 따라가려면 다음과 같이 쿼리할 수 있습니다.

SELECT 
    LPAD(' ', (LEVEL - 1) * 2, ' ') || 직원 AS 직원, 
    직급
FROM 직원
START WITH 직원 = '철수'
CONNECT BY PRIOR 직속상사 = 직원;

 

결과:

철수     대리
  순희   과장
    영희 부장
      개똥 사장

이와 같이, 자식에서 부모 관계로 쿼리할 수 있으며, PRIOR 키워드를 통해 탐색 방향을 자유롭게 지정할 수 있습니다.