从 Oracle 移植递归 SQL 到 DB2 UDB
reference:http://www.cnitblog.com/iniboy/archive/2006/06/05/11622.html

   简介

递归 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 如何工作？

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

   1. 初始化。
   2. 递归，或者在整个层次结构中重复对逻辑的迭代。
   3. 终止。

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

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

   1. 创建初始结果集。
   2. 基于现有的结果集进行递归。
   3. 查询完毕，返回最终的结果集。

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

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


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





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

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

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


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 会逐渐变大。下面给出了所有的递归元素：

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

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


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

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


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

      SELECT sum(empcount) FROM temptab


下面是例子查询的结果：
<