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

转：http://www.diybl.com/course/7_databases/oracle/oraclejs/2008224/101010.html

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

 

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