从 Oracle 移植递归 SQL 到 DB2 UDB

简介

递归 SQL 是用于查询数据层次结构的一种非常强大的方式。组织结构（部门、子部门、子子部门，等等）、讨论论坛（发贴、响应、对响应的响应，等等）、原料帐单、产品分类以及文档层次结构都是层次型数据的例子。

IBM® DB2® Universal Database™ (UDB)是实现了递归 SQL 的几种关系数据库产品中的一种。通常，可以将 DB2 方法看作一种高度强大和灵活的实现。DB2 在递归优势上的一个体现就是在单个的 DB2 表中查询多个层次结构的能力。（要了解更多这方面的细节，请参考在 DB2 开发者园地（DB2 Developer Domain）上由 Srini Venigalla 撰写的文章 使用 DB2 v7.2 中的 SQL UDF 扩大递归机会 。

如果您要将数据从一个 RDBMS 移植到另一个 RDBMS，那么重要的是要知道递归 SQL 的实现因产品而异。特别地，在 Oracle 与 DB2 UDB 之间的差异 这一部分，我将解释在将项目从 Oracle 移植到 DB2 并且涉及递归 SQL 时经常会出现的一个问题。

最根本的问题就是，在 Oracle 和 DB2 中，查询的默认排序次序各不相同。乍一看来这并不重要，因为通常应用程序并不十分依赖于默认的排序次序（没有使用 ORDER BY 子句）。然而在实际中，需要用 Oracle 提供的默认排序次序来解决许多问题，例如显示讨论的线索。很多应用程序都是基于 Oracle 的排序次序的假设，因而当要将那些应用程序移植到 DB2 UDB 时，要理解这一点。

当然，除了解释这个问题之外，我还会给出针对 DB2 中这一难题的解决方案的要点。要看这方面的内容，参见 在 DB2 UDB 中仿效 Oracle 的行为 这一部分。

为了给读者提供有关一般递归，尤其是递归 SQL 的一些背景信息，我将从简要地介绍 DB2 递归 SQL 开始我们的话题。

递归 SQL 如何工作？

递归通常表现为三个基本的步骤：

初始化。
递归，或者在整个层次结构中重复对逻辑的迭代。
终止。
在初始步骤中，要准备好工作区域，并用初始值设置好变量。递归由工作区域中的商业逻辑操作以及随后对下一递归的调用组成，这里采用一种嵌套的方式。最后，终止步骤用于限定递归。打个比方，可以理解为对嵌套级数进行计数，当达到某一特定级数时便停止执行。

这一原理也可以应用到 DB2 中的递归 SQL。递归 SQL 是一种可以分为三个执行阶段的查询：

创建初始结果集。
基于现有的结果集进行递归。
查询完毕，返回最终的结果集。
初始的结果集建立在对基本表的常规 SQL 查询的基础上，这是公共表表达式（CTE）的第一部分。公共表表达式是用于支持递归的手段，它的第二部分对自己进行调用并将其与基本表相连接。从该 CTE 中进行选择的查询便是终止步骤。

下面的例子演示了这一过程。DEPARTMENT是一个包含了有关某个部门的信息的表：

CREATE TABLE departments (deptid INT,
     deptname VARCHAR(20),
     empcount INT,
     superdept INT)

这个表的内容代表了一个层次结构。下面的 图 1 就是一个例子：

图 1. 一个表层次结构的例子

对于一个给定的部门，该部门包括所有的子部门，要获得该部门的雇员人数，需要一个递归查询：

WITH temptab(deptid, empcount, superdept) AS
   (    SELECT root.deptid, root.empcount, root.superdept
            FROM departments root
            WHERE deptname='Production'
     UNION ALL
        SELECT sub.deptid, sub.empcount, sub.superdept
            FROM departments sub, temptab super
            WHERE sub.superdept = super.deptid
   )
SELECT sum(empcount) FROM temptab

在这个例子中，CTE 被称作 temptab，随着查询的继续执行，temptab 会逐渐变大。下面给出了所有的递归元素：

在 temptab 中建立初始结果集。它包含了部门“Production”的雇员人数：

