오라클 상관관계 쿼리 level

기본문 :

select * from
start with classid = ''
connect by prior parentclassid = classid

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          

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