将oracle的 CONNECT BY 移植到 DB2

将oracle的 CONNECT BY 移植到 DB2

本文通过例子逐步描述如何将 CONNECT BY 语法和相关的伪列映射到 DB2® Universal Database™（DB2 UDB） for Linux®、UNIX® 和 Windows® 中的公共表表达式

动机

当把应用程序从 Oracle 移植或迁移到 DB2 UDB for Linux、UNIX 和 Windows 时，递归查询成为一块巨大的绊脚石。能理解和使用 Oracle 型递归的开发人员，却常常无法自然而然地理解 DB2 中的递归，反过来也是一样。

乍一看来，这似乎是因为 DB2 是按照 SQL 标准中的定义并使用公共表表达式（CTE）和 UNION ALL 来实现递归的，而 Oracle 则使用 CONNECT BY 子句和一组所谓的伪列（pseudo column）及系统过程来定义递归。然而，两者之间的差别不仅在于语法方面。在底层，DB2 处理递归时采用的方法是逐层递归（广度优先），而 Oracle 采用的是深度优先的方法。后一种方法自然地产生与组织结构图（org chart）相匹配的输出。

在本文中，我使用一个实用的例子，这个例子将 CONNECT BY 和相关的伪列与 DB2 递归逐步进行匹配。

安装

为了执行一些查询，您必须下载和解压本文后面 下载 小节中提供的 CONNECT_BY.zip 文件。

如果您使用的是 32 位 Windows、32 位 Intel 上的 Linux 或 64 位 AIX® 系统，那么只需将 CONNECT_BY.zip 文件中适当子目录中的可执行文件 connect_by（在 Windows 上是 connect_by.dll）复制到 sqllib/function 目录。

如果使用的是其他操作系统或硬件平台，并且系统中安装了 C 编译器，那么可以将 Connect_By/FunctionSource 目录中的文件复制到 sqllib/samples/c 目录中。进入到那个目录后，运行 bldrtn connect_by。该命令将编译 C 源代码。bldrtn 还将把产生的可执行文件复制到 sqllib/function，这样便可以运行本文中的查询了。

为了方便，当连接到一个测试数据库时，运行 CONNECT_BY.zip 中的以下文件将重新产生本文中的所有 DB2 SQL：

db2 -tvf connect_by_functions.sql
db2 -tvf connect_by_sample.sql

设置例子

首先，我们将定义和填充一个雇员表。这个表有四个列，一个是作为主键的雇员 ID，一个是将雇员与其经理关联起来的经理 ID，此外还有姓名和薪水：

1 CREATE TABLE emp(empid INTEGER NOT NULL PRIMARY KEY,
2 name VARCHAR(10),
3 salary DECIMAL(9, 2),
4 mgrid INTEGER);
5 INSERT INTO emp
6 VALUES ( 1, 'Jones', 30000, 10),
7 ( 2, 'Hall', 35000, 10),
8 ( 3, 'Kim', 40000, 10),
9 ( 4, 'Lindsay', 38000, 10),
10 ( 5, 'McKeough', 42000, 11),
11 ( 6, 'Barnes', 41000, 11),
12 ( 7, 'O''Neil', 36000, 12),
13 ( 8, 'Smith', 34000, 12),
14 ( 9, 'Shoeman', 33000, 12),
15 (10, 'Monroe', 50000, 15),
16 (11, 'Zander', 52000, 16),
17 (12, 'Henry', 51000, 16),
18 (13, 'Aaron', 54000, 15),
19 (14, 'Scott', 53000, 16),
20 (15, 'Mills', 70000, 17),
21 (16, 'Goyal', 80000, 17),
22 (17, 'Urbassek', 95000, NULL);

注意被插入的这些列，显然雇员‘Urbassek’是最高级的经理，‘Goyal’和‘Mills’是他的属下。而 ‘Scott’又是‘Goyal’的属下，但是他没有自己的属下。‘Monroe’则是‘Hall’、‘Kim’和‘Lindsay’的上司，同时也是‘Mills’的属下。

