Oracle 11g Release 1 (11.1) SQL_层级查询（详）

http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries003.htm

本文内容

• HR 模式
•     Oracle 示例 Schema
•     安装 HR 模式
• 演示层级查询
• 层级查询
• 参考

HR 模式

Oracle 示例 Schema

Oracle 有几个示例 Schema，包括，HR（Human Resources）、OE（Order Entry）、OC（ Online Catalog）、PM（Product Media ）、IX（Information Exchange） 和 SH（Sales History）。这几个 Schema 有依赖关系。在一起是一个完整的数据库。详细文档参看 http://docs.oracle.com/cd/B28359_01/server.111/b28328/toc.htm#BEGIN。

这些 Schema 可以通过如下方式安装：

• 使用数据库配置助手（Database Configuration Assistant）
• 手动安装
• 通过 mksample.sql 脚本重置，将示例恢复成初始状态

安装 HR 模式

本文的演示使用 HR 模式。参看 http://docs.oracle.com/cd/B28359_01/server.111/b28328/scripts.htm#Cihgfecd，列出了创建 HR 模式的脚本，及 HR 模式包含的对象和表。手动安装 HR 模式，参看 http://docs.oracle.com/cd/B28359_01/server.111/b28328/installation.htm#sthref22。

演示层级查询

示例 1：演示 CONNECT BY。用 CONNECT BY 来定义员工和经理的关系。

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

 

（不是全部结果。）

示例 2：演示 LEVEL。本例与示例 1 一样。只是使用了 LEVEL  伪列。

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

（不是全部结果。）

示例 3：演示 START WITH。用 START WITH 子句指定层级的根数据行，并使用 ORDER SIBLINGS BY 保持层级内的顺序。

不是全部结果。在 hr.employees 表，员工 Steven King 是公司职位最高的。而在上面的结果中，如下所示：

部门编号为 80 的员工 John Russell。若更新该表，让 John Russell 为该公司职位最高的人，成为 Steven King 的上级，则就存在了一个“循环“。

UPDATE employees SET manager_id = 145 WHERE employee_id = 100;

再执行下面 SQL，就会报错。

SELECT last_name "Employee",
       LEVEL,
       SYS_CONNECT_BY_PATH(last_name, '/') "Path"
  FROM employees
 WHERE level <= 3
   AND department_id = 80
 START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id
       AND LEVEL <= 4;

此时，可以使用 NOCYCLE  参数。如示例 4 所示。

示例 4：演示 CONNECT BY 的 NOCYCLE 参数。尽管有循环，但 Oracle 也会返回。CONNECT_BY_ISCYCLE 伪列显示包含循环的行。

SELECT last_name "Employee",
       CONNECT_BY_ISCYCLE "Cycle",
       LEVEL,
       SYS_CONNECT_BY_PATH(last_name, '/') "Path"
  FROM employees
 WHERE level <= 3
   AND department_id = 80
 START WITH last_name = 'King'
CONNECT BY NOCYCLE PRIOR employee_id = manager_id
       AND LEVEL <= 4
 ORDER BY "Employee", "Cycle", LEVEL, "Path";

（不是全部结果。）

该结果包含一个循环。

示例 5：演示 CONNECT_BY_ROOT。返回部门编号为 110 的每个员工的 last name，员工之上的所有上级，经理和员工之间的职位级别差，和层级路径。

SELECT last_name "Employee",
       CONNECT_BY_ROOT last_name "Manager",
       LEVEL - 1 "Pathlen",
       SYS_CONNECT_BY_PATH(last_name, '/') "Path"
  FROM employees
 WHERE LEVEL > 1
   and department_id = 110
CONNECT BY PRIOR employee_id = manager_id
 ORDER BY "Employee", "Manager", "Pathlen", "Path";

 

示例 6：演示使用 GROUP BY 子句，返回部门编号为 110 的所有员工在层级中的工资总额。

SELECT name, SUM(salary) "Total_Salary"
  FROM (SELECT CONNECT_BY_ROOT last_name as name, Salary
          FROM employees
         WHERE department_id = 110
        CONNECT BY PRIOR employee_id = manager_id)
 GROUP BY name
 ORDER BY name, "Total_Salary";

层级查询

若一个表包含层级数据，则可以使用层级查询子句，以层级顺序来获得数据行。如下图所示层级查询子句的语法。

hierarchical_query_clause::=

START WITH 指定层级的根数据行。

CONNECT BY 指定父数据行与子数据行的层级关系。

• NOCYCLE 参数让 Oracle 即使数据存在 CONNECT BY 循环，也返回。可以与  CONNECT_BY_ISCYCLE 一起使用，查看包含循环的数据行。
• 在层级查询中，条件中的一个表达式必须具备指向父行的 PRIOR 运算符。例如：

... PRIOR expr = expr

或

... 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 使用这些计算的信息，通过如下步骤形成层级：

1. Oracle 选择层级的根行——满足 START WITH 子句的那些行。
2. Oracle 选择每个根行的子行。每个子行必须满足 CONNECT BY 条件。Oracle 一直递归选择这样的子行。
3. 若查询包含没有表连接的 WHERE 子句，则 Oracle 从层级中去掉所有不满足 WHERE 子句的行。Oracle 单独计算每行的条件，而不是删除不满足条件的行的所有子行。
4. Oracle 按顺序返回行。

若查找一个父行的子行，Oracle 计算对父行的 CONNECT BY 条件 PRIOR 表达式，和表中每行的其他表达式。返回条件为真的那些行。 CONNECT BY 条件可以包含其他进一步过滤行的条件。

若 CONNECT BY 条件导致了循环，则 Oracle 返回一个错误。

备注：

在层级查询中，不能指定 ORDER BY 或 GROUP BY，它们会覆盖 CONNECT BY 结果的层级顺序。若想排序具有相同父行的兄弟行，则可用 ORDER SIBLINGS BY 子句。

参考

• Oracle 11g Release 1 (11.1) SQL_层级查询（概）