级别: 中级
Netsetgo
2003 年 3 月
IBM DB2 开发者园地文章将递归 SQL 与 SQL 用户定义函数结合起来，以解决涉及多个数据层次结构的复杂问题。
简介
递归是一种极其有效的解析类树关系的技术。在关系数据库中，类树模型用来表示材料单（Bill of Materials (BOM)）、文档层次结构以及组织的层次结构等数据。如果不使用递归，查询这些数据模型就需要在主机应用程序中进行冗长乏味的迭代编程。
DB2® SQL 程序员都熟悉 IBM DB2 Universal Database™ 中可用的递归构造。DB2 SQL 提供了公共表表达式（WITH 子句）来实现递归。DB2 还提供用户定义函数（UDF），您可以使用它将应用程序特定的函数合并到数据库服务器中。从 DB2 版本 7.2 开始，可以用 DB2 SQL 开发 UDF（此前可以用其它高级语言如 C 和 Java™ 来编写它们）。
我将在本文中说明如何将 DB2 中的传统递归 SQL 与基于 SQL 的用户定义函数（SQL UDF）结合起来，以缩短主机程序实现的时间并增强应用程序性能。
为了提供必要的背景知识，我将通过给出一个树结构的示例来开始讨论。接下来，我将给出一个用递归查询来解析树结构的示例。随后，将使用一个内容管理问题来阐明 DB2 递归 SQL 和 SQL UDF 的结合用法。
递归树结构示例
图 1、2 和 3 分别描述了一个树结构的逻辑视图、其表的表示法和递归查询的结果。
图 1. 树结构的逻辑视图在 图 1中，将内容层次结构描述成包含一组节点的树。该树由根节点 Library 及其子节点 News、Sports 等表示。
上述关系可以存储在如 图 2所示的关系表中。
图 2. 将树节点作为关系表表示
Parent_id Item_id Item_Name -1 1 Library 1 2 News 2 3 World News 2 4 Politics 2 5 Business 2 6 Science 2 7 Technology 1 8 Sports 8 9 Local 8 10 Collegiate 8 11 Professional 9 12 Soccer 10 13 Soccer 11 14 Soccer 9 15 Football 10 16 Football 11 17 Football
该表中的 Parent_id 和 Item_id 列可以通过将节点的 Item_id 用作其子节点的 Parent_id 来表示父、子关系。（请注意：根节点 Library 没有真正的父节点；因此将 -1 用作它的父节点。）数据的固有循环本质（任何节点都既可以看作父节点又可以看作子节点的能力表明了这种本质）促进了递归。
给定上面的数据，递归对于开发那些“遍历”树来产生各种结果的查询是有用的，如任何给定节点的所有后代（子节点和子节点的子节点等等）。
例如，查找名为“Sprots”的节点的后代的递归查询产生如 图 3所示的结果集。
图 3. 递归查询的结果
Parent_id Item_id Item_Name 8 9 Local 8 10 Collegiate 8 11 Professional 9 12 Soccer 9 15 Football 10 13 Soccer 10 16 Football 11 14 Soccer 11 17 Football
DB2 中的递归查询使用 WITH 子句实现，也称为 公共表表达式。从版本 5 开始，就可以在工作站上的 DB2 中使用公共表表达式。
公共表表达式与临时视图非常类似。然而，临时视图仅在定义它的单条 SQL 语句执行期间有效。公共表表达式在 SELECT 语句的开始部分采用 WITH 子句的形式。在使用公共表表达式的查询中可以多次使用它，并且公共表表达式还可以通过取别名连接到它本身，这两个特性使它在实现递归时很有用。
递归查询通常有三个部分：
- 一个公共表表达式形式的虚拟表。
- 一个初始化表。
- 一个与虚拟表进行完全内连接的辅助表。
使用 UNION ALL 合并上述所有表。最后用 SELECT 从递归输出中产生所需的行。
图 4显示了产生 图 3中结果的递归查询。
图 4. 产生图 3 中结果的递归查询。
WITH RPL (Parent_ID, Item_ID, Item_name) AS
(
SELECT ROOT.Parent_ID, ROOT.Item_ID, ROOT.Item_Name
FROM Subject ROOT
WHERE ROOT.Parent_ID = 8
UNION ALL
SELECT CHILD.Parent_ID, CHILD.Item_ID, CHILD.Item_Name
FROM RPL PARENT, Subject CHILD
WHERE PARENT.Item_ID = CHILD.Parent_ID
)
SELECT DISTINCT Parent_ID, Item_ID, Item_Name
FROM RPL
ORDER BY Parent_ID, Item_ID, Item_Name
让我们研究这个查询的组件：
- RPL 作为一个具有以下三列的虚拟表：Parent_ID、Item_ID 和 Item_name。
- WITH 子句内的第一个 SELECT 语句是初始化表。它只执行一次。它的结果形成虚拟表的初始内容以作为递归的种子。在上面的示例中，种子是 Parent_ID 为 8 的一行或多行。
- 第二个 SELECT 语句执行多次。将种子作为输入（JOIN 中的辅助表）传递给第二个 SELECT 语句以产生下一个行集合。将 JOIN 的结果添加（UNION ALL）到虚拟表的当前内容中，并放回到其中以形成用于下一次传递的输入。只要有行产生，这个过程就会继续。
- 如果期望，虚拟表上最后的 SELECT 允许我们选择递归查询所产生的所有行或仅部分行。
图 5用图形描述了上述过程。
图 5. 图 4 中递归查询的图形访问计划以下是如何阅读该图的方法：
- 右边分支是初始化查询，它扫描表 Subject 的主索引文件并使用 Union (6) 将选中的行放置到 Temp (5) 中。
- Temp (5) 是虚拟表。
- 左边分支是查询的递归部分。正如 NLJOIN (7) 所示，它将 Temp (5) 的当前内容连接到 SUBJECT，并将结果放回 TEMP (5) 中。这个过程反复进行，直到没有更多要放到 TEMP (5) 中的行为止。
- 最后的排序和选择操作产生所需的确切结果。
这种技术很好地适用于任何树结构类型 — 发散、收敛、平衡、不平衡以及递归类型。
本文中包含的参考资料 [ Birchall]、[ Chamberlin] 和 [ IBM] 提供了很好的公共表表达式描述。
问题：解决多个层次结构
基于公共表表达式的递归构造提出了一些告诫。最重要的告诫是：您不能以相关方式将一个递归构造的行级结果传递给另一个递归构造。下列语句引自 IBM DB2 Universal Database SQL Reference [ IBM]。
如果在同一语句中定义了多个公共表表达式，不允许公共表表达式之间的循环引用（SQLSTATE 42835）。当创建了两个公共表表达式 dt1 和 dt2 并使 dt1 引用 dt2 且 dt2 引用 dt1 时，就发生循环引用。
如上所述，语句中的多个表表达式不能以相关的方式彼此引用。相关的子查询是对结果集中的每一行执行一次的查询。
让我用示例来阐明这个问题：
考虑 Subjects 层次结构中的一组文档，如 图 1所示。每个主题包含一组文档。Subjects 层次结构中的每个文档都指向一个符合要求的用户组，这个用户组位于组的独立层次结构中。那就意味着有两个树结构 — Subjects 和 User Groups — 都通过文档连接。
图 6说明了该方案。
图 6. 描述多个层次结构 — Subjects 和 User Groups