계층구조 쿼리의 활용

Oracle10g 부터 CONNECT BY 절에서 제공하는 CONNECT_BY_ROOT, SYS_CONNECT_BY_PATH, CONNECT_BY_ISLEAF 기능에 대해서 알아보자

CONNECT_BY_ROOT

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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 정보를 쉽게 얻어 올 수 있다.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

-- 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 정보가 나오도록 작성 할 수 있다

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

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

분석:순서대로 보기

SELECT LEVEL, SUBSTR(SYS_CONNECT_BY_PATH(ename, ','), 2) path

  FROM emp

 WHERE CONNECT_BY_ISLEAF = 1 //3번 로우의 레벨들만

 START WITH mgr IS NULL // 1번 MGR이 널이 아닌것을 기준인가?

CONNECT BY PRIOR empno = mgr; //2번 EMPNO=MGR까지

ORDER SIBLINGS BY

계층구조 쿼리에서 상관관계를 유지하면서 정렬을 할 수 있게 해준다. 아래 예제를 가지고 ORDER SIBLINGS BY와 ORDER BY로 테스트 해보자

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55

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

분석:순서대로 보기

SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename,

       ename ename2, empno, level

  FROM emp

 START WITH job='PRESIDENT' //1번 PRESIDENT를 기준으로 정렬하고.

CONNECT BY NOCYCLE PRIOR empno=mgr //2번 사번에서 MGR로 LEVEL이 만들어 지는데 NOCYCLE이라

 ORDER SIBLINGS BY ename2; //3번 이데이커들의 값의 정렬을 ENAME2로 함 레벨은 유지하면서 정렬됨.

 

태그

• CONNECT_BY_ROOT SYS_CONNECT_BY_PATH CONNECT_BY_ISLEAF CONNECT BY START WITH PRIOR ORDER SIBLINGS BY계층구조쿼리

문서에 대하여

• - 강좌 URL : http://www.gurubee.net/lecture/1903
• - 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• - 구루비의 모든 강좌는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.