使用 DB2 Connect By 的分级查询(转)
以递归方式处理数据关系的一种新方法
简介： 根据 SQL 标准，分级数据（如组织图和材料单）或双向数据（如航班中转）可以通过使用递归通用表表达式 (RCTE) 进行评估。DB2 for i 的 V5R4 版本中提供了 RCTE 功能。其他的数据库（如 Oracle）使用了一个非标准的数据查询方法，叫做分级查询子句。为了最大限度地提高可移植性，我们通过PTF SF99701 V9 版本使 DB2 for I 支持了这个功能。本文将介绍分级查询子句的语法，以及如何组合使用新的运算符、伪列和特殊标量函数实现它。
本文的标签： odbc_(开放数据库连接性), sql_(结构化查询语言)
根据 Wikipedia：递归（拉丁语 recurrere -> currere = run 和 re = return）是以自相似性的方式循环某些项的过程。在数学和计算机科学中，递归指的是基于自己 定义 来定义函数的一种方法。
存储在表（或物理文件）中的递归数据指向同一表格/物理文件的其他行/记录的另一个列/字段中的数据。
递归数据之间的关系可以是分层的（单向，比如 父母 -> 孩子，经理 -> 下属）、双向的或循环的（如航班中转，法兰克福 -> 纽约，但也可以是纽约 -> 法兰克福）。
在接下来的所有示例中，都会使用其中的表 STAFF（组织图）或 FLIGHTS（航班中转）其中的一个。
下图显示了一个小型组织图和存储数据的表 STAFF。表 STAFF 包含 3 个列：
- EMPLOYEE：雇员编号
- NAME：雇员的姓名
- BOSS：此雇员的直接主管的雇员编号
图 1：分层关系 - 组织图 - STAFF
下一个图显示一个含有航班中转的图表。在分析双向数据时，会出现一个循环，最终会产生一个无限循环（用红色标识）。该图还显示了包含航班中转的表 FLIGHT 的摘要。在这个样例数据中，您将会发现到柏林和法兰克福市之间的线路，还会发现到法兰克福市和柏林之间的连接。
表 FLIGHTS 包含以下的列：
- DEPARTURE - 出发城市
- ARRIVAL - 抵达城市
- PRICE - 连接费用
图 2：双向关系 - 航班中转 – FLIGHTS
根据 SQL 标准，评估分层或双向数据的方法为递归通用表表达式 (RCTE)。
RCTE 可分为二个部分：
- 第一个部分，起始 SELECT 语句，预先定义了起始点（如组织图中的管理人员或航班中转里的起航）。
- 第二部分代表迭代或递归。第二个SELECT 语句通过将父数据链接至子数据与公共表表达式 (CTE) 联接。使用 UNION ALL 子句，可将当前的迭代 SELECT 语句的结果与初始 SELECT 语句的结果以及之前的迭代返回的所有结果合并在一起。
在 RTCE 与终止 SELECT 语句之间必须指定额外的子句，该子句充许预先定义返回结果的顺序。
- SEARCH BREADTH FIRST 是默认顺序，在返回下一层数据前会先返回同级的所有数据。
- 如果必须在返回下一个父数据的数据之前返回所有的子数据（所有级别上），则必须指定 SEARCH DEPTH FIRST。
为了避免双向关系中出现无限循环，必须在 RCTE 和终止 SELECT 语句之间指定一个 CYCLE 子句。一旦检测到循环，就会设置 CYCLE 子句中已定义好的适当标记，并停止分支的递归查询过程。
注意：要检测循环，在检测循环时，系统只会检查迭代 select 语句返回的结果。而不会检查起始 select 的返回的结果。
下图显示了查询递归数据的 RCTE 结构。
图 3：递归通用表表达式 (RCTE) - 结构
在下一个示例中，将使用 RTCE 来返回 Bauer 经理的所有下属（不只是直接下级中的下属）。
RCTE 和终止 SELECT 语句都返回了 4 个列，分别是递归的层数、下属的雇员编号、直属经理的姓名以及直属经理的雇员编号。
在起始 SELECT 语句中，级别固定为 1，在 WHERE 子句中，会硬编码 Bauer 经理的姓名，以便预定义起始点。
在迭代 SELECT 语句中，通过比较表 RCTE 中的雇员编号（列 EMPLOYEE）与表 STAFF 中的管理人员编号（列 BOSS），将 RTCE STAFFLIST 与表 STAFF 联合在一起。递归的层数的值都会增加 1，级别值都会增加 1。此外，应指定 SEARCH DEPTH FIRST 子句。临时创建的字段 SORT 用于 ORDER BY 子句内，以便按深度优先顺序获得返回的结果。
图 4：示例 RCTE – 确定所有下属
正如 DB2 for i 技术更新 wiki 中所宣传的那样，我们在IBM i 7.1 版本中通过PTF (PTF SF99701 Version 9)支持了分级查询语句。这个新的功能提供了分级查询子句。这个新的功能提供了以递归方式读取数据关系的另一种方法。与 RCTE 语法相比，分级查询子句较更容易理解。分级查询支持也符合于 Oracle 数据库所用的非标准方法。
分级查询子句不是作为公共表表达式来实现的，而是作为子句被指定为子 SELECT 语句的一部分，并且必须在指定 WHERE、GROUP BY 或 HAVING 子句之后指定。
分级查询子句由两部分组成：
- START WITH 谓词定义了起始点，可以与 RCTE 中的起始 SELECT 语句相比较。可以通过将 SQL 谓词（如 IN 或 LIKE）或逻辑运算符将多个条件语句结合来定义多个起始点。
- CONNECT BY 谓词定义了父元素和子元素之间的连接关系。对于双向关系，必须在 CONNECT BY 部分指定关键词NOCYCLE 。如果没有为双向关系指定 NOCYCLE，就不会执行查询，而是返回一个错误（与 RCTE 不同，RCTE 在没有适当的 CYCLE 子句时会以无限循环结束 ）。
下列示例显示使用一个 SELECT 语句和一个分级查询子句来返回 Meier 经理（雇员编号为 101）的所有直接下属。
图 5：分级查询子句 – 下一层
您可能会感到失望，因为这类查询只需使用简单 WHERE 子句（如 WHERE BOSS = 101）即可实现。
要获取所有级别上的所有下属，必须将 PRIOR 运算符添加到分级查询子句的 CONNECT BY 部分。PRIOR 运算符必须为要解析的列加上前缀。例如，如果您想要确定某个特定经理（列 BOSS）的所有下属（列 EMPLOYEE），则必须将 PRIOR 放在 EMPLOYEE 列的前面。PRIOR 运算符可放在比较运算符的左边或右边。
在下面的示例中，返回了 Bauer 经理（雇员编号为 202）的所有下属（不只是下一级的下属）。在第一个语句中，PRIOR 用于等号的左边，而在第二个语句中，它被指定在另一边。两个语句返回的结果相同。
图 6：分级查询 – 运算符 PRIOR
如果联接键是一个复合键，那么必须使用 PRIOR 运算符为每个键所在的列加上前缀。
CONNECT BY PRIOR a.Department = b.Department
AND PRIOR Employee = Boss
|
在将先前查询的结果与表 STAFF 中的数据进行比较时，结果是按照深度优先顺序返回的，这意味着会先返回所有的子行，然后才能返回下一个子行（与 RCTE 相反，RCTE 在默认情况下按宽度优先顺序返回数据，比如逐级返回）。
即使先前查询的数据是按照搜索深度优先顺序返回的，但在同一个级别中，比如在编号为 405、404 和 403 的雇员中，或者在姓名 Hund、Schmidt 和 Jägerthere 中，没有特定的顺序。
添加一个 ORDER SIBLINGS BY 子句，该子句只能与分级查询子句组合使用，并且将按预先定义的顺序返回同级查询结果。类似于在常规的 ORDER BY 子句中，可以按升序或降序指定多个列。
在下一个示例中，会修改以前的查询，添加一个 ORDER BY SIBLINGS 子句，从按照 NAME 排序的结果中获取同级查询结果。以前是先返回 Becker（雇员编号为 303），然在修改后的查询中，先返回 Ackermann（雇员编号为 304），其下属 Hund、Jäger 和 Schmidt 也是按字母顺序排列的。
图 7：分级查询 – ORDER SIBLINGS BY
在先前的示例中，第一个列（如总经理）被指定为常数值。硬编码或更糟的重复硬编码值（比如 Select 语句和 Start with 子句中的 BOSS）绝不是一个好点子。只要在硬编码定义多个起始点（比如多个经理），工作区就无法再运行。
要避开硬编码和重复的硬编码值，可以使用 CONNECT_BY_ROOT 运算符，它总是返回其参数值，就像在初始步骤中那样。
在下列的示例中，必须确定雇员编号为 201 (Huber) 和 202 (Bauer) 的经理的所有下属或者雇员编号大于 303 的所有经理（只有编号为 304 的 Ackermann）的所有下属。
在此示例中，使用了 CONNECT_BY_ROOT 运算符来返回经理的相应下属的雇员编号。要将经理的姓名包含在内，则需要添加一个子 SELECT 语句，在该语句中，通过将经理的雇员编号（列 BOSS）与下属雇员编号（列 EMPLOYEE）联系起来确定表 STAFF 中的下属名称。
图 8：分级查询 – CONNECT_BY_ROOT 运算符
在分级查询中，提供了几个伪列。伪列是具有预先定义的名称的标识符，其中包含关于分级查询的信息；它的用途类似于表中的任何列。
- LEVEL – 伪列 LEVEL 返回一个整数值，表示所生成行的分层结构中的递归步骤。
- 对于通过 start with 子句生成的所有行，LEVEL 被设置为 1。
- 对于通过 CONNECT BY 子句的迭代生成的所有行，LEVEL 增加 1。
可在 ORDER BY 子句中使用伪列 LEVEL 按照搜索宽度优先顺序返回结果集。
- CONNECT_BY_ISCYCLE 返回一个设置为 0（未检测到循环）或 1（检测到循环）的小整数值。
伪列不仅可以用来检测循环，还可以使用伪列从结果中删除循环。
- CONNECT_BY_ISLEAF 返回一个设置为 0 或 1 的小整数值。
如果某个行是分层结构中的叶子节点，则返回的值为 1，否则返回值为 0 。在分层关系中，对于驻留在最低级别的所有元素，CONNECT_BY_ISLEAF 值都设置为 1。在双向关系中，CONNECT_BY_ISLEAF 返回与 CONNECT_BY_ISCYCLE 相同的值。
在下列示例中，在选择列表中指定伪列 LEVEL。还可以使用伪列值按照宽度优先顺序对查询结果进行排序。
图 9：分级查询 – 伪列 LEVEL
下面的示例中将显示使用 LEVEL 伪列的另一种方法。可在字符串中使用伪列 LEVEL，使分层结构变得可视。基于当前的级别值，会根据与当前级别有关的空格数对名称进行缩进。
图 10：分级查询 – 使分层结构变得可视
标量函数 SYS_CONNECT_BY_PATH 只能与分级查询组合使用。它构建了一行字符串，该字符串包含从 root 到当前行的所有元素。函数的结果会以 CLOB（字符型大型对象）数据类型的形式返回，可容纳 1 MB 的数据。
函数 SYS_CONNECT_BY_PATH 需要两个字符串表达式。
- 第一个表达式必须为串联的行值。
- 第二个表达式是为分隔元素而添加的运算符。
在下列的示例中，显示了法兰克福市和柏林之间的所有航班中转。
由于是双向数据，所以必须在 START WITH … CONNECT BY 子句中指定关键字 NOCYCLE。可通过在 WHERE 子句中指定伪列 CONNECT_BY_ISCYCLE 来排除导致循环的中转。
基于伪列 LEVEL 最大返回两个连接 (LEVEL <= 3)。
因为没有检查循环数据的起始点，并且不能再次连接到 “法兰克福市” ，所以将从 CONNECT BY 子句中删除该中转 (Arrival <> 'Frankfurt')。
可以使用 CONNECT_BY_ROOT 确定出发点（总是法兰克福市），并将它与 ARRIVAL SYS_CONNECT_BY_PATH 函数所生成字符串串联在一起，该函数将所有迭代中用破折号 (' – ') 分隔的所有 ARRIVAL 串联起来。
图11：函数 SYS_CONNECT_BY_PATH
不幸的是，SYS_CONNECT_BY_PATH 只能用于串联字符值。有时，您想要串联数字值，比如，计算航班中转的总成本。
不好的消息是，没有一个特殊的函数或运算符可以直接与分级查询子句结合使用来实现此操作。好消息是，有两种方法可以实现此目的，例如，要计算总成本，请执行以下操作：
- 第一个选项是使用一个 RCTE
- 第二个选项是使用函数 SYS_CONNECT_BY_PATH 构建一个字符串，将所有成本以加号 (+) 分隔的字符值形式连接在一起。并编写一个简单使用动态 SQL 的 SQL 用户定义函数 (UDF)，转换字符串中传入的公式并计算值。
SYS_CONNECT_BY_PATH 函数只可用来连接字符串（例如，不能将用它来计算总数）。
在 SQL 编程中，可以使用 SQL 命令 PREPARE 将含有任何（数学）公式的字符串转换成可执行的 SQL 语句。可以使用 SQL 命令 EXECUTE 来执行这个动态预备语句。
下列的 SQL 脚本包含用来创建 Calculate user-defined function (UDF) 的源代码。UDF 需要一个字符型大型对象 (CLOB)，它包含一个作为输入参数的数学公式（如 3+5*17）。这个传入的参数值会嵌套在另一个字符串中，以获得已计算出的和返回的公式的结果。
VALUES 语句提供了一个无需访问表或视图即可执行 SQL 语句的方法。VALUES … INTO 语句允许执行 SQL 表达式，并将结果返回至变量。语句字符串中的 ?（问号）是一个参数标识，代表用来返回结果的变量。
可以使用使用 SQL PREPARE 语句将完整的字符串（如嵌套在 VALUES … INTO、VALUES 组合中的导入公式）转换成可执行的 SQL 语句。用来检索结果的变量 RtnVal 与 USING 子句中的参数标记有关联。
如果发生错误，比如公式中包含无效的数据，就会激活后续处理程序，并返回一个负的默认值 (-999999999999.99)。
图 12：计算字符串 UDF - 示例
一旦生成 UDF，就可以将它与函数 SYS_CONNECT_BY_PATH 结合使用来计算总数。
在下列的示例中，列 LISTPRICES 显示了包含用加号 (+) 分隔的连接在一起的成本的字符串。要在 Cost 列中显示结果，则必须通过 Calculate UDF 来传递并处理此串联字符。
图13：UDF CALCULATE 和函数 SYS_CONNECT_BY_PATH
现在您应该能够使用下列语句创建具有分层或双向关系的所有类型的递归查询：
- 分级查询子句 (START WITH … CONNECT BY)
- 运算符 PRIOR、NOCYCLE 和 CONNECT_BY_ROOT
- 特殊的 ORDER SIBLINGS BY 子句
- 函数 SYS_CONNECT_BY_PATH
- 伪列 LEVEL、CONNECT_BY_ISCYCLE 和 CONNECT_BY_ISLEAF
- 递归通用表表达式 (RCTE)
此外，您应该能够将 UDF 与函数 SYS_CONNECT_BY_PATH 结合使用来计算递归关系中的总数。
目前为止，我们已经对分级查询子句进行了一些有趣的试验。
IBM i - DB2 for i SQL Reference - 7.1：第五章 – 查询
IBM developerWorks DB2 for i - 论坛
IBM developerWorks - IBM i 技术更新