一个合情合理的问题是：“谁是‘Goyal’直接或间接的属下？”

一个简单的递归查询

为了回答谁是‘Goyal’的属下这个问题，Oracle 开发人员可能会编写以下查询：

1 SELECT name
2 FROM emp
3 START WITH name = 'Goyal'
4 CONNECT BY PRIOR empid = mgrid

START WITH 表示递归的种子，而 CONNECT BY 描述递归步骤，也就是如何从第 n 步发展到第 (n + 1) 步。由于在归结 name 时需要区分第 n 步和第 (n + 1) 步，因此使用 PRIOR 来表明 empid 属于第 n 步，而 mgrid 属于第 (n + 1) 步。在第 1 步，empid 为 16，mgrid 也必须是 16，因此第 2 步产生‘Scott’、‘Henry’和‘Zander’。他们的 empid 将作为第 3 步的 PRIOR，依此类推。

Oracle 语法非常简洁。SQL 标准和 DB2 语法不使用任何递归专用的关键词，而是使用常规的 SQL 来描述完全相同的关系。您会看到，这样做显得更啰嗦，但是同样也比较简单：

1 WITH n(empid, name) AS
2 (SELECT empid, name
3 FROM emp
4 WHERE name = 'Goyal'
5 UNION ALL
6 SELECT nplus1.empid, nplus1.name
7 FROM emp as nplus1, n
8 WHERE n.empid = nplus1.mgrid)
9 SELECT name FROM n;

WITH 子句允许在一条语句中定义一个有名称的查询，在同一条语句中，稍后将引用到这个查询。用技术术语来说，这叫做 公共表表达式（common table expression，CTE）。 CTE 现在在大多数基于 SQL 的 DBMS 中都受到支持，包括 Oracle 9i 和 SQL Server 2005，当然也包括 DB2。

这个 CTE 的特殊之处在于，它在自己的定义中被引用。这就是常规 CTE 与 递归 CTE 的区别。这里我将 CTE 命名为 n，以便与递归步骤关联起来。递归 CTE 由两部分组成，这两部分通过 UNION ALL 连接：

一部分是递归的种子（即第 1 步）。这就是在 Oracle 中用 START WITH 描述的那一部分。在递归 CTE 中，它只是一个普通的提供一组行的查询。在这个例子中，我们查询 emp 表，并用 ‘Goyal’ 进行过滤。当然我们会选择 name，另外还有 empid，因为在递归步骤中需要它。
另一部分是递归从第 n 步发展到第 (n + 1) 步。这里我们引用第 n 步（CTE n），并使用与 CONNECT BY 中相同的谓词将它与第 (n + 1) 步相连接。但是，这里不使用 PRIOR，而是使用常规的相关名称来区分第 n 步和第 (n + 1) 步。
这里有必要看一下这个查询的输出：

NAME
----------
SQL0347W The recursive common table expression "SRIELAU.N"
may contain an infinite loop. SQLSTATE=01605
Goyal
Zander
Henry
Scott
McKeough
Barnes
O'Neil
Smith
Shoeman
9 record(s) selected with 1 warning messages printed.

DB2 不执行任何自动的循环检查。正因为如此，当它断定可能存在循环时，就会返回这条警告。介意这条警告的 DB2 开发人员通常添加一个 level 列，令其在每次递归中加 1，并且不能大于一个固定的值。 DB2 承认这种模式，因而取消这条警告。出于映射 CONNECT BY 的目的，从现在开始我们将忽略警告，因为在后面将增加循环检查。

而且，令人感兴趣的是，DB2 返回结果时所依照的顺序暴露了递归如何逐层地而不是采用遍历树的方法进行处理。

在讨论伪列和更复杂的例子之前，我想简要地解释一下 Oracle 中递归的 WHERE 谓词应该放在哪里。我们将修改这个例子，使它返回 ‘Goyal’ 管辖的所有收入超过 40,000 的雇员，包括他们的薪水。

