这篇文章详细说明了 Oracle Database 10g 中三个新的 CONNECT BY 特性。这些示例使用的表和数据与我之前的文章中所用的表和数据相同。您可能希望读那篇文章来重温一下 CONNECT BY 查询的工作方式。
问题的起源
如果您阅读了我先前的文章，则您已经了解了如何在层次查询中使用 PRIOR 操作符来从一个父行返回列数据。能够返回到根行有时是很方便的。Oracle Database 10g 中新增的 CONNECT_BY_ROOT 操作符使您能够从一个层次结构中的任何位置引用根行的值。
CONNECT_BY_ROOT 的一个用途是用来识别所有包含了一个给定部分的产品。设想您为一个制造公司工作。您发现 1019 号部件有缺陷，消费品安全委员会命令您收回已售出的所有包含了该部件的产品。您的第一个任务是确定您担心的是哪些产品。您可以通过发出以下查询来开始，作为第一步工作：
SELECT assembly_id, assembly_name
FROM bill_of_materials
WHERE part_number = 1019
START WITH parent_assembly IS NULL
CONNECT BY parent_assembly = PRIOR assembly_id;
ASSEMBLY_ID ASSEMBLY_NAME
----------- -----------------------
141 Lock
144 Lock
这些结果看起来似乎是合理的，但它们存在一个问题。1019 号部件是一个锁，但事实上它被用在两个锁组件中。然而，那些锁组件随后被用在左边和右边的门组件内，门组件随后又被用在一个车身组件中，车身组件最后被用来制造一辆汽车，而我们售出的和要关心的是汽车。您不想要 1019 号部件的直接父组件；您想要 1019 号部件最终的父亲。幸运的是，您的数据库刚刚升级到 Oracle Database 10g，因此您可以利用新的 CONNECT_BY_ROOT 操作符：
SELECT DISTINCT CONNECT_BY_ROOT assembly_id,
CONNECT_BY_ROOT assembly_name
FROM bill_of_materials
WHERE part_number = 1019
START WITH parent_assembly IS NULL
CONNECT BY parent_assembly = PRIOR assembly_id;
CONNECT_BY_ROOTASSEMBLY_ID CONNECT_BY_ROOTASSEMBLY
-------------------------- -----------------------
100 Automobile
该查询大部分和以前相同。只有两点区别：使用了DISTINCT；在 SELECT 列表中的每一个列名前面出现了 CONNECT_BY_ROOT。CONNECT_BY_ROOT 操作符输出我们关心的那个部件的最终父组件 ID 和名称。DISTINCT 关键字防止一个产品在多个组件中包含了相同部件时被多次列出。因此，结果是您的汽车在它的右边和左边的门里都包含了锁。
寻根究底
层次数据常常是深层嵌套的。考虑这样一个问题：为机械师提供一辆汽车中的组件和部件的一个嵌套列表。汽车包含大量的部件。机械师很少希望立刻了解所有组件和部件的详情。那样的一个列表不仅将使人不知所措，而且当用户只需要该数据的一部分时，从数据库检索那样一个组件和部件的完整列表以及跨网络传递那样的信息也是非常低效的。相反，您可能选择一开始仅提供顶层的组件，然后让用户从那里开始深入到层次结构内部。例如，您可能一开始提供给我们的用户下列查询的结果：
SELECT ASSEMBLY_ID,
RPAD(' ', 2*(LEVEL-1)) || assembly_name assembly_name,
quantity
FROM bill_of_materials
WHERE LEVEL
START WITH assembly_id = 100
CONNECT BY parent_assembly = PRIOR assembly_id;
ASSEMBLY_ID
ASSEMBLY_NAME
QUANTITY
-----------
-------------
----------
100
Automobile
110
Combustion Engine 1
120
Body
1
130
Interior
1
通过查看第一级的组件，我们的用户现在能够确定他们是否要进一步向下查看。当有更多的数据要查看时，可以通过将组件名实现为 Web 链接来实现向下查看，或者您可以像 Windows 应用中常见的那样实现一个树状控制。且慢！您怎么知道什么时候一个组件会有更多的数据？什么时候向下查看是可能的？当用户试图从该层次结构的底部向下查看时，您可以让他们试着向下查看到任何组件中，然后给他们一条“没有更多数据”的消息，但这是一种生硬的解决办法，无疑将使他们感到灰心。最好能够提前了解向下查看是否可能。Oracle Database 10g 使我们能够通过 CONNECT_BY_ISLEAF 虚拟列来达到这一目的。您可以使用以下查询来开始：
SELECT ASSEMBLY_ID,
RPAD(' ', 2*(LEVEL-1)) || assembly_name assembly_name,
quantity, CONNECT_BY_ISLEAF
FROM bill_of_materials
WHERE LEVEL
START WITH assembly_id = 100
CONNECT BY parent_assembly = PRIOR assembly_id;
ASSEMBLY_ID ASSEMBLY_NAME
QUANTITY CONNECT_BY_ISLEAF
----------- ----------------------- ---------- -----------------
100 Automobile
0
110
Combustion Engine
1
0
120
Body
1
0
130
Interior
1
0
CONNECT_BY_ISLEAF 返回的零指示在该列表中显示的组件中没有一个是叶节点。换句话说，向下查看到它们的任何一个中都是无效的。假定用户向下查看到 Combustion Engine 中。那么您可以发出以下查询来获取组成发动机的子组件：
SELECT ASSEMBLY_ID,
RPAD(' ', 2*(LEVEL-1)) || assembly_name assembly_name,
quantity, CONNECT_BY_ISLEAF
FROM bill_of_materials
WHERE LEVEL = 2
START WITH assembly_id = 110
CONNECT BY parent_assembly = PRIOR assembly_id;
ASSEMBLY_ID ASSEMBLY_NAME
QUANTITY CONNECT_BY_ISLEAF
----------- ----------------------- ---------- -----------------
111
Piston
6
1
112
Air Filter
1
1
113
Spark Plug
6
1
114
Block
1
1
115
Starter System
1
0
该查询和以前几乎是相同的。Combustion Engine 的 START WITH 组件 ID 值变为 110，该查询特别请求 LEVEL = 2。在这个节点上，您不需要 LEVEL = 1，因为那将再次返回 Combustion Engine 的行，而您已经得到那一行了。
这次，您看到 CONNECT_BY_ISLEAF 有两个值。Piston、Air Filter、Spark Plug 和 Block 的值为 1 指示那些组件是叶节点，且其下没有发现更多的组件。了解到这一点，您就可以调整我们的显示内容，这样用户就知道不用徒劳地在那些组件上向下钻取。另一方面，Starter System 的 CONNECT_BY_ISLEAF 值为 0，这指示仍然存在要检索的子组件。
跳出循环
只要您利用层次数据进行工作，您就可能遇到一个循环的层次。比如说，有人可能将一辆汽车的父组件设为一个火花塞：
UPDATE bill_of_materials
SET parent_assembly = 113
WHERE assembly_id=100;
尝试在该组件树中查询 "Automobile" 现在就将失败：
SELECT RPAD(' ', 2*(LEVEL-1)) || assembly_name assembly_name,
quantity
FROM bill_of_materials
START WITH assembly_id = 100
CONNECT BY parent_assembly = PRIOR assembly_id;
ERROR:
ORA-01436:CONNECT BY loop in user data
当您获得像这样的一条错误消息时，您可以使用 CONNECT_BY_ISCYCLE 虚拟列来确定引起问题的行的位置。要做到这一点，您还必须添加 NOCYCLE 关键字到 CONNECT BY 子句中，防止数据库进入层次结构中的任何循环：
SELECT RPAD(' ', 2*(LEVEL-1)) || assembly_name assembly_name,
quantity, CONNECT_BY_ISCYCLE
FROM bill_of_materials
START WITH assembly_id = 100
CONNECT BY NOCYCLE parent_assembly = PRIOR assembly_id;
ASSEMBLY_NAME
QUANTITY CONNECT_BY_ISCYCLE
------------------------------ ---------- ------------------
Automobile
0
Combustion Engine
1
0
Piston
6
0
Air Filter
1
0
Spark Plug
6
1
Block
1
0
注意，CONNECT_BY_ISCYCLE 为 "Spark Plug" 行返回一个 1。当您使用 NOCYCLE 时，数据库通过层次跟踪它的路径，不断检查确保它不会进入循环。在完成从 "Automobile" 到 "Combustion Engine" 再到 "Spark Plug" 的遍历后，数据库发现 "Spark Plug" 的孩子是 "Automobile"，该行所处的路径通向 "Spark Plug"。这样的一行代表一个循环。NOCYCLE 防止数据库进入循环，CONNECT_BY_ISCYCLE 返回一个 1 来指示出现循环的行
