ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 2012-02-14 level,connect_by_isleaf,connect_by_iscycle

2012-02-14 level,connect_by_isleaf,connect_by_iscycle

原创 Linux操作系统 作者:demonat 时间:2012-02-14 10:30:56 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-04-15

  • 博文量
    46
  • 访问量
    92541