[SQL] 오라클 계층형 질의 (Hierarchical Query)

계층형 질의 (Hierarchical Query)

계층형 데이터를 조회할 때 사용하는 쿼리.

계층형 데이터란, 동일 테이블에 상위와 하위 데이터가 포함된 데이터를 의미한다. 예를 들면, 사원-관리자  테이블인 경우.

 

오라클 계층형 질의

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

 

• START WHIT 절은 계층 구조 전개의 시작 위치를 저장하는 구문이다. 루트를 지정한다. (액세스)
• CONNECT BY 절은 다음에 전개될 자식 데이터를 지정하는 구문이다. 자식 데이터는 CONNECT BY 절에 주어진 조건을 만족해야 한다.(조인)
• PRIOR : CONNECT BY 절에 사용되며, 현재 읽은 컬럼을 지정한다. PRIOR 자식 = 부모 형태를 사용하면 계측 구조에서 부모 데이터에서 자식 데이터 (부모 -> 자식) 방향으로 전개하는 순방향 전개를 한다. 그리고 PRIOR 부모 = 자식 형태를 사용하면 반대로 자식 데이터에서 부모 데이터(자식 -> 부모) 방향으로 전개하는 역방향 전개를 한다. 
• NOCYCLE : 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이것을 가리켜 사이클(cycle)이 형성되었다라고 말한다. 사이클이 발생한 데이터는 런타임 오류가 발생한다. 그렇지만 NOCYCLE 를 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다. 
• ORDER SIBLINGS BY : 형제 노드(동일 Level) 사이에서 정렬을 수행한다. 
• WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다.(필터링)

 

오라클은 계정형 질의를 사용할 때 다음과 같은 가상 컬럼(Pseudo Column)을 제공한다. 

• LEVEL : 루트 데이터면 1, 그 하위 데이터면 2. 리프(leaf)데이터까지 1씩 증가한다. 
• CONNECT_BY_ISLEAF : 전개 과정에서 해당 데이터가 리프 데이터면 1, 그렇지 않으면 0이다.
• CONNECT_BY_ISCYCLE : 전개 과정에서 자식을 갖는데, 해당 데이터가 조상으로서 존재하면 1, 그렇지 않으면 0이다. 여기서 조상이란 자신으로부터 루트까지의 경로에 존재하는 데이터를 말한다. CYCLE 옵션을 사용했을 때만 사용 가능하다. 

 

샘플 데이터 조회하는 예제 

<샘플 데이터>

사원	관리자
A
B	A
C	A
D	C
E	C

 

 

관리자 -> 사원 방향의 전개이기 때문에 순방향 전개의 예제.

A -> B, C -> D, E

SELECT     LEVEL, EMPNO, MGR, CONNECT_BY_ISLEAF 리프
FROM       EMP
START WITH MGR IS NULL 
CONNECT BY PRIOR EMPNO = MGR ;

 

 

 

사원 D부터 상위 관리자를 찾는 역방항 전개의 예제. 

D -> C -> A

SELECT     LEVEL, EMPNO, MGR, CONNECT_BY_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 컬럼

 

예제

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

 

 

 

 

 

 

kdata 한국데이터진흥원에서 출간한 SQL 전문가 가이드 2013 Edition을 요약했습니다.