[Oracle] 계층형 쿼리 - START WITH ~ CONNECT BY

 

계층형 쿼리란?

부모, 자식 간의 수직관계를 트리 구조 형태로 보여주는 쿼리

 

회사 조직도를 예시로 들어보자.

 

'회사'는 최상위계층이고, 회사를 중심으로 개발부, 경영지원부, 영업부 세 부서로 나뉜다.

계속해서 부서를 중심으로 하위 계층이 생성되어 있다고 가정하자.

이를 테이블로 나타내면 아래와 같다.

 

부서번호는 고유식별자로 Primary Key가 된다. 

 

 

START WITH

SELECT *
FROM EMP_TB
START WITH 상위부서번호 IS NULL;

회사는 그보다 상위부서가 없기 때문에 상위부서를 NULL로 주며, 부서번호가 NULL 인 것을 찾으면 

최상위 부서를 찾게 되는 원리이다

CONNECT BY PRIOR 하위트리 = 상위트리

폭포를 생각하면 쉽다. 위에서 아래로 내려가는 그런 기본 트리구조를 나타내기 위해서는 PRIOR 바로 뒤에 하위 트리를 적고 = 연산자 뒤에는 상위 트리를 적어준다. 반대를 나타내고 싶다면 PRIOR를 = 연산자 뒤에 적어주면 된다. 그렇게 되면 조회는 아래서부터 위로 상향하며 조회될 것이다. 

CONNECT BY PRIOR 부서번호 = 상위부서번호;

LEVEL

- 계층 구조 쿼리에서 수행 결과의 Depth를 표현하는 가짜칼럼.

단순하게 레코드들이 최상의 레코드로부터 몇 단계 깊이에 있는지를 참고하는 용도로만 사용된다.

SELECT LEVEL,부서번호,상위부서번호,부서이름,사용유무
  FROM  EMP_TB
  START WITH 상위부서번호 IS NULL;
  CONNECT BY PRIOR 부서번호 = 상위부서번호;

 

보기 좀 더 좋게 하기 위해서는 아래 명령어를 추가할 수 있다.

LPAD(' ' , 4*(LEVEL-1)) || 필드명

이 경우 부서이름의 레벨을 보기 좋게 할 수 있다.

  SELECT LEVEL,
  LPAD(' ' , 4*(LEVEL-1)) || 부서이름,
  부서번호,상위부서번호,부서이름,사용유무
  FROM  EMP_TB
  START WITH 상위부서번호 IS NULL;
  CONNECT BY PRIOR 부서번호 = 상위부서번호;

ORDER SIBLINGS BY 

ORDER BY는 그 자체의 알파벳(사전순) 순으로만 정렬된다. 하지만, ORDER SIBLINGS BY를 사용하면 알파벳순만 아닌 계층 구조에 최적화된 상태로 레코드를 가져올 수 있다.

  SELECT LEVEL,
  LPAD(' ' , 4*(LEVEL-1)) || 부서이름,
  부서번호,상위부서번호,부서이름,사용유무
  FROM  EMP_TB
  START WITH 상위부서번호 IS NULL;
  CONNECT BY PRIOR 부서번호 = 상위부서번호;
  ORDER SIBLINGS BY 부서번호;