oracle connect by prior 树形结构

卜梨净

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

CONNECT_BY_ROOT一张表，有多颗子树（根节点为0），现在我想知道每个节点属于哪个子树。举例：铃音目录结构下有多个大分类：中外名曲、流行经典、浪漫舞曲……，每个大类下面又有多个子类，子类下面还可以细分。那现在想要知道每个子类分属哪个大类，或者要统计每个大类下面有多少个子类。
看下面的例子，DIRINDEX分别为1、2、3的就是大分类，其他编号的都是子类或孙子类：
select dirindex, fatherindex, RPAD(' ', 2*(LEVEL-1)) || dirname from t_tonedirlib
start with fatherindex = 0
connect by  fatherindex =  prior dirindex
             DIRINDEX           FATHERINDEX DIRNAME                             
--------------------- ------------------------------------
                    1                     0 中文经典                           
                   52                     1   kkkkkkk                           
                   70                    52     222                             
                   58                    52     sixx                           
                   59                    52     seven                           
                   69                    52     uiouoooo                        
                   55                    52     four                           
                    7                     1   流行风云                          
                    8                     1   影视金曲                          
                 1111                     8     aaa                             
                 1112                     8     bbb                             
                 1113                     8     ccc                             
                    9                     1   古典音乐                          
                   81                     1   小熊之家                          
                  104                    81     龙珠                           
                  105                    81     snoppy                          
                  101                    81     叮当1                           
                  102                    81     龙猫                           
                  103                    81     叮当2                           
                    2                     0 热门流行                           
                   31                     2   有奖活动                          
                   32                     2   相约香格里拉                     
                   50                     2   新浪彩铃                          
                    3                     0 老歌回放                           
                  333                     3   老电影                           
                  335                     3   怀旧金曲                          
26 rows selected


如何统计1、2、3三个大类下有哪些子类，有多少个子类？在9i及以前要做这样的统计十分麻烦。现在10G提供了一个新特性：CONNECT_BY_ROOT， 他的作用就是使结果不是当前的节点ID，而满足查询条件下的根节点的ID。以上面为例，我们需要得到以上结果只需要执行以下语句就可以搞定了：
select CONNECT_BY_ROOT dirindex, fatherindex, RPAD(' ', 2*(LEVEL-1)) || dirname from t_tonedirlib
start with fatherindex = 0
connect by  fatherindex =  prior dirindex
CONNECT_BY_ROOTDIRINDEX   FATHERINDEX RPAD('',2*(LEVEL-1))||DIRNAME     
----------------------- ------------- -----------------------------
                      1                     0 中文经典                          
                      1                     1   kkkkkkk                        
                      1                    52     222                           
                      1                    52     sixx                          
                      1                    52     seven                        
                      1                    52     uiouoooo                     
                      1                    52     four                          
                      1                     1   流行风云                        
                      1                     1   影视金曲                        
                      1                     8     aaa                           
                      1                     8     bbb                           
                      1                     8     ccc                           
                      1                     1   古典音乐                        
                      1                     1   小熊之家                        
                      1                    81     龙珠                          
                      1                    81     snoppy                        
                      1                    81     叮当1                        
                      1                    81     龙猫                          
                      1                    81     叮当2                        
                      2                     0 热门流行                          
                      2                     2   有奖活动                        
                      2                     2   相约香格里拉                    
                      2                     2   新浪彩铃                        
                      3                     0 老歌回放                          
                      3                     3   老电影                          
                      3                     3   怀旧金曲                        
26 rows selected


查出来的结果中，CONNECT_BY_ROOTDIRINDEX就是各个子类（孙子类）所属的大类编号，如果需要统计，就只要执行以下语句马上可以统计出来了：
select rootindex, count('X') from
    (select CONNECT_BY_ROOT dirindex as rootindex
    from t_tonedirlib
    start with fatherindex = 0
    connect by  fatherindex =  prior dirindex) a
group by a.rootindex
ROOTINDEX COUNT('X')
--------- ----------
        1         19
        2          4
        3          3
3 rows selected
CONNECT_BY_ISLEAF    经常有DBA因为要查找树状表中的叶子节点而苦恼。大部分DBA为了解决这个问题就给表增加了一个字段来描述这 个节点是否为叶子节点。但这样做有很大的弊端：需要通代码逻辑来保证这个字段的正确性。
    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   怀旧金曲        
26 rows selected
    一看结果，清晰明了！
CONNECT_BY_ISCYCLE       我们的树状属性一般都是在一条记录中记录一个当前节点的ID和这个节点的父ID来实现。但是，一旦数据中出现了循环记录，如两个节点互为对方父节点，系统就会报ORA-01436错误：
insert into t_tonedirlib(dirindex, fatherindex, dirname, status) values (666, 667, '123', 5);
1 row inserted

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


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