1 SELECT name, salary
2 FROM emp
3 WHERE salary > 40000
4 START WITH name = 'Goyal'
5 CONNECT BY PRIOR empid = mgrid

令人感兴趣的是，WHERE 子句在递归说明之前。对于 DB2 开发人员，在上述查询中，该谓词似乎属于开始时考虑的一组行。然而，事实并非如此。相反，WHERE 负责过滤最终的结果，在对应的 DB2 查询中，它是属于整个查询的：

1 WITH n(empid, name, salary) AS
2 (SELECT empid, name, salary
3 FROM emp
4 WHERE name = 'Goyal'
5 UNION ALL
6 SELECT nplus1.empid, nplus1.name, nplus1.salary
7 FROM emp as nplus1, n
8 WHERE n.empid = nplus1.mgrid)
9 SELECT name FROM n WHERE salary > 40000;
NAME SALARY
---------- -----------
Goyal 80000.00
Zander 52000.00
Henry 51000.00
Scott 53000.00
McKeough 42000.00
Barnes 41000.00
6 record(s) selected

这里要清楚的是，WHERE 子句不同于 ON 子句。在后一个例子中我会重新提到这个问题。但是首先我们还是来讨论伪列。

LEVEL 伪列

最常见的伪列是 LEVEL。这个列的作用是表明产生行的递归步骤 n 属于第几步。在这个例子中，它表示‘Goyal’与雇员之间相差的管理等级加 1（因为 LEVEL 一开始为 1）。下面是原先的 Oracle 例子添加了 LEVEL 列之后的样子：

1 SELECT LEVEL, name
2 FROM emp
3 START WITH name = 'Goyal'
4 CONNECT BY PRIOR empid = mgrid

SQL 标准不必为该特性添加语法，因为它可以使用常规的 SQL 来表达：

1 WITH n(level, empid, name) AS
2 (SELECT 1, empid, name
3 FROM emp
4 WHERE name = 'Goyal'
5 UNION ALL
6 SELECT n.level + 1, nplus1.empid, nplus1.name
7 FROM emp as nplus1, n
8 WHERE n.empid = nplus1.mgrid)
9 SELECT level, name FROM n;
LEVEL NAME
----------- ----------
1 Goyal
2 Zander
2 Henry
2 Scott
3 McKeough
3 Barnes
3 O'Neil
3 Smith
3 Shoeman
9 record(s) selected

我在这里只是引入了一个 level 列，这个列的初值为 1，每次加 1。当然，任何语义都是可能的，但是这里刚好提供了和 LEVEL 相同的语义。要由开发人员来选择传递什么信息，以及如何传递信息，下一个例子将展示这一点。

CONNECT_BY_ROOT 表达式

CONNECT_BY_ROOT 作用在一个列上，并返回当前行最早的（root）祖先的值。为了展示它做什么事情以及如何翻译它，我修改了上面的例子，让例子使用‘Goyal’的直接下属作为 root：

1 SELECT CONNECT_BY_ROOT name AS root, name
2 FROM emp
3 START WITH name IN ('Zander', 'Henry', 'Scott')
4 CONNECT BY PRIOR empid = mgrid

为映射到 DB2，只需添加一个 root 列。

1 WITH n(empid, root, name) AS
2 (SELECT empid, name, name
3 FROM emp
4 WHERE name IN ('Zander', 'Henry', 'Scott')
5 UNION ALL
6 SELECT nplus1.empid, n.root,
7 nplus1.name
8 FROM emp as nplus1, n
9 WHERE n.empid = nplus1.mgrid)
10 SELECT root, name FROM n;
ROOT NAME
---------- ----------
Zander Zander
Henry Henry
Scott Scott
Zander McKeough
Zander Barnes
Henry O'Neil
Henry Smith
Henry Shoeman
8 record(s) selected

