ORACLE 계층형쿼리

계층형 쿼리란?

계층형 구조란 상하 수직관계의 구조로 이루어진 형태를 말한다.
계층형 쿼리는 데이터를 이러한 계층형 구조로 반환하는 쿼리를 말한다.
START WITH … CONNECT BY 절로 생성할 수 있다.

계층형 쿼리 기본개념

1. START WITH

START WITH 절엔 시작 조건을 기술한다.
즉, 데이터를 연결할 때 가장 최상위에 들어갈 데이터를 선택해야한다.
회사 소속의 전 부서를 보고 싶다면 최상위 데이터는 ‘회사’가 될 것이고, 영업지원본부 소속의 팀을 보고싶다면 최상위 데이터는 ‘영업지원본부’가 될 것이다

2. CONNECT BY ~ PRIOR

CONNECT BY ~ 절엔 연결 조건을 기술한다.
최상위 데이터는 START WITH 절에서 지정했으니 다음 구문에선 상위(부모), 하위(자식)의 결합기준을 지정한다.

예시

예를들어, 다음과 같이 부서와 상하위 부서 관계를 관리하는 ‘부서정보’ 테이블이 있다고 가정하자.

부서	상위부서
회사
사외이사	회사
사장	회사
영업지원본부	사장
인사팀	영업지원본부
총무팀	영업지원본부
재무부	사장
자금팀	재무부
회계팀	재무부

위와 같은 데이터에서 ‘회사’를 기준으로 소속된 하위부서 리스트를 조회하고 싶을때, 계층형 쿼리를 사용하여 출력하도록 하자.

1. START WITH

‘회사’를 기준으로 소속된 하위부서 리스트를 조회하고 싶을때 최상위 데이터는 당연히 ‘회사’이다.
START WITH 부서 = ‘회사’ 또는 START WITH 상위부서 IS NULL 로 상위 데이터를 지정해준다.

2. CONNECT BY ~ PRIOR

현재의 행과 다른행을 결합해야 하기에, 이것을 구분하기 위해서 PRIOR 라는 것을 함께 사용 한다.
PRIOR의 의미는 '최초의, 이전의' 라는 의미를 가지는데, 현재의 행의 값을 의미 한다.
START WITH 부서 = ‘회사’ CONNECT BY PRIOR 부서 = 상위부서 라고 한다면, '회사'가 존재하는 행에서 그 행의 ‘부서’ 와 다른행의 ‘상위부서’를 결합하는 방식이다.

위의 과정을 거치면 다음과 같이 ‘회사’ 기준으로 소속부서를 모두 조회하는 쿼리가 완성된다.

 SELECT 부서
   FROM 부서정보
  START WITH 부서 = '회사'
CONNECT BY PRIOR 부서 = 상위부서

계층형쿼리 수행순서

실행순서	QUERY  문장	내용
1	FROM ~
2	START WITH ~	계층 구조 전개의 루트(시작위치)를 지정
3	CONNECT BY [PRIOR, NOCYCLE] ~	부모, 자식 데이터 간의 결합 기준 지정. - 1. PRIOR 자식 = 부모 계층구조가 부모 > 자식 방향으로 전개되는 순방향 전개 - 2. PRIOR 부모 = 자식 계층구조가 자식 > 부모 방향으로 전개되는 역방향 전개 - 3. NOCYCLE 데이터를 전개하면서 이미 나타났던 동일한 데이터가 다시 나타난다면 이것을 가리켜 사이클(Cycle)이 형성 되었다고 한다. 사이클이 발생하면 런타임 오류가 발생한다. 하지만 NOCYCLE 구문을 추가하면 사이클 발생 이후의 데이터는 전개하지 않는다.
4	ORDER SIBLINGS BY ~	형제 노드 (동일 LEVEL) 사이에서 정렬을 수행한다.
5	WHERE ~	모든 전개를 수행한 후에 지정된 조건에 만족하는 데이터를 필터링 한다.
6	SELECT ~