2012-02-14 level,connect_by_isleaf,connect_by_iscycle

nat@ORCL-10.1.16.14>select lpad(' ',4*(level-1))||e.empno empno,e.mgr,connect_by_isleaf,connect_by_iscycle,level from emp e start with e.mgr is null c
onnect by nocycle prior e.empno=e.mgr;

EMPNO                  MGR CONNECT_BY_ISLEAF CONNECT_BY_ISCYCLE      LEVEL
-------------------- ----- ----------------- ------------------ ----------
7839                                       0                  0          1
    7566              7839                 0                  0          2
        7788          7566                 0                  0          3
            7876      7788                 1                  0          4
        7902          7566                 0                  0          3
            7369      7902                 1                  0          4
    7698              7839                 0                  0          2
        7499          7698                 1                  0          3
        7521          7698                 1                  0          3
        7654          7698                 1                  0          3
        7844          7698                 1                  0          3
        7900          7698                 1                  0          3
    7782              7839                 0                  0          2
        7934          7782                 1                  0          3

nat@ORCL-10.1.16.14>update emp set mgr=7876 where mgr is null
  2  ;

1 row updated.

nat@ORCL-10.1.16.14>select lpad(' ',4*(level-1))||e.empno empno,e.mgr from emp e start with e.empno=7839 connect by prior e.empno=e.mgr;
ERROR:
ORA-01436: CONNECT BY loop in user data

nat@ORCL-10.1.16.14>select lpad(' ',4*(level-1))||e.empno empno,e.mgr,connect_by_isleaf,connect_by_iscycle,level from emp e start with e.empno = 7839
connect by nocycle prior e.empno=e.mgr;

EMPNO                  MGR CONNECT_BY_ISLEAF CONNECT_BY_ISCYCLE      LEVEL
-------------------- ----- ----------------- ------------------ ----------
7839                  7876                 0                  0          1
    7566              7839                 0                  0          2
        7788          7566                 0                  0          3
            7876      7788                 1                  1          4
        7902          7566                 0                  0          3
            7369      7902                 1                  0          4
    7698              7839                 0                  0          2
        7499          7698                 1                  0          3
        7521          7698                 1                  0          3
        7654          7698                 1                  0          3
        7844          7698                 1                  0          3
        7900          7698                 1                  0          3
    7782              7839                 0                  0          2
        7934          7782                 1                  0          3

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24383181/viewspace-716209/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24383181/viewspace-716209/