Oracle10g에서 CONNECT BY 순환쿼리

Oracle10g에서 CONNECT BY의 새로운 기능들 | oracle & mssql  2006.09.06 12:37 
 
 우스(rius33)  성실멤버   http://cafe.naver.com/itea11/236 
 
 

Oracle10g 부터  CONNECT BY 절에서 제공하는 CONNECT_BY_ROOT, SYS_CONNECT_BY_PATH, CONNECT_BY_ISLEAF 

기능에 대해서 알아보겠습니다.
 
 
SQLPLUS scott/tiger
 
SQL>SET LINESIZE 100
SQL>SET PAGESIZE 100
SQL>COL ename FORMAT A20
 
 
◈ 상관관계 쿼리 예제
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

-- 여기서  START WITH job='PRESIDENT'부분을 START WITH EMPNO = :AS_EMPNO로 바꿔서

해당 사번에 대한 계층구조를 구할수도 있음..

⊙ START WITH와 CONNECT BY를 이용해 데이터를 계층적인 순서로  조회할 수 있습니다.
 
 
◈ START WITH
 
 - 계층 질의의 루트(부모행)로 사용될 행을 지정 합니다..
 - 서브쿼리를 사용할 수도 있습니다.
 
 
◈ CONNECT BY
 
 - 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 합니다.
 - 보통 PRIOR 연산자를 많이 사용 합니다..
 - 서브쿼리를 사용할 수 없습니다..
 
 
 
◈ CONNECT BY의 실행순서는 다음과 같습니다.
 
 - 첫째 START WITH절
 - 둘째 CONNECT BY 절
 - 세째 WHERE 절 순서로 풀리게 되어있습니다.

◈ 데이터가 많아질 경우....
 
 - 첫째로 풀리는 START WITH job='PRESIDENT' job 컬럼에 index가 생성되어 있지 않는다면
    속도를 보장할 수 없습니다.
 
 - 그리고 둘째로 풀리는 CONNECT BY PRIOR empno = mgr 역시 PRIOR 쪽의 컬럼값이 상수가
   되기 때문에 MGR컬럼에 index를 생성하여야 CONNECT BY의 속도를 보장할 수 있습니다.
  
 - 계층구조를 CONNECT BY, START WITH로 풀면 부분범위 처리가 불가능하고 Desc으로
   표현하기가 어렵 습니다.

 

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

 - 상관관계 쿼리에서 로우의 최하위 레벨 여부를 반환 합니다.
 
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.nettop.pe.kr/nettop/nettopjsp/board/view.jsp?table=jsp_nettop_study&nettop_ref=oracle&Page=1&board_idx=1594&search=&search_str=>