오라클 START WITH ... CONNECT BY(계층쿼리) 사용법

 

오라클 계층쿼리
START WITH... CONNECT BY 구문
사용법에 대해 알아본다

 
아래 EMP 테이블은 사원정보를 관리하는 테이블로 사장(king)부터 부하직원까지 모든 직원 정보를 가지고 있다.컬럼을 살펴보면 사원번호(EMPNO)와 관리자번호(MGR)이 보이는데 상급자와 부하직원의 관계가 확인 가능하다.
예를들면 
MILLER의 관리자(MGR=7782)는 CLARK(EMPNO=7782)이고
CLARK의 관리자(MGR=7839)는 KING(EMPNO=7839)
이렇게
KING(사장) -> CLARK(중간관리자) -> MILLER(사원) 트리구조가 만들어진다.
이런 계층구조 결과를 조회할때 사용하는 것이 CONNECT BY 구문이다.

emp 테이블

 
샘플테이블 생성 DDL 참고 : 오라클 EMP, DEPT 샘플 테이블 생성 스크립트

 
다음은 최상위관리자 KING을 시작으로 하위계층을 구하는 SQL이다.
START WITH MGR IS NULL 절이 MGR이 비어있는(NULL) 사장 KING부터 시작

CONNECT BY PRIOR EMPNO = MGR (이전 행의 EMPNO와 현재 행의 MGR)과 동일한 결과를 출력하라.

SELECT LEVEL, EMPNO, MGR, DEPTNO, ENAME
FROM sk.EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
;

계층쿼리 결과

 
출력결과를 이쁘게 만들어 보자
LEVEL 마다 5칸의 공백을 주어 결과를 보기게 만들어 준다
1레벨이면 좌측에 공백 0칸
2레벨이면 좌측에 공백 5칸
3레벨이면 좌측에 공백 10칸

SELECT LEVEL, EMPNO, MGR, DEPTNO, LPAD(' ',(LEVEL-1)*5)||ENAME AS NAME
FROM sk.EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
;

LPAD를 사용한 출력결과 수정

 
마지막으로 정렬까지 해보자
ORDER SIBLINGS BY ENAME 절을 사용하여
같은 LEVEL별 이름순(ENAME)으로 정렬해보자

SELECT LEVEL, EMPNO, MGR, DEPTNO, LPAD(' ',(LEVEL-1)*5)||ENAME AS NAME
FROM sk.EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY ENAME
;

CONNECT SIBLING BY로 결과 정렬

 
 
 
여기까지 기본적이 계층쿼리 사용법에 대해 알아 보았다
 
아래는 기타 여러 상황별 테스트이므로 참고하세요
 

CONNECT BY 구문 앞/뒤 조건에 따른 결과 값 차이

-- CONNECT BY 구문 앞에 조건있는 경우
SELECT LEVEL, EMPNO, MGR, DEPTNO, LPAD(' ',(LEVEL-1)*5)||ENAME AS NAME
FROM sk.EMP
WHERE 1=1
AND DEPTNO = 30
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
;

-- CONNECT BY 구문 뒤에 조건있는 경우
SELECT LEVEL, EMPNO, MGR, DEPTNO, LPAD(' ',(LEVEL-1)*5)||ENAME AS NAME
FROM sk.EMP
WHERE 1=1
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
AND DEPTNO = 30
;

 

CONNECT BY 구문 앞에 조건있는 경우

CONNECT BY 구문 뒤에 조건있는 경우

 
 

PRIOR 위치에 따른 차이

앞에서 나온 예제가 정방향 전개 였다면(상위 -> 하위)
아래 PRIOR 위치는 변경하여 역방향 전개(하위 -> 상위)를 할 수 있다

SELECT LEVEL, EMPNO, MGR, DEPTNO, LPAD(' ',(LEVEL-1)*5)||ENAME AS NAME
FROM sk.EMP
WHERE 1=1
START WITH MGR IS NULL
CONNECT BY EMPNO = PRIOR MGR
;

CONNECT BY 역방향

KING 하나만 나온 이유는 MGR IS NULL 조건으로 KING부터 시작하였고
이전 MGR(공백) = 현재 EMPNO 와 같은 값은 없기 때문이다.
 
시작값을 적절히 변경하여 아래와 같은 형태의 결과를 가져올 수도 있다.

SELECT LEVEL, EMPNO, MGR, DEPTNO, LPAD(' ',(LEVEL-1)*5)||ENAME AS NAME
FROM sk.EMP
WHERE 1=1
START WITH MGR = 7782
CONNECT BY EMPNO = PRIOR MGR
;

역방향 전개