接下来，我将演示如何展示整个管理链，而不仅仅是根。

SYS_CONNECT_BY_PATH() 过程

运行查询时一个常见的问题是：“这个元素与递归的开始有怎样的关系？”，或者换句话说，“这一行的祖先是什么？”在 Oracle 中，可以使用 SYS_CONNECT_BY_PATH() 来连接来自每个递归步骤的值，从而形成一个祖先路径。看看下面这个著名的例子，它展示了‘Goyal’每个下属的“从属”链：

1 SELECT SYS_CONNECT_BY_PATH(name, ':') AS chain
2 FROM emp
3 START WITH name = 'Goyal'
4 CONNECT BY PRIOR empid = mgrid

在这个例子中，每个值之间用一个 ‘:’ 号隔开。而在 DB2 中要回答这个问题，可以使用与 LEVEL 相同的技术。惟一的差别是，这里使用的是 并置（concatenation），而不是 相加（addition）。

1 WITH n(empid, chain) AS
2 (SELECT empid, CAST(name AS VARCHAR(30))
3 FROM emp
4 WHERE name = 'Goyal'
5 UNION ALL
6 SELECT nplus1.empid,
7 n.chain || ':' || nplus1.name
8 FROM emp as nplus1, n
9 WHERE n.empid = nplus1.mgrid)
10 SELECT chain FROM n;
CHAIN
------------------------------
Goyal
Goyal:Zander
Goyal:Henry
Goyal:Scott
Goyal:Zander:McKeough
Goyal:Zander:Barnes
Goyal:Henry:O'Neil
Goyal:Henry:Smith
Goyal:Henry:Shoeman
9 record(s) selected

您可以自由地选择如何构造这个链。例如，可以反转并置，使‘Goyal’位于链尾。这里还应注意被执行的 CAST。对于字符串数据类型，DB2 需要通过它来知道这个链可以增长到多长。递归 CTE 的规则规定，数据类型的长度应该由种子或 UNION ALL 查询的前半部分来定义。

至此，从 CONNECT BY 到 UNION ALL 的映射已轻松完成。现在您对递归 CTE 已经感到得心应手了，接下来是继续攻克更大难题的时候了。如果只是要求按照后面选择的路径中元素的顺序对结果排序，那么只需在最后的 SELECT 中的路径上使用一个 ORDER BY：

1 WITH n(empid, chain) AS
2 (SELECT empid, CAST(name AS VARCHAR(30))
3 FROM emp
4 WHERE name = 'Goyal'
5 UNION ALL
6 SELECT nplus1.empid,
7 n.chain || ':' || nplus1.name
8 FROM emp as nplus1, n
9 WHERE n.empid = nplus1.mgrid)
10 SELECT chain FROM n
11 ORDER BY chain;
CHAIN
------------------------------
Goyal
Goyal:Henry
Goyal:Henry:O'Neil
Goyal:Henry:Shoeman
Goyal:Henry:Smith
Goyal:Scott
Goyal:Zander
Goyal:Zander:Barnes
Goyal:Zander:McKeough
9 record(s) selected

这是一种常见的 组织结构图。但是如果要求按其他不是字符串类型的列排序，结果会怎样呢？

ORDER SIBLINGS BY 表达式

在 Oracle 中，ORDER SIBLINGS BY 定义返回时同一父亲下各个兄弟之间的顺序。为了使用 CONNECT BY 递归地按照薪水对所有雇员排序，查询的形式如下：

1 SELECT name, level, salary
2 FROM emp
3 START WITH name = 'Goyal'
4 CONNECT BY PRIOR empid = mgrid
5 ORDER SIBLINGS BY salary

SQL 标准有用于这个需求的特定语法。然而，DB2 UDB for LUW 还没有实现 SQL 标准的这一部分。为了在 DB2 中映射这个特性，就需要发挥创造力。可以再次建立一个路径，然后对字符串排序，但是这样看上去有点儿不优雅，因为需要将数值转换成字符串，从而增加了路径的长度。而且，随后的映射将表明，提供一个更通用的解决方案比较有益。

