Oracle 10G -- 增强的CONNECT BY子句

http://hi.baidu.com/heerit/blog/item/1f0235d99dcc022e11df9b91.html

 

为了更好的查询一个树状结构的表，在Oracle的PL/SQL中提供乐一个诱人的特性——CONNECT BY子句。它大大的方便了我们查找树状表：遍历一棵树、寻找某个分支......，但还是存在一些不足。在Oracle 10G，就对这个特性做了增强。下面就举例说明一下。

CONNECT_BY_ISCYCLE

树状一般都是在一条记录中记录一个当前节点的ID和这个节点的父ID来实现。但是，一旦数据中出现了循环记录，如两个节点互为对方父节点，系统就会报ORA-01436错误，例如：

• 如果有这样的数据

  insert into t_tonedirlib(dirindex, fatherindex, dirname, status) values (666, 667, '123', 5);
  insert into t_tonedirlib(dirindex, fatherindex, dirname, status) values (667, 666, '456', 5);

• 执行这样的查询

  select dirindex, fatherindex, RPAD(' ', 2*(LEVEL-1)) || dirname from t_tonedirlib
  start with fatherindex = 666
  connect by   fatherindex =   prior dirindex;

• 结果是 ORA-01436: 用户数据中的 CONNECT BY 循环。

10G中，可以通过加上NOCYCLE关键字避免报错。并且通过CONNECT_BY_ISCYCLE属性就知道哪些节点产生了循环

select CONNECT_BY_ISCYCLE, dirindex, fatherindex, RPAD(' ', 2*(LEVEL-1)) || dirname
from t_tonedirlib
start with fatherindex = 666
connect by NOCYCLE fatherindex =   prior dirindex
CONNECT_BY_ISCYCLE DIRINDEX FATHERINDEX RPAD(' ',2*(LEVEL-1))||dirname
;

----------------- ---------------- ---------------------------------
                  0                    667                    666 456
                  1                    666                    667 123
2 rows selected

CONNECT_BY_ISLEAF

查找树状表中的叶子节点不是件容易事。可以给表增加了一个字段来描述这个节点是否为叶子节点来解决问题，但这样做有很大的弊端：需要通代码逻辑来保证这个字段的正确性。
Oracle 10G中提供了一个新特性 CONNECT_BY_ISLEAF 来解决这个问题。简单点说，这个属性结果表明当前节点在满足条件的查询结果中是否为叶子节点， 0不是，1是。

select CONNECT_BY_ISLEAF, dirindex, fatherindex, RPAD(' ', 2*(LEVEL-1)) || dirname   
from t_tonedirlib
start with fatherindex = 0
connect by   fatherindex =   prior dirindex
CONNECT_BY_ISLEAF DIRINDEX FATHERINDEX RPAD(' ',2*(LEVEL-1))||dirname

• 查询结果清晰明了！

  ----------------- ---------------- ---------------------------------
                   0                      1                      0 中文经典           
                   0                     52                      1    kkkkkkk          
                   1                     70                     52      222            
                   1                     58                     52      sixx           
                   1                     59                     52      seven          
                   1                     69                     52      uiouoooo       
                   1                     55                     52      four           
                   1                      7                      1    流行风云         
                   0                      8                      1    影视金曲         
                   1                   1111                      8      aaa            
                   1                   1112                      8      bbb            
                   1                   1113                      8      ccc            
                   1                      9                      1    古典音乐         
                   0                     81                      1    小熊之家         
                   1                    104                     81      龙珠           
                   1                    105                     81      snoppy         
                   1                    101                     81      叮当1          
                   1                    102                     81      龙猫           
                   1                    103                     81      叮当2          
                   0                      2                      0 热门流行           
                   1                     31                      2    有奖活动         
                   1                     32                      2    相约香格里拉     
                   1                     50                      2    新浪彩铃         
                   0                      3                      0 老歌回放           
                   1                    333                      3    老电影           
                   1                    335                      3    怀旧金曲