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

      为了更好的查询一个树状结构的表，在Oracle的PL/SQL中提供乐一个诱人的特性——CONNECT BY子句。它大大的方便了我们查找树状表：遍历一棵树、寻找某个分支……，但还是存在一些不足。在Oracle <st1:chmetcnv tcsc="0" hasspace="False" sourcevalue="10" numbertype="1" negative="False" w:st="on" unitname="g">10G</st1:chmetcnv>，就对这个特性做了增强。下面就举例说明一下：

CONNECT_BY_ROOT<o:p></o:p>

一张表，有多颗子树（根节点为0），现在我想知道每个节点属于哪个子树。举例：铃音目录结构下有多个大分类：中外名曲、流行经典、浪漫舞曲……，每个大类下面又有多个子类，子类下面还可以细分。那现在想要知道每个子类分属哪个大类，或者要统计每个大类下面有多少个子类。<o:p></o:p>

看下面的例子，DIRINDEX分别为1、2、3的就是大分类，其他编号的都是子类或孙子类：<o:p></o:p>

select dirindex, fatherindex, RPAD(' ', 2*(LEVEL-1)) || dirname from t_tonedirlib<o:p></o:p>

start with fatherindex = 0<o:p></o:p>

connect by  fatherindex =  prior dirindex<o:p></o:p>

             DIRINDEX           FATHERINDEX DIRNAME                             <o:p></o:p>

--------------------- ------------------------------------<o:p></o:p>

                    1                     0 中文经典                            <o:p></o:p>

                   52                     1   kkkkkkk                           <o:p></o:p>

                   70                    52     222                             <o:p></o:p>

                   58                    52     sixx                            <o:p></o:p>

                   59                    52     seven                           <o:p></o:p>

                   69                    52     uiouoooo                        <o:p></o:p>

                   55                    52     four                            <o:p></o:p>

                    7                     1   流行风云                          <o:p></o:p>

                    8                     1   影视金曲                          <o:p></o:p>

                 1111                     8     aaa                             <o:p></o:p>

                 1112                     8     bbb                             <o:p></o:p>

                 1113                     8     ccc                             <o:p></o:p>

                    9                     1   古典音乐                          <o:p></o:p>

                   81                     1   小熊之家                          <o:p></o:p>

                  104                    81     龙珠                            <o:p></o:p>

                  105                    81     snoppy                          <o:p></o:p>

                  101                    81     叮当1                           <o:p></o:p>

                  102                    81     龙猫                            <o:p></o:p>

                  103                    81     叮当2                           <o:p></o:p>

                    2                     0 热门流行                            <o:p></o:p>

                   31                     2   有奖活动                          <o:p></o:p>

                   32                     2   相约香格里拉                      <o:p></o:p>

                   50                     2   新浪彩铃                          <o:p></o:p>

                    3                     0 老歌回放                            <o:p></o:p>

                  333                     3   老电影                            <o:p></o:p>

                  335                     3   怀旧金曲                          <o:p></o:p>

26 rows selected<o:p></o:p>

 

如何统计1、2、3三个大类下有哪些子类，有多少个子类？在9i及以前要做这样的统计十分麻烦。现在10G提供了一个新特性：CONNECT_BY_ROOT，他的作用就是使结果不是当前的节点ID，而满足查询条件下的根节点的ID。以上面为例，我们需要得到以上结果只需要执行以下语句就可以搞定了：<o:p></o:p>

select CONNECT_BY_ROOT dirindex, fatherindex, RPAD(' ', 2*(LEVEL-1)) || dirname from t_tonedirlib<o:p></o:p>

start with fatherindex = 0<o:p></o:p>

connect by  fatherindex =  prior dirindex<o:p></o:p>

CONNECT_BY_ROOTDIRINDEX   FATHERINDEX RPAD('',2*(LEVEL-1))||DIRNAME     <o:p></o:p>

----------------------- ------------- -----------------------------<o:p></o:p>

                      1                     0 中文经典                          <o:p></o:p>

                      1                     1   kkkkkkk                         <o:p></o:p>

                      1                    52     222                           <o:p></o:p>

                      1                    52     sixx                          <o:p></o:p>

                      1                    52     seven                         <o:p></o:p>

                      1                    52     uiouoooo                      <o:p></o:p>

                      1                    52     four                          <o:p></o:p>

                      1                     1   流行风云                        <o:p></o:p>

                      1                     1   影视金曲                        <o:p></o:p>

                      1                     8     aaa                           <o:p></o:p>

                      1                     8     bbb                           <o:p></o:p>

                      1                     8     ccc                           <o:p></o:p>

                      1                     1   古典音乐                        <o:p></o:p>

                      1                     1   小熊之家                        <o:p></o:p>

                      1                    81     龙珠                          <o:p></o:p>

                      1                    81     snoppy                        <o:p></o:p>

                      1                    81     叮当1                         <o:p></o:p>

                      1                    81     龙猫                          <o:p></o:p>

                      1                    81     叮当2                         <o:p></o:p>

                      2                     0 热门流行                          <o:p></o:p>

                      2                     2   有奖活动                        <o:p></o:p>

                      2                     2   相约香格里拉                    <o:p></o:p>

                      2                     2   新浪彩铃                        <o:p></o:p>

                      3                     0 老歌回放                          <o:p></o:p>

                      3                     3