这里，我引入了一个 用户定义函数（UDF）。UDF 是 DB2 功能的一个扩展。在这个例子中，这个 UDF（和后面的那些 UDF）是用 C 实现的，如果您遵循了前面的 安装 小节中的说明，那么 DB2 中就有它的可执行文件。

1 CREATE FUNCTION CONNECT_BY_POS
2 (POS VARCHAR(256) FOR BIT DATA,
3 N INTEGER,
4 POSPLUS1 BIGINT)
5 RETURNS VARCHAR(256) FOR BIT DATA
6 SPECIFIC CONNECT_BY_POS
7 EXTERNAL NAME 'connect_by!connect_by_pos'
8 NOT FENCED RETURNS NULL ON NULL INPUT
9 DETERMINISTIC NO SQL NO EXTERNAL ACTION
10 LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

这个函数有两个作用：

该函数以行 posplus1 的全局位置的 BIGINT 值为参数并进行转换，同时使父行的 pos 模式成为下一行的模式。父行的层次 n 用于推动递归。原则上，该函数只是建立一个二进制编码的路径。这里提供的实现在内部是使用整数的。所以，这里为二进制串任意选择的长度 256 允许 64 层嵌套，通常这是可以扩展的。
作为附加的功能，该函数还测试新行的全局位置是否已经出现在父路径中。如果的确如此，那么就会产生一个运行时错误。如果没有显式地指定，Oracle 不允许循环。所以这个功能使得这个 DB2 查询是可兼容的。
下面这个与之前 CONNECT BY 版本对应的例子很好地展示了对这个 UDF 的使用：

1 WITH source(empid, name, salary, mgrid, rownum) AS
2 (SELECT empid, name, salary, mgrid,
3 ROW_NUMBER() OVER(ORDER BY salary)
4 FROM emp),
5 n(empid, name, salary, level, pos) AS
6 (SELECT empid, name, salary, 1,
7 CONNECT_BY_POS('', 0, rownum)
8 FROM source
9 WHERE name = 'Goyal'
10 UNION ALL
11 SELECT nplus1.empid, nplus1.name,
12 nplus1.salary, level + 1,
13 CONNECT_BY_POS(n.pos, level, rownum)
14 FROM source AS nplus1, n
15 WHERE n.empid = nplus1.mgrid)
16 SELECT name, level, salary
17 FROM n
18 ORDER BY pos;
NAME LEVEL SALARY
---------- ----------- -----------
Goyal 1 80000.00
Henry 2 51000.00
Shoeman 3 33000.00
Smith 3 34000.00
O'Neil 3 36000.00
Zander 2 52000.00
Barnes 3 41000.00
McKeough 3 42000.00
Scott 2 53000.00
9 record(s) selected

这期间发生了什么呢？通过添加另一个 CTE source，我们可以根据 ORDER SIBLINGS BY 请求的顺序对所有行发出一个全局顺序。之后，递归 CTE n 引用 source 来获得 rownum。查询的其他部分在前面已经解释过了。实际上，CONNECT_BY_POS() 产生一个二进制串 PATH，而不像 SYS_CONNECT_BY_PATH() 那样产生常规字符串。最后，该查询按照提供层次结构 pos 中的位置的路径排序。

这时，我想回到 CONNECT BY 查询中的 ON 子句。任何连接，甚至更复杂的查询，都可以用同样的方法通过将递归源的组成放入一个 CTE 中来表达。

NOCYCLE 关键字

至此我已经介绍了记录递归路径和在运行时检测递归的功能，实际上还可以映射 Oracle 的 CONNECT BY 查询语法中的 NOCYCLE 关键字。简言之，NOCYCLE 防止递归进入循环。对于第 (n + 1) 步，任何在祖先中已经存在的候选行都将被忽略。在 Oracle 中，使用方法如下：

