层次化查询

6.2.4  层次化查询（1）

尽管表默认呈现为行和列的二维表格，但是在很多时候，开发人员需要在二维表格中存储层次化的数据，比如一个组织架构图，需要保存组织的层次信息，制造业的物料清单需要具有产品的层次结构信息，家庭层次等，这些数据具有层次特性，Oracle提供了层次化查询的特性，使得可以通过SQL语句表中存储的具有层次化特性的数据转换为树状的        数据。

以组织架构图为例，一个公司的组织结构以树状结构表示如图6.3所示。

从这个树状结构中可以看到，树状层次中的每个元素都是树中的一个节点，因为节点具有层次关系，因此具有父节点与子节点，顶层的为根节点，没有子节点的节点为叶节点。

为了让一个或多个表具有层次关系，必须使用相关的字段将表关联起来。以HR方案中的employees表为例，该表中的员工包含一个名为MANAGER_ID的字段，该字段自引用到EMPLOYEE_ID字段，使得每一个员工可以具有一个指向组织架构中的父级。ER图如图6.4所示。

Oracle提供了对于ANSI SQL的扩展来简化对树状层次结构的遍历。Oracle提供了如下所示的3个构造来有效和有用地处理层次化查询问题。

START WITH..CONNECT BY子句。

PRIOR操作符。

LEVEL伪列。

通过在SELECT语句中包含START WITH和CONENCT BY子句，可以很容易地提取表中的层次结构的数据，其基本语法为：

1. [[START WITH condition1]  CONNECT BY condition2] 

START WITH condition1：指定层次结构中的根节点，所有满足condition1条件的都可以被考虑为根节点。如果没有指定START WITH子句，那么会使得所有的行都被考虑为根节点，这显然不符合树状查询的规律，也可以在condition1中包含子查询。

CONNECT BY condition2：指定层次结构中父行和子行之间的关系，这个关系是一个比较表达式，用来从当前的行开始比较其父行的列的相应值。condition2必须包含PRIOR操作符，用于指定列是来自父行，condition不能包含子查询。

注意：PRIOR是Oracle内置的操作符，仅用来处理层次化的查询。

在层次化的查询中，CONNECT BY子句指定父行和子行之间的关系，当在CONNECT BY条件中使用了PRIOR关键字后，在PRIOR关键字之后的表达式将被作为查询中当前列的父列进行计算，例如下面的示例语句将使用START WITH和CONNECT BY来查询员工表中的组织层次结构：

1. SQL> SELECT     employee_id, manager_id, first_name, last_name, hire_date,  
2.           FROM employees  
3.     --表示根节点为manager_id  
4.     START WITH manager_id IS NULL  
5.     --PRIOR表示父行的employee_id，等于当前行的manager_id  
6.     CONNECT BY PRIOR employee_id = manager_id;  
7.   EMPLOYEE_ID ID FIRST_NAME  LAST_NAME   HIRE_DATE         SALARY  
8.   ----------- ------------- ----------- ------------- --------------  
9.         100        Steven       King         17-6月 -03          24000  
10.         101    100 Neena       Kochhar       21-9月 -05          17000  
11.         108    101 Nancy       Greenberg     17-8月 -02          12008  
12.         109    108 Daniel      Faviet        16-8月 -02          9000  
13.         110    108 John        Chen          28-9月 -05          8200  
14.         111    108 Ismael      Sciarra       30-9月 -05          7700  
15.         112    108 Jose Manue Urman          07-3月 -06          7800  

6.2.4  层次化查询（2）

在语句中，START WITH以manager_id IS NULL作为根节点，从查询结果来看，King满足这个条件，在CONNECT BY子句中，紧随其后的是PRIOR操作符，其后面的列名表示是父项列的列名，而manager_id表示子项的manager_id的值，从查询结果可以看到，从根节点开始，King的employee_id值为100，因此将查询子行中manager_id为100的值，在示例中找到了Kochhar，它的employee_id为101，根据CONNECT BY PRIOR规则，继续向下寻找，形成树状的层次结构查询。

通过上面的示例可以看到，PRIOR后面的列代表父行的列，PRIOR可以位于比较操作符的任何一方，而不在乎是否是在CONNECT BY后面。例如将上面的语句的PRIOR更改为如下所示的语句，具有同样的效果：

1. SQL> SELECT   employee_id, manager_id, first_name, last_name, hire_date,  
2.                salary  
3.           FROM employees  
4.     --表示根节点为manger_id  
5.     START WITH manager_id IS NULL  
6.     --PRIOR表示父行的employee_id，等于当前行的manager_id  
7.     CONNECT BY manager_id = PRIOR employee_id;