ORACLE|Oracle10g - CONNECT_BY_ROOT, SYS_CONNECT_BY_PATH, CONNECT_BY_ISLEAF

Oracle10g에서 CONNECT BY의 새로운 기능들

  오라클팁의 "계층 구조의 조회(Hierarchical Queries)" 강좌에서 CONNECT BY 와 START WITH를 상관관계 쿼리에 대해서 소개를 했었습니다.

  Oracle10g 부터 CONNECT BY 절에서 제공하는 CONNECT_BY_ROOT, SYS_CONNECT_BY_PATH, CONNECT_BY_ISLEAF 기능에 대해서 알아보겠습니다.

상관관계 쿼리 예제

  아래 예제와 같이 계층구조로 조회 할 수 있습니다.

 
SQL> SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno
     FROM emp
     START WITH job='PRESIDENT'
     CONNECT BY PRIOR empno=mgr;
 
ENAME                     EMPNO       MGR        JOB
-------------------- ---------- ---------- ---------
KING                       7839             PRESIDENT
    JONES                  7566      7839   MANAGER
        SCOTT              7788      7566   ANALYST
            ADAMS          7876      7788   CLERK
        FORD               7902      7566   ANALYST
            SMITH          7369      7902   CLERK
   
    

CONNECT_BY_ROOT

  상관관계 쿼리에서 LEVEL이 0인 최상위 로우의 정보를 얻어 올 수 있습니다.

 
SQL> 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  
    

SYS_CONNECT_BY_PATH

  상관관계 쿼리에서 현재 로우 까지의 PATH 정보를 쉽게 얻어 올 수 있습니다.

 
SQL> COL path FORMAT A40
 
SQL> 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
            SMITH         7369    /KING/JONES/FORD/SMITH  
    

CONNECT_BY_ISLEAF

  상관관계 쿼리에서 로우의 최하위 레벨 여부를 반환 합니다.

 
SQL> 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          0          3
            SMITH         7369          1          4  
    

#출처: http://www.oracleclub.com/lecture/1903