3.2. 계층구조 쿼리의 활용

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

출처 : http://www.gurubee.net/lecture/1903