Oracle 10g新特性——增强的Connect By子句

日期： 2006-04-07 14:47:09

分享到

       为了更好的查询一个树状结构的表，在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     龙猫