为了更好的查询一个树状结构的表，在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
分享到：
相关推荐
递归树形结构查询主要依赖于`CONNECT BY`和`PRIOR`关键字，它们允许我们构建复杂的层级查询，以展示数据的层次关系。 在Oracle中，树形结构查询的基本语法如下： ```sql SELECT [LEVEL], * FROM table_name START ...
`Connect By Prior` 是一个用于构建层次结构查询的特殊语法，它允许我们在表中按照某种关系建立树形结构。具体语法如下： ```sql SELECT ... FROM table_name START WITH condition CONNECT BY PRIOR column_name =...
首先，要了解的是，在Oracle中，树形结构数据的查询主要依赖于START WITH...CONNECT BY PRIOR语句。这种查询方式允许用户指定起始节点（根节点），并通过CONNECT BY子句定义父子节点之间的递归关系。这种方式特别...
在Oracle中，树形结构查询可以通过start with和connect by子句来实现。start with子句用于确定开始节点，而connect by子句用于指定父子关系。层次查询语句可以通过level关键字来确定层次关系，level关键字是可选的，...
Oracle数据库中的`CONNECT BY PRIOR`是一个强大的查询构造器，用于处理树形数据结构，尤其在组织层级、部门结构或者员工管理系统中非常常见。这个功能允许我们从一个或多个根节点开始，按照指定的规则遍历整个树结构...
总之，Oracle的`CONNECT BY`和`LEVEL`是处理层次数据的强大工具，它们提供了遍历和展示树形结构数据的有效方式。通过深入理解并灵活运用这些概念，你可以在数据库查询中实现更加复杂和高效的数据操作。在进行实际...
### Oracle中的START WITH CONNECT BY PRIOR 用法详解 #### 一、概念介绍 在Oracle数据库中，`START WITH` 和 `CONNECT BY PRIOR` 是两个非常强大的特性，主要用于处理具有层级结构的数据。这两个特性可以帮助我们...
### Oracle中的Connect By Prior递归算法详解 #### 一、Connect By Prior 子句概述 在Oracle数据库中，`Connect By Prior`子句是一种非常有用的工具，尤其在处理具有层次结构的数据时。它允许用户以一种简洁的方式...
2. **Start With**：用来定义查询的起点，即指定哪一行作为树形结构的根节点。例如，`START WITH employee_id = 1`表示从ID为1的员工开始查询其所有的下属。 #### 三、示例代码解析 假设我们有一个如下的测试表`...
Oracle数据库在许多企业级应用中扮演着核心角色，它的数据组织方式之一是树形结构，这种结构对于管理和展示层次化数据非常有效。本主题将深入探讨如何在Oracle中利用树形结构以及如何结合dtree插件来显示树状结构...
总结来说，`START WITH...CONNECT BY PRIOR`子句是Oracle SQL处理树形数据的关键工具，通过它可以有效地查询和展示层次关系的数据。理解并熟练运用这个子句，能够帮助你在处理具有层级结构的业务场景时更加游刃有余...
这个特性允许我们遍历和查询树形结构，将层次数据平展为一行一列的形式，以便进行分析和处理。 `connect by prior`的基本语法如下： ```sql SELECT ... FROM tablename START WITH 条件 1 CONNECT BY PRIOR ...
oracle菜单树查询 使用实例 使用START WITH CONNECT BY PRIOR子句实现递归查询
Connect By语句的优点是可以高效地查询树形结构的数据，且可以递归地遍历整个树形结构。同时，Connect By语句也可以用于实现多种功能，例如列出上下级关系、构造序列和实现排列组合。 Connect By的应用场景 ...
Oracle 连接查询是指使用 START WITH 和 CONNECT BY 语句来实现递归查询的方法，这种方法可以生成树形结构的数据。在 Oracle 中，START WITH 语句用于指定递归查询的开始记录，而 CONNECT BY 语句用于指定递归查询的...
Oracle SQL 提供了 `START WITH...CONNECT BY PRIOR` 语句来实现递归查询，使得我们可以轻松地遍历和检索这些树形结构。 首先，`START WITH` 子句定义了查询的起点，即树形结构中的根节点或者一个特定的起点节点。...
在Oracle中，`START WITH` 和 `CONNECT BY` 是进行递归查询的关键字，它们允许我们从一个特定的根节点出发，沿着预定义的关系链接遍历整个树形结构。本文将深入探讨这两个关键字的用法，并通过实例来说明其在实际...
在Oracle数据库中，进行树形结构数据查询时，通常会用到`START WITH`和`CONNECT BY PRIOR`这两个关键语法。本文将详细介绍如何使用这两种方法来实现不同类型的树形结构查询，并通过具体示例加深理解。 #### 1. 基本...
这条查询将遍历整个树形结构，按深度优先的方式逐层向下探索，并返回所有层级的数据。其中`leaf_id`为子节点字段，`pir_id`为父节点字段。 - 指定起始条件时，使用`START WITH ... CONNECT BY`： ```sql SELECT * ...