oracle connect by

转

Oracle Database 10g 中新的 CONNECT BY 特性

Jonathan Gennick
自 2001 年以来一直是 OTN 会员

Oracle Database 10g 增强了对查询层次或树状结构数据的支持

最近，我在 OTN 写了一篇文章，名为《查询层次结构：最出色的支持》，其中论述了 Oracle 对层次查询的支持。在写那篇文章时，我了解到 Oracle Database 10g 中一些激动人心的新特性，这些特性加强了 Oracle 已有的对查询树状结构数据的稳健支持。 不幸的是，我不能提及那些新特性，因为它们仍在测试中。 其后，我一直迫不及待地等待着该产品发布，现在我终于可以畅所欲言了。

这篇文章详细说明了 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 <= 2
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 <= 2
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 子句中，防止数据库进入层次结构中的任何循环：

接下来的步骤

下载 Oracle Database：
/global/cn/software/products/oracle9i/index.html

访问 Oracle Database 10g 产品页面：
/global/cn/products/database/index.html

 

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 来指示出现循环的行。现在您已经知道了出现问题的位置，您可以解决它。

完成工作

Oracle Database 10g 中新的 CONNECT BY 特性 — CONNECT_BY_ROOT、CONNECT_BY_ISLEAF 和 CONNECT_BY_ISCYCLE — 是经过精心设计的对 Oracle 层次查询支持的改善，受到大家的欢迎。这些特性解决了查询层次数据所固有的常见和长期存在的问题，而这些问题用其它方式难以解决。利用这些特性，您可以更少地关注实施细节，而更多地专注于您需要实现的目标的全局，从而简单且井然有序地完成您需要完成的工作。