Oracle Advanced SQL 강좌
계층구조 쿼리의 활용 30 16 99,999+

by 구루비 CONNECT_BY_ROOT SYS_CONNECT_BY_PATH CONNECT_BY_ISLEAF CONNECT BY START WITH PRIOR ORDER SIBLINGS BY 계층구조쿼리 [2006.04.12]


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 

- 강좌 URL : http://www.gurubee.net/lecture/1903

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 김경준 [2006.06.02 17:32:53]
good..9i에 없는게 아쉽네여..

by ima [2007.01.25 14:41:55]
SYS_CONNECT_BY_PATH는 되는데요.. 9i라두...
다른것도 되면 좋을텐데..

by 텐지사랑 [2007.05.09 00:01:37]
텐지로 올린 후에도 특별히 sql문 다르게 사용해보질 않았는데,
이 기능은 유용하게 잘 쓰겠네요.
감사합니다.

by 김한수 [2007.08.06 15:34:15]
오호 좋다...

by 장태길 [2007.09.20 18:49:36]
넘좋다..
흐흐흐

by 된장 [2008.05.14 08:19:02]
오호호...좋네요..잘 봤습니당..

by 장형근 [2008.05.21 17:09:47]
감사합니다.

by 마농 [2009.03.03 11:11:57]
순환구조로 에러가 발생하여 계층쿼리를 사용하지 못할 경우
NOCYCLE 을 주면 순환구조가 발생하기 전까지 계층구조를 전개한다.
NOCYCLE 과 함께 CONNECT_BY_ISCYCLE 을 사용하면 순환구조가 발생여부를 알수 있다.
SELECT cd, pcd, LEVEL, CONNECT_BY_ISCYCLE
FROM t
START WITH pcd IS NULL
CONNECT BY NOCYCLE PRIOR cd = pcd

by 샤샤 [2010.04.12 19:25:49]
오잉~~ 짐 이걸 열심히 찾구있었어요~~ ^6

감사~~^^

by 마루~치 [2010.08.30 16:18:37]
저역시 요기능을 찾고있었습죠.. 감사합니다.
유용한쿼리입니다. 베리쿼리~ㅎ

by 물개 [2012.04.18 09:24:12]
와 정말 알면 알수록 편한 기능이 많네요 ㅠㅠ

by 고기브페 [2012.08.20 11:49:48]
오... 집가서 한번 해봐야 겠네요..
회사는 8i .. ㅠ ㅠ

by 조희식 [2013.04.16 14:44:29]

편리한 기능들이 많네요 ^^

by 정존 [2013.07.12 13:45:33]
감사합니다.

by 정진우 [2014.02.25 10:02:32]
감사합니다. ! PATH 정보를 알아내는게 정말 유익한 공부가 되었습니다.

by 비비엘소프트 [2016.03.30 10:55:52]

<오탈자 정정건의>

계층구조 쿼리에서 로우의 최하위 레벨(Leaf) 여부를 반환한다. 최하 레벨이면 1, 아니면 0

계층구조 쿼리에서 로우의 최하위 레벨(Leaf) 여부를 반환한다. 최하 레벨이면 1, 아니면 0

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입