1 SELECT name, level, salary
2 FROM emp
3 START WITH name = 'Goyal'
4 CONNECT BY NOCYCLE PRIOR empid = mgrid

给定祖先路径，该语义的映射很简单。只需再用一个 UDF 就足够了。如果检测到循环，该 UDF 将返回 0。如果没有检测到循环，则返回 1。注册了该 UDF 之后，我还将在管理链中引入一个循环。‘Urbassek’现在应该是‘O'Neil’的下属。

1 CREATE function CONNECT_BY_NOCYCLE
2 (N VARCHAR(256) FOR BIT DATA,
3 LEVEL INTEGER,
4 NPLUS1 BIGINT)
5 RETURNS SMALLINT
6 SPECIFIC CONNECT_BY_NOCYCLE
7 EXTERNAL NAME 'connect_by!connect_by_nocycle'
8 NOT FENCED RETURNS NULL ON NULL INPUT
9 DETERMINISTIC NO SQL NO EXTERNAL ACTION
10 LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;
11 UPDATE emp SET mgrid = 7 WHERE name = 'Urbassek';
12 WITH source(empid, name, salary, mgrid, rownum) AS
13 (SELECT empid, name, salary, mgrid,
14 ROW_NUMBER() OVER(ORDER BY salary)
15 FROM emp),
16 n(empid, name, salary, level, pos) AS
17 (SELECT empid, name, salary, 1,
18 CONNECT_BY_POS('', 0, rownum)
19 FROM source
20 WHERE name = 'Goyal'
21 UNION ALL
22 SELECT nplus1.empid, nplus1.name,
23 nplus1.salary, level + 1,
24 CONNECT_BY_POS(n.pos, level, rownum)
25 FROM source AS nplus1, n
26 WHERE n.empid = nplus1.mgrid
27 AND CONNECT_BY_NOCYCLE(n.pos, level, rownum) = 0
27 SELECT name, level, salary
28 FROM n
29 ORDER BY pos;
NAME LEVEL SALARY
---------- ----------- -----------
Goyal 1 80000.00
Henry 2 51000.00
Shoeman 3 33000.00
Smith 3 34000.00
O'Neil 3 36000.00
Urbassek 4 95000.00
Mills 5 70000.00
Monroe 6 50000.00
Jones 7 30000.00
Hall 7 35000.00
Lindsay 7 38000.00
Kim 7 40000.00
Aaron 6 54000.00
Zander 2 52000.00
Barnes 3 41000.00
McKeough 3 42000.00
Scott 2 53000.00
17 record(s) selected

您可以看到，‘Urbassek’已经被添加到输出中，此外输出还包括‘Mills’的所有属下。但是 ‘Goyal’没有再次添加进来。这样当然很好，但是如何发现循环的所在呢？

CONNECT_BY_ISCYCLE

为了“标出”递归中的循环，Oracle 提供了另一个伪列，即 CONNECT_BY_ISCYCLE。如果当前行是循环的一部分，则这个列返回 1，否则返回 0。

1 SELECT name, level,
2 CONNECT_BY_ISCYCLE AS cycle
3 FROM emp
4 START WITH name = 'Goyal'
5 CONNECT BY NOCYCLE PRIOR empid = mgrid

为了将该特性映射到 DB2，我将再次引入一个 UDF。CONNECT_BY_ISCYCLE() 用于检测循环，就像 CONNECT_BY_NOCYCLE() 做的那样。但是，当 CONNECT_BY_ISCYCLE() 检测到一个循环时，它还将给定的路径与另一个路径进行比较。如果两个路径在循环中重叠，则返回 1，否则返回 0。下面是这个 UDF 的声明：

1 CREATE FUNCTION CONNECT_BY_ISCYCLE
2 (POS VARCHAR(256) FOR BIT DATA,
3 LEVELPOS INTEGER,
4 NPLUS1 BIGINT,
5 POSANCESTOR VARCHAR(256) FOR BIT DATA,
6 LEVELANCESTOR INTEGER)
7 RETURNS SMALLINT
8 SPECIFIC CONNECT_BY_ISCYCLE
9 EXTERNAL NAME 'connect_by!connect_by_iscycle'
10 NOT FENCED RETURNS NULL ON NULL INPUT
11 DETERMINISTIC NO SQL NO EXTERNAL ACTION
12 LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

为达到目的，这个新查询必须：

返回递归中的所有行。
加入每个行的子行，以便进行比较。
驱动每个子行的递归步骤，以检测那一步是否会导致循环。
最后需要一个三路（three-way）连接：递归输出上的自连接，和对数字编号的源的引用，类似于在原先递归中所做的那样。这样做有一个优点：它足以发现将引入递归的任何子行。这里没有必要返回所有子行，实际上这样做反而有害，因为我们不想返回重复的行。注意，通过一点儿小小的变动，可以计算出一个给定行参与了多少个循环。这个谜就让有兴趣的读者自己去解开。

1 WITH source(empid, name, salary, mgrid, rownum) AS
2 (SELECT empid, name, salary, mgrid,
3 ROW_NUMBER() OVER(ORDER BY salary)
4 FROM emp),
5 n(empid, name, mgrid, level, pos) AS
6 (SELECT empid, name, mgrid, 1,
7 CONNECT_BY_POS('', 0, rownum)
8 FROM source
9 WHERE name = 'Goyal'
10 UNION ALL
11 SELECT nplus1.empid,
12 nplus1.name,
13 nplus1.mgrid,
14 level + 1,
15 CONNECT_BY_POS(n.pos, level, rownum)
16 FROM source AS nplus1, n
17 WHERE n.empid = nplus1.mgrid
18 AND CONNECT_BY_NOCYCLE(n.pos, level, rownum) = 0),
19 cycles(name, level, pos, iscycle) AS
20 (SELECT name, level, pos,
21 COALESCE((SELECT 1 AS iscycle
22 FROM n
23 LEFT OUTER JOIN source
24 ON n.mgrid = source.empid
25 WHERE CONNECT_BY_ISCYCLE(n.pos,
26 n.level,
27 source.rownum,
28 ancestor.pos,
29 ancestor.level) = 1
30 FETCH FIRST ROW ONLY), 0) AS iscycle
31 FROM n AS ancestor)
32 SELECT name, level, iscycle
33 FROM cycles
34 ORDER BY pos;
NAME LEVEL ISCYCLE
---------- ----------- -----------
Goyal 1 1
Henry 2 1
Shoeman 3 0
Smith 3 0
O'Neil 3 1
Urbassek 4 1
Mills 5 1
Monroe 6 0
Jones 7 0
Hall 7 0
Lindsay 7 0
Kim 7 0
Aaron 6 0
Zander 2 0
Barnes 3 0
McKeough 3 0
Scott 2 0
17 record(s) selected

这只是一小部分的工作，但至少现在已经获得一定成果了。像所有美好的故事一样，现在该添加一个好莱坞式的结局，让一切看上去是那么美妙。

CONNECT_BY_ISLEAF

与 CONNECT_BY_ISCYCLE 相比，CONNECT_BY_ISLEAF 更轻量一些。这个伪列只是当一个给定行在递归中是叶子的时候返回 1。换句话说：该行不会产生任何其他的行。在这个例子中，叶子指不是经理的雇员。

1 SELECT name, level,
2 CONNECT_BY_ISLEAF AS isleaf
3 FROM emp
4 START WITH name = 'Goyal'
5 CONNECT BY PRIOR empid = mgrid

实际上，这里只需要剔除之前的大部分相加。为增加可读性，我们重新开始，‘Urbassek’同样还是他自己的上司。还应注意，我去掉了前面的 NOCYCLE 选项，以便把注意力放在现在关注的问题上。毕竟，没有必要停留在过去，尤其是在过去的知识已经掌握的时候。现在，这个查询几乎没有多余的部分。

1 UPDATE emp SET mgrid = NULL WHERE name = 'Urbassek';
2 WITH n(empid, name) AS
3 (SELECT empid, name
4 FROM emp
5 WHERE name = 'Goyal'
6 UNION ALL
7 SELECT nplus1.empid, nplus1.name
8 FROM emp as nplus1, n
9 WHERE n.empid = nplus1.mgrid)
10 SELECT name,
11 COALESCE((SELECT 0 FROM emp
12 WHERE n.empid = emp.mgrid
13 FETCH FIRST ROW ONLY), 1) AS isleaf
11 FROM n;
NAME ISLEAF
---------- -----------
Goyal 0
Zander 0
Henry 0
Scott 1
McKeough 1
Barnes 1
O'Neil 1
Smith 1
Shoeman 1
9 record(s) selected

这的确不难。除非 Oracle 引入了我不知道的其他伪列或关键字，否则 Oracle 所有的递归特性现在都被映射到 DB2 UDB for Linux、UNIX 和 Windows。

结束语

在本文中，我提供了从 Oracle 型 CONNECT BY 递归查询语法到 DB2 中遵从标准的使用 UNION ALL 的 递归公共表表达式 的通用映射。所有映射都运行得很好，并且我展示了 DB2 的递归特性至少是可以表达的。

虽然 Oracle 语法要简练一些，因为它为各种常见的语义提供了关键字，但是这也意味着它的表达能力更弱，因为如果不改变 DBMS，就很难添加新的语义。

作为试图将应用程序从 Oracle 移植到 DB2 的 ISV 或终端用户，现在您不用太担心这一点了。您现在只需知道如何从 Oracle 翻译到 DB2。本文的主要目的就是向您展示这一点。当您对 SQL 标准语法感到得心应手的时候，我相信您将从 DB2 的表达能力中发现价值，虽然这意味着要多写几行代码。

后边增加一点自己的经验

CREATE TABLE "DB2ADMIN"."FIT_TC"
("CUST_ID" DECIMAL(17, 0),
"TCNAME" VARCHAR(20),
"PACKNAME" VARCHAR(20),
"FITADD" VARCHAR(100),
"ADDFEE" INTEGER,
"FITLOS" VARCHAR(100),
"LOSFEE" INTEGER
)

select * from "DB2ADMIN"."FIT_TC";

1 '49元包180' '必选包1' '34元包2元' '-1' '' '-1'
1 '49元包180' '可选包3' '' '-1' '' '-1'
1 '49元包180' '可选包2' '' '-1' '' '-1'
1 '49元包180' '可选包1' '35元包80元' 10 '' '-1'
1 '酷灵通' '必选包1' '' '-1' '' '-1'
1 '酷灵通' '可选包1' '20元包100元' '-1' '' '-1'
1 '酷灵通' '必选包1' '' '-1' '' '-1'

with
tt as (
select row_number() over (partition by TCNAME order by TCNAME) as parent,
row_number() over (partition by TCNAME order by TCNAME) as child, TCNAME,"PACKNAME"||"FITADD" as dw from "DB2ADMIN"."FIT_TC" where FITADD <>''
),
t2 (parent, child,TCNAME, dw) as (
select parent, child,TCNAME, cast(dw as varchar(200)) from tt
where parent=1 and child=1
union all
select t2.child, t2.child+1, t2.TCNAME,cast(rtrim(t2.dw) || ',' || rtrim(tt.dw) as varchar(200))
from t2, tt where t2.child=tt.parent-1 and t2.TCNAME=tt.TCNAME)
select dw, child,TCNAME from t2
where t2.child=(select max(child) from tt where t2.TCNAME=tt.TCNAME)
;

'可选包120元包100元' 1 '酷灵通'
'必选包134元包2元,可选包135元包80元' 2 '49元包180'

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/694276/viewspace-51431/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/694276/viewspace-51431/