Where The Streets Have No Name

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

Developement/DataBase

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

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

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

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

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

'Developement/DataBase' Related Articles more