[Oracle] 계층형 질의

계층형 질의

 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의를 사용합니다. 계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말합니다. 예를 들어, 사원 테이블에서는 사원들 사이에 상위 사원(관리자)과 하위 사원 관계가 존재하고 조직 테이블에서는 조직들 사이에 상위 조직과 하위 조직 관계가 존재합니다.

  

SELECT...  
FROM 테이블
WHERE condition AND condition...
START WITH condition
CONNECT BY [NOCYCLE] condition AND condition...
[ORDER SIBLINGS BY column, column, ...]

- START WITH 절은 계층 구조 전개의 시작 위치를 저장하는 구문입니다. 즉, 루트 데이터를 지정합니다.

- CONNECT BY 절은 다음에 전개될 자식 데이터를 지정하는 구문입니다. 자식 데이터는 CONNECT BY 절에 주어진 조건을 만족해야 합니다.

- PRIOR : CONNECT BY 절에 사용되며, 현재 읽는 컬럼을 지정합니다. PRIOR 자식 = 부모 형태를 사용하면 계층 구조에서 부모 데이터에서 자식 데이터(부모 -> 자식) 방향으로 전개하는 순방향 전개를 합니다. 그리고 PRIOR 부모 = 자식 형태를 사용하면 반대로 자식 데이터에서 부모 데이터(자식 -> 부모) 방향으로 전개하는 역방향 전개를 합니다.

- NOCYCLE : 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이것을 가리켜 사이클(cycle)이 형성되었다고 말합니다. 사이클이 발생한 데이터는 런타임 오류가 발생합니다. 그렇지만 NOCYCLE를 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않습니다.

- ORDER SIBLINGS BY : 형제 노드(동일 LEVEL) 사이에서 정렬을 수행합니다.

- WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출합니다.

계층형 질의에서 사용되는 가상 컬럼

LEVEL - 루트 데이터이면 1, 그 하위 데이터이면 2입니다. 리프(Leaf) 데이터까지 1씩 증가합니다.

CONNECT_BY_ISLEAF - 전개 과정에서 해당 데이터가 리프 데이터이면 1, 그렇지 않으면 0입니다.

CONNECT_BY)ISCYCLE - 전개 과정에서 자식을 갖는데, 해당 데이터가 조상으로서 존재하면 1, 그렇지 않으면 0 입니다. 여기서 조상이란 자신으로부터 루트까지의 경로에 존재하는 데이터를 말합니다. CYCLE 옵션을 사용했을 때만 사용할 수 있습니다. 

 

순방향 계층형 질의 

SELECT LEVEL
          , LPAD(' ', 4 * (LEVEL - 1)) || EMPNO AS 사원
          , MGR AS 관리자
          , CONNECT_BY_ISLEAF AS ISLEAF
  FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;                                                                                                                                                                              

역방향 계층형 질의

SELECT LEVEL
          , LPAD(' ', 4 * (LEVEL - 1)) || EMPNO AS 사원
          , MGR AS 관리자
          , CONNECT_BY_ISLEAF AS ISLEAF
  FROM EMP
START WITH EMPNO = '7876'
CONNECT BY PRIOR MGR = EMPNO;                                                                                                                                                                              

계층형 질의에서 사용되는 함수

SYS_CONNECT_BY_PATH - 루트 데이터부터 현재 전개할 데이터까지의 경로를 표시합니다. 

 - 사용법 : SYS_CONNECT_BY_PATH(컬럼, 경로 분리자)

CONNECT_BY_ROOT - 현재 전개할 데이터의 루트 데이터를 표시합니다. 단한 연산자입니다. 

 - 사용법  : CONNECT_BY_ROOT 컬럼 

SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT를 사용한 질의

SELECT CONNECT_BY_ROOT(EMPNO) AS 루트사원
          , SYS_CONNECT_BY_PATH(EMPNO, '/') AS 경로
          , EMPNO AS 사원
          , MGR AS 관리자
   FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;