Oracle 10G 新特性——增强的CONNECT BY子句

  1. 云栖社区>
  2. 博客>
  3. 正文

Oracle 10G 新特性——增强的CONNECT BY子句

风月无边 2008-02-20 17:03:00 浏览622
展开阅读全文
      为了更好的查询一个树状结构的表,在OraclePL/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

 

       以上就是在10G中增强的CONNECT BY了。当然对于这些增强特性的作用肯定不止如上介绍的,还需要更多高人去挖掘了。

 

网友评论

登录后评论
0/500
评论
风月无边
+ 关注