Connect By在10g中得增强, nocycle关键字等

我 们的树状属性一般都是在一条记录中记录一个当前节点的ID和 这个节点的父ID来实现。但是，一旦数据中出现了循环记录，如两个节点互为 对方父节点，系统就会报ORA-01436错误。10G中， 可以通过加上NOCYCLE关键字避免报错。并且通过CONNECT_BY_ISCYCLE属 性就知道哪些节点产生了循环：

 

drop table t;
create table t(cid int, pid int);
truncate table t;

/*
  1--
    |--2
    |--3
      |--5
        |--1(cycle)
      |--6
    |--4
  11--
    |--6
    |--112 
*/
-- tree number 1
insert into t(cid, pid) values(1,null);
insert into t(cid, pid) values(2,1);
insert into t(cid, pid) values(3,1);
insert into t(cid, pid) values(4,1);
insert into t(cid, pid) values(5,3);
insert into t(cid, pid) values(6,3);
-- another tree number 11
insert into t(cid, pid) values(11,null);
insert into t(cid, pid) values(112,11);
insert into t(cid, pid) values(6,11); -- node 6 in both trees
-- cycle node
insert into t(cid, pid) values(1,5);

commit;

select * from t;

-- the path in row whose iscyle=1 tells the cycle path
-- here is 1_3_5(_1)
select lpad(' ', 2 * (level - 1)) || t.cid cid,
       connect_by_root t.cid root,
       connect_by_isleaf isleaf,
       connect_by_iscycle iscycle,
       substr(sys_connect_by_path(t.cid,'_'),2) path
  from t
 start with t.pid is null
connect by nocycle t.pid = prior t.cid
 order siblings by t.cid;

-- 测试结果
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE        10.2.0.1.0        Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL>
SQL> -- the path in row whose iscyle=1 tells the cycle path
SQL> -- here is 1_3_5(_1)
SQL> select lpad(' ', 2 * (level - 1)) || t.cid cid,
  2         --connect_by_root t.cid root,
  3         --connect_by_isleaf isleaf,
  4         connect_by_iscycle iscycle,
  5         substr(sys_connect_by_path(t.cid,'_'),2) path
  6    from t
  7   start with t.pid is null
  8  connect by nocycle t.pid = prior t.cid
  9   order siblings by t.cid;

CID                  ISCYCLE        PATH
-------------------- ----------     ----------------------------------------
1                             0     1
  2                           0     1_2
  3                           0     1_3
    5                         1     1_3_5
    6                         0     1_3_6
  4                           0     1_4
11                            0     11
  6                           0     11_6
  112                         0     11_112

9 rows selected