CONNECT BY 子句

CONNECT BY 子句是对 SQL 的 ANSI/ISO 标准的扩展。

语法

用法

如果您包括 START WITH 子句，则它指定的搜索条件应用于为层级查询生成第一个中间结果集。这由那些在 FROM 子句中指定的表中满足 START WITH 条件为真的行构成。

如果省略 START WITH 子句，则没有可用的 START WITH 条件作为过滤器，且第一个中间结果集是 FROM 子句指定的表中的行的全集。

通过应用 CONNECT BY 搜索条件，CONNECT BY 子句生成连续的中间结果集，直到当迭代生成空结果集时此递归过程终结为止。

NOCYCLE 关键字

由 CONNECT BY 子句的递归查询返回的行必须为简单的层级的一部分。如果该查询返回一行，该行既是另一节点的祖先又是另一节点的后代，则包括该层级子句的 SELECT 语句失败并报错。此拓扑称为循环。

您可在 CONNECT BY 关键字与 CONNECT BY 子句的条件规范之间包括 NOCYCLE 关键字来过滤掉任何会导致层级查询失败并报错 -26079 的那些行，出错的原因是在中间的结果集中有循环。

例如，对于在主题层级查询子句中描述的 employee 表的层级数据集，下列 UPDATE 语句会引起其 empid 值为 5 和 17 的员工的循环：

UPDATE employee SET mgrid = 5 WHERE name = 'Urbassek';

在通过上述 UPDATE 语句修改了层级数据集之后，下列查询（省略 NOCYCLE 关键字）失败：

SELECT empid, name, mgrid , CONNECT_BY_ISLEAF leaf
        FROM employee
        START WITH name = 'Goyal'
        CONNECT BY PRIOR empid = mgrid;

当最后的 CONNECT BY 步骤检测到员工 McKeough 是循环的一部分时，发出错误 -26079：

      empid name             mgrid   leaf 
        
        16 Goyal               17      0
        14 Scott               16      1
        12 Henry               16      0
        9 Shoeman             12      1
        8 Smith               12      1
        7 O'Neil              12      1
        11 Zander              16      0
        6 Barnes              11      1
        5 McKeough            11      0
        
        26079: CONNECT BY query resulted in a loop/cycle.
        Error in line 8
        Near character position 28

您可在 CONNECT BY 关键字与 CONNECT BY 子句的条件规范之间包括 NOCYCLE 关键字来过滤掉会导致层级查询失败并报错 -26079 的任何行，该错误是由于在中间的结果集中有循环所致。下列查询在 CONNECT BY 子句中包括 NOCYCLE 关键字，这与在 Projection 子句中包括 CONNECT_BY_ISCYCLE 伪列而导致失败的查询不同。

SELECT empid, name, mgrid, CONNECT_BY_ISLEAF leaf, CONNECT_BY_ISCYCLE cycle
        FROM employee
        START WITH name = 'Goyal'
        CONNECT BY NOCYCLE PRIOR empid = mgrid;
        
        empid name             mgrid   leaf  cycle 
        
        16 Goyal               17      0      0
        14 Scott               16      1      0
        12 Henry               16      0      0
        9 Shoeman             12      1      0
        8 Smith               12      1      0
        7 O'Neil              12      1      0
        11 Zander              16      0      0
        6 Barnes              11      1      0
        5 McKeough            11      0      0
        17 Urbassek             5      0      1
        15 Mills               17      0      0
        13 Aaron               15      1      0
        10 Monroe              15      0      0
        4 Lindsay             10      1      0
        3 Kim                 10      1      0
        2 Hall                10      1      0
        1 Jones               10      1      0
        
        17 row(s) retrieved.

由于 NOCYCLE 关键字使得在检测到了循环之后 CONNECT BY 子句能够继续处理，从在先前的示例中失败了的 CONNECT BY 步骤返回了 Urbassek，并继续处理直到已返回了该结果集中的所有行为止。在上述输出显示中，leaf 为 CONNECT_BY_ISLEAF 伪列的别名，且 cycle 为 CONNECT_BY_ISCYCLE 伪列的别名，在 Projection 子句中同时声明两个别名。在这些结果中，Urbassek 在 cycle 中被标记为循环的起因。

上述结果集表明，通过更改该行中的 mgrid 值，该行已识别了 McKeough 作为 Urbassek 的管理者，可从 employee 表移除循环：

UPDATE employee SET mgrid = NULL WHERE empid = 17;