SELECT root.deptid, root.empcount, root.superdept
            FROM departments root
            WHERE deptname='Production'

当在 temptab 中针对于各个子部门加入每一行记录时，便发生了递归。该递归每一次执行的结果都通过 UNION ALL 加入到 temptab 中：

SELECT sub.deptid, sub.empcount, sub.superdept
            FROM departments sub, temptab super
            WHERE sub.superdept = super.deptid

最后的查询就是从 CTE 中提取出所需的信息。在本例中，进行的是总计操作：

SELECT sum(empcount) FROM temptab

下面是例子查询的结果：

1
-----------
SQL0347W  The recursive common table expression "TORSTEN.TEMPTAB" may contain
an infinite loop.  SQLSTATE=01605

         50

  1 record(s) selected with 1 warning messages printed.

通过 DB2 解释工具可以检查 DB2 是如何执行这种递归查询的。嵌套的循环连接（NLJOIN）以一个临时结果表（TEMP）为基础，而这次连接的的结果又再次通过 UNION 被放到这个临时表中。

图 2. 对递归 SQL 的解释

Oracle 与 DB2 UDB 之间的差异

Oracle 通过使用 CONNECT BY PRIOR 提供了类似的特性。在 Oracle 中，上面的例子可以这样来实现：

SELECT sum(empcount) FROM STRUCREL
   CONNECT BY PRIOR superdept = deptid
     START WITH deptname = 'Production';

除了语法上的不同之外，DB2 与 Oracle 在功能性上也有差异。当使用 CONNECT BY PRIOR 时，Oracle 提供了内建的伪列level。在 Oracle 中，下面的查询提供了所有的部门以及这些部门所在的层次结构：

SELECT deptname, level FROM departments
  CONNECT BY PRIOR superdept = deptid
  START WITH deptname = 'Samples & Co.';

DEPTNAME             LEVEL
-------------------- -----------

Samples & Co.                  1
Production                     2
QA                             3
Manufacturing                  3
Prebuilding                    4
Finalbuilding                  4
Sales                          2
North                          3
East                           3
South                          3
West                           3
IT                             2

这种伪列通常用于限制那些查询的递归深度。例如，为了检索“Sales”这个部门的直属子部门，在 Oracle 中可以使用下面的查询：

SELECT deptname FROM departments CONNECT BY PRIOR superdept = deptid
START WITH deptname = 'Sales' AND level=2;

DEPTNAME
--------------------

North
East
South
West

在 DB2 中可以轻易地仿效这一特性，只需像下面这样在 CTE 中维护一个自定义的伪列：

WITH temptab(deptid, deptname, superdept, level) AS
   (    SELECT root.deptid, root.deptname, root.superdept, 1
            FROM departments root
            WHERE deptname='Sales'
     UNION ALL
        SELECT sub.deptid, sub.deptname, sub.superdept, super.level+1
            FROM departments sub, temptab super
            WHERE sub.superdept = super.deptid
   )
SELECT deptname FROM temptab WHERE level=2;

除了 level 伪列，在 DB2 和 Oracle 中另一个非常重要的差异就是由递归查询生成的结果集的搜索次序。在 Oracle 中，层次结构是由深度优先算法创建的。这样一来，当检索整个例子层次结构时，产生的结果集就是这个样子：

SELECT deptname, level FROM departments CONNECT BY PRIOR superdept = deptid
START WITH deptname = 'Samples & Co.;

DEPTNAME             LEVEL
-------------------- -----------

Samples & Co.                  1
Production                     2
QA                             3
Manufacturing                  3
Prebuilding                    4
Finalbuilding                  4
Sales                          2
North                          3
East                           3
South                          3
West                           3
IT                             2

这个结果集说明，在查询延伸到邻节点之前，先要浏览完每个子节点。然而，在 DB2 中，层次结构是通过广度优先算法创建的：

WITH temptab(deptid, deptname, superdept, level) AS
   (    SELECT root.deptid, root.deptname, root.superdept, 1
            FROM departments root WHERE deptname='Samples & Co.'
 

[1] [2] [3]  下一页

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18