Oracle10g 부터 CONNECT BY 절에서 제공하는 CONNECT_BY_ROOT, SYS_CONNECT_BY_PATH, CONNECT_BY_ISLEAF 기능에 대해서 알아보자
CONNECT_BY_ROOT
계층구조 쿼리에서 LEVEL이 0인 최상위 로우의 정보를 얻어 올 수 있다.
SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,
CONNECT_BY_ROOT empno "Root empno", level
FROM emp
START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr;
ENAME EMPNO Root empno LEVEL
------------------ ------- ----------- -------
KING 7839 7839 1
JONES 7566 7839 2
SCOTT 7788 7839 3
ADAMS 7876 7839 4
FORD 7902 7839 3
SMITH 7369 7839 4
CONNECT_BY_ISLEAF
계층구조 쿼리에서 로우의 최하위 레벨(Leaf) 여부를 반환한다. 최하이 레벨이면 1, 아니면 0
SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,
CONNECT_BY_ISLEAF "leaf", level
FROM emp
START WITH job='PRESIDENT'
CONNECT BY NOCYCLE PRIOR empno=mgr;
ENAME EMPNO leaf LEVEL
-------------------- ---------- ---------- ----------
KING 7839 0 1
JONES 7566 0 2
SCOTT 7788 0 3
ADAMS 7876 1 4
FORD 7902 1 3
BLAKE 7698 0 2
MARTIN 7654 1 3
TURNER 7844 1 3
JAMES 7900 1 3
CLARK 7782 0 2
MILLER 7934 1 3
SYS_CONNECT_BY_PATH
계층구조 쿼리에서 현재 로우 까지의 PATH 정보를 쉽게 얻어 올 수 있다.
-- SQL*Plus에서만 깔끔하게 보기위해서
COL PATH FORMAT A40
-- SYS_CONNECT_BY_PATH 예제
SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,
SYS_CONNECT_BY_PATH(ename, '/') "PATH"
FROM emp
START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr;
ENAME EMPNO PATH
-------------------- ---------- ---------------------------
KING 7839 /KING
JONES 7566 /KING/JONES
SCOTT 7788 /KING/JONES/SCOTT
ADAMS 7876 /KING/JONES/SCOTT/ADAMS
FORD 7902 /KING/JONES/FORD
BLAKE 7698 /KING/BLAKE
MARTIN 7654 /KING/BLAKE/MARTIN
TURNER 7844 /KING/BLAKE/TURNER
JAMES 7900 /KING/BLAKE/JAMES
CLARK 7782 /KING/CLARK
MILLER 7934 /KING/CLARK/MILLER
아래와 같이 Leaf Node만 전체 PATH 정보가 나오도록 작성 할 수 있다
-- SQL*Plus에서만 깔끔하게 보기위해서
COL PATH FORMAT A40
SELECT LEVEL, SUBSTR(SYS_CONNECT_BY_PATH(ename, ','), 2) path
FROM emp
WHERE CONNECT_BY_ISLEAF = 1
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;
LEVEL PATH
--------- -------------------------
4 KING,JONES,SCOTT,ADAMS
3 KING,JONES,FORD
3 KING,BLAKE,MARTIN
3 KING,BLAKE,TURNER
3 KING,BLAKE,JAMES
3 KING,CLARK,MILLER
ORDER SIBLINGS BY
계층구조 쿼리에서 상관관계를 유지하면서 정렬을 할 수 있게 해준다. 아래 예제를 가지고 ORDER SIBLINGS BY와 ORDER BY로 테스트 해보자
-- SQL*Plus에서만 깔끔하게 보기위해서
COL ename FORMAT A25
COL ename2 FORMAT A10
-- ORDER SIBLINGS BY 예
-- 정렬이 정상적으로 수행된 것을 확인 할 수 있다.
SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename,
ename ename2, empno, level
FROM emp
START WITH job='PRESIDENT'
CONNECT BY NOCYCLE PRIOR empno=mgr
ORDER SIBLINGS BY ename2;
ENAME ENAME2 EMPNO LEVEL
-------------------- ---------- ---------- ----------
KING KING 7839 1
BLAKE BLAKE 7698 2
JAMES JAMES 7900 3
MARTIN MARTIN 7654 3
TURNER TURNER 7844 3
CLARK CLARK 7782 2
MILLER MILLER 7934 3
JONES JONES 7566 2
FORD FORD 7902 3
SCOTT SCOTT 7788 3
ADAMS ADAMS 7876 4
-- ORDER BY 예
-- 정렬이 이상하게 수행된 것을 확인 할 수 있다.
SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename,
ename ename2, empno, level
FROM emp
START WITH job='PRESIDENT'
CONNECT BY NOCYCLE PRIOR empno=mgr
ORDER BY ename2;
ENAME ENAME2 EMPNO LEVEL
-------------------- ---------- ---------- ----------
ADAMS ADAMS 7876 4
BLAKE BLAKE 7698 2
CLARK CLARK 7782 2
FORD FORD 7902 3
JAMES JAMES 7900 3
JONES JONES 7566 2
KING KING 7839 1
MARTIN MARTIN 7654 3
MILLER MILLER 7934 3
SCOTT SCOTT 7788 3
TURNER TURNER 7844 3