oracle 层次查询 connect by

最新推荐文章于 2024-03-11 20:00:09 发布

「已注销」

最新推荐文章于 2024-03-11 20:00:09 发布

阅读量988

点赞数

分类专栏：数据库文章标签： oracle 数据库 sql

本文链接：https://blog.csdn.net/a140981/article/details/127633433

版权

本文深入介绍了Oracle数据库的层次查询，重点讨论了CONNECT BY操作、PRIOR和CONNECT_BY_ROOT运算符，以及Hierarchical Query的伪列如CONNECT_BY_ISCYCLE、CONNECT_BY_ISLEAF和LEVEL。通过示例解释了如何处理层次结构中的循环、查找叶子节点以及构建路径。

摘要由CSDN通过智能技术生成

Hierarchical Queries

语法

connect by [nocycle] condition [start with condition]
start with condition connect by [nocycle] condition

condition

start with 指定层次查询的 root row

connect by 指定层次查询中 parent rows 和 cild rows 的关系

NOCYCLE 参数指示 Oracle 数据库从查询中返回行，即使数据中存在 CONNECT BY 循环。将此参数与 CONNECT_BY_ISCYCLE 伪列一起使用以查看哪些行包含循环。有关详细信息，请参阅 CONNECT_BY_ISCYCLE 伪列。
在分层查询中，条件中的一个表达式必须使用 PRIOR 运算符限定以引用父行。例如，

... PRIOR expr = expr
or
... expr = PRIOR expr

如果 CONNECT BY 条件是复合条件，则只有一个条件需要 PRIOR 运算符，尽管您可以有多个 PRIOR 条件。例如：

CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...
CONNECT BY PRIOR employee_id = manager_id and 
           PRIOR account_mgr_id = customer_id ...

PRIOR 是一元运算符，与一元 + 和 - 算术运算符具有相同的优先级。它为分层查询中当前行的父行计算紧随其后的表达式。

PRIOR 最常用于使用相等运算符比较列值时。（PRIOR 关键字可以在运算符的任一侧。） PRIOR 使 Oracle 使用列中父行的值。在 CONNECT BY 子句中理论上可以使用除等号 (=) 以外的运算符。但是，这些其他运算符创建的条件可能会导致通过可能的组合的无限循环。在这种情况下，Oracle 在运行时检测到循环并返回错误。

CONNECT BY 条件和 PRIOR 表达式都可以采用不相关子查询的形式。但是，CURRVAL 和 NEXTVAL 不是有效的 PRIOR 表达式，因此 PRIOR 表达式不能引用序列。

您可以通过使用 CONNECT_BY_ROOT 运算符来进一步细化层次查询，以限定选择列表中的列。此运算符不仅返回直接父行，而且返回层次结构中的所有祖先行，从而扩展了层次查询的 CONNECT BY [PRIOR] 条件的功能。

Oracle 按如下方式处理分层查询：

如果存在连接，则首先评估连接，无论连接是在 FROM 子句中指定还是使用 WHERE 子句谓词。
评估 CONNECT BY 条件。
评估任何剩余的 WHERE 子句谓词。

然后，Oracle 使用来自这些评估的信息通过以下步骤形成层次结构：

Oracle 选择层次结构的根行——那些满足 START WITH 条件的行。
Oracle 选择每个根行的子行。每个子行必须满足关于其中一个根行的 CONNECT BY 条件的条件。
Oracle 选择连续几代的子行。 Oracle 首先选择步骤 2 中返回的行的子代，然后选择这些子代的子代，以此类推。 Oracle 总是通过评估与当前父行相关的 CONNECT BY 条件来选择子行。
如果查询包含没有连接的 WHERE 子句，则 Oracle 从层次结构中删除所有不满足 WHERE 子句条件的行。 Oracle 对每一行单独评估此条件，而不是删除不满足条件的行的所有子行。
Oracle 按图 9-1 所示的顺序返回行。在图中，孩子出现在父母的下方。有关分层树的说明，请参见图 3-1。

Description of Figure 9-1 follows

为了找到父行的子行，Oracle 计算父行的 CONNECT BY 条件的 PRIOR 表达式和表中每一行的另一个表达式。条件为真的行是父项的子项。 CONNECT BY 条件可以包含其他条件以进一步过滤查询选择的行。

如果 CONNECT BY 条件导致层次结构中出现循环，则 Oracle 返回错误。如果一行既是另一行的父（或祖父母或直接祖先）又是子（或孙子或直接后代），则发生循环。

注意：在分层查询中，不要指定 ORDER BY 或 GROUP BY，因为它们会覆盖 CONNECT BY 结果的分层顺序。如果要对同一父级的兄弟行进行排序，请使用 ORDER SIBLINGS BY 子句。请参见 order_by_clause。

Hierarchical Query Examples

CONNECT BY Example

以下分层查询使用 CONNECT BY 子句来定义员工和经理之间的关系：

SELECT employee_id, last_name, manager_id
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID LAST_NAME                 MANAGER_ID
----------- ------------------------- ----------
        101 Kochhar                          100
        108 Greenberg                        101
        109 Faviet                           108
        110 Chen                             108
        111 Sciarra                          108
        112 Urman                            108
        113 Popp                             108
        200 Whalen                           101
        203 Mavris                           101
        204 Baer                             101
. . .

LEVEL Example

下一个示例与前面的示例类似，但使用 LEVEL 伪列来显示父行和子行：

SELECT employee_id, last_name, manager_id, LEVEL
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID LAST_NAME                 MANAGER_ID      LEVEL
----------- ------------------------- ---------- ----------
        101 Kochhar                          100          1
        108 Greenberg                        101          2
        109 Faviet                           108          3
        110 Chen                             108          3
        111 Sciarra                          108          3
        112 Urman                            108          3
        113 Popp                             108          3
        200 Whalen                           101          2
        203 Mavris                           101          2
        204 Baer                             101          2
        205 Higgins                          101          2
        206 Gietz                            205          3
        102 De Haan                          100          1
...

START WITH Examples

下一个示例添加一个 START WITH 子句来指定层次结构的根行，并使用 SIBLINGS 关键字添加一个 ORDER BY 子句来保持层次结构内的顺序：

SELECT last_name, employee_id, manager_id, LEVEL
      FROM employees
      START WITH employee_id = 100
      CONNECT BY PRIOR employee_id = manager_id
      ORDER SIBLINGS BY last_name;

LAST_NAME                 EMPLOYEE_ID MANAGER_ID      LEVEL
------------------------- ----------- ---------- ----------
King                              100                     1
Cambrault                         148        100          2
Bates                             172        148          3
Bloom                             169        148          3
Fox                               170        148          3
Kumar                             173        148          3
Ozer                              168        148          3
Smith                             171        148          3
De Haan                           102        100          2
Hunold                            103        102          3
Austin                            105