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

Oracle <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><chmetcnv unitname="g" sourcevalue="10" hasspace="False" negative="False" numbertype="1" tcsc="0" w:st="on">10G</chmetcnv>新特性——增强的CONNECT BY子句<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

作者：fuyuncat

来源：WWW.HelloDBA.COM

为了更好的查询一个树状结构的表，在Oracle的PL/SQL中提供乐一个诱人的特性——CONNECT BY子句。它大大的方便了我们查找树状表：遍历一棵树、寻找某个分支……，但还是存在一些不足。在Oracle <chmetcnv unitname="g" sourcevalue="10" hasspace="False" negative="False" numbertype="1" tcsc="0" w:st="on">10G</chmetcnv>，就对这个特性做了增强。下面就举例说明一下： 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 循环 <chmetcnv unitname="g" sourcevalue="10" hasspace="False" negative="False" numbertype="1" tcsc="0" w:st="on">10G</chmetcnv>中，可以通过加上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 以上就是在<chmetcnv unitname="g" sourcevalue="10" hasspace="False" negative="False" numbertype="1" tcsc="0" w:st="on"><span lang="EN-US">10G</span></chmetcnv>中增强的CONNECT BY了。当然对于这些增强特性的作用肯定不止如上介绍的，还需要更多高人去挖掘了。