ORACLE中的层次结构查询

ORACLE中的层次结构查询

层次结构在现实生活中是很常见的，因此早期的数据库是层次数据库，如实的反映了这种关系。后来层次数据库被证实是非常笨重的，因此关系数据库取得了成功。但是层次结构依然存在，而在关系数据库中层次并不能得到很好的体现。ORACLE提供了强大的层次结构查询来解决这个问题。

一、 固定和可变层数

层次结构关系大致可分为两种。

一种是层级固定，例如美国分为各个州，州下分县，县下又分镇区和城市。这种情况如果在一张表中用递归来实现并不是很好的选择，将其保存在主从结构的三张表中更容易查询和操作。

另一种是层级可变。如图所示的组件清单：

  可构造层次表如下：

二、 ORACLE的层次查询

ORACLE提供了SELECT查询语句的START WITH和CONNECT BY子句，来实现层次查询。要进行层次查询，必须明了两件事情：识别根记录的条件和子记录中指向其父亲的列名。

看我们的例子，parent_assembly列决定该记录的父亲。由于根记录Airplane没有父亲，因此列parent_assembly为空，而其他记录的该列不为空，凭此可识别根记录。两个条件均已满足，构造查询语句如下：

SELECT assembly_id, assembly_name, parent_assembly

FROM bill_of_materials

START WITH parent_assembly IS NULL

CONNECT BY parent_assembly = PRIOR assembly_id;

三、 解析CONNECT BY子句

从一定角度看，CONNECT BY可以看作是一种连接的规范。实现递归连接，也可以使用标准sql语句，将BILL_OF_MATERIAL与自身连接起来并返回每行的父记录。

SELECT bom1.assembly_id, bom1.assembly_name, bom2.assembly_id parent

FROM bill_of_materials bom1 LEFT OUTER JOIN bill_of_materials bom2

ON bom1.parent_assembly = bom2.assembly_id;

这个sql语句返回的记录和上面那个语句是同样的。唯一的区别是行的顺序不一样。当然行的顺序是非常重要的，自连接的sql语句返回记录的顺序不能反映父子关系。

很多问题并不能通过自连接解决，例如你不能使用自连接查询选出刚好组成一架飞机的组件。但是CONNECT BY可以解决所有父子结构方面的问题。

四、 如何使用WHERE子句

要过滤记录，有两种途径。首先，从查询的根节点方面着手，使用START WITH子句来限制查询特定节点。例如我们可以使用如下语句，只查询飞机上两个座位及其部件的信息：

SELECT assembly_id, assembly_name, PRIOR assembly_id parent

FROM bill_of_materials

START WITH assembly_id IN (205,206)

CONNECT BY parent_assembly = PRIOR assembly_id;

但是有时需要其他条件，比如part_number is not null，这时就需要引入WHERE子句。

SELECT assembly_id, assembly_name, PRIOR assembly_id parent

FROM bill_of_materials

WHERE PART_NUMBER IS NOT NULL

START WITH assembly_id IN (205,206)

CONNECT BY parent_assembly = PRIOR assembly_id;

上面语句中的过滤条件是在CONNECT BY语句处理完成后才发生作用。当树已经生成，过滤条件才开始影响查询所返回的记录。

五、 层次连接

要写好层次查询，必须了解ORACLE如何处理这样的查询。

Ø 首先实现连接

Ø 其次CONNECT BY处理应用在连接所返回的记录行上。

Ø 再次WHERE子句定义的过滤条件应用在CONNECT BY操作返回的记录行上。

知道了上述顺序，让我们来看一个查询：

SELECT assembly_id id, parent_assembly parent, assembly_name name,

bom.part_number part, current_inventory inventory

FROM bill_of_materials bom, part p

WHERE bom.part_number = p.part_number

START WITH assembly_id = 200

CONNECT BY parent_assembly = PRIOR assembly_id;

上面的查询中，首先处理的是连接——bom.part_number = p.part_number。

而在bill_of_materials表中只有最低层次的节点（叶节点）其part_number列才不为空。两者连接后，将bill_of_materials表中part_number为空的列剔除了，而那些列又是叶节点的父级。所以下一步进行CONNECT BY操作时，由于找不到查询的根节点——assembly_id = 200，查询不会返回任何记录。注意：没有根就意味着没有输出。

解决办法是使用外连接(+)，这样CONNECT BY操作才会成功。

SELECT assembly_id id, parent_assembly parent, assembly_name name,

bom.part_number part, current_inventory inventory

FROM bill_of_materials bom, part p

WHERE bom.part_number = p.part_number(+)

START WITH assembly_id = 200

CONNECT BY parent_assembly = PRIOR assembly_id;

WHERE子句里的连接条件是首先被处理的，而WHERE子句里的过滤条件是在CONNECT BY处理完成后才起作用的。

如下述查询选出备件所有存货量小于500的记录：

SELECT assembly_id id, parent_assembly parent, assembly_name name,

bom.part_number part, current_inventory inventory

FROM bill_of_materials bom, part p

WHERE bom.part_number = p.part_number (+)

AND p.current_inventory < 500

START WITH assembly_id = 200

CONNECT BY parent_assembly = PRIOR assembly_id;

AND p.current_inventory < 500属于过滤条件，最后处理。

你也许注意到上面的查询都是用的老版本的连接，很不幸的，Oracle 9i数据库在CONNNECT BY查询和新的JOIN子句之间存在bug。上述语句用新JOIN子句写出：

SELECT assembly_id id, parent_assembly parent, assembly_name name,

bom.part_number part, current_inventory inventory

FROM bill_of_materials bom LEFT OUTER JOIN part p

ON bom.part_number = p.part_number

WHERE p.current_inventory < 500

START WITH assembly_id = 200

CONNECT BY parent_assembly = PRIOR assembly_id;

这个语句将不会返回任何值，因为根节点被过早的过滤掉了。

当然ORACLE已经清楚这个bug，正在修复。

六、 层级

我们经常希望得知树中给定记录的层级，oracle提供LEVEL伪列来实现。LEVEL可出现在SQL语句的任何部分，并且对于根节点返回1，根节点的孩子返回2，孙子返回3…。

通常使用LEVEL来实现记录的缩排，以便更清楚的表现树状结构。

七、 节点路径

Oracle 9i提供了新函数SYS_CONNECT_BY_PATH，可以给出从根节点到指定节点的全路径。该函数有两个参数：列名和分隔符，而且列名并不局限于CONNECT BY列。

例如：

SELECT SYS_CONNECT_BY_PATH(assembly_name,'/') name_path

FROM bill_of_materials

WHERE part_number = 1019

START WITH parent_assembly IS NULL

CONNECT BY parent_assembly = PRIOR assembly_id;

八、 排序

想对结果进行排序，当然可以使用标准的ORDER BY子句，但是它会破坏父子结构，一般不建议使用。

Oracle 9i也提供了一个ORDER BY子句的关键字SIBLINGS，允许对层次数据以有意义的方式进行分类，同时保留层次结构。

如：

SELECT RPAD(' ', 2*(LEVEL-1)) || assembly_name assembly_name

FROM bill_of_materials

START WITH assembly_id = 200

CONNECT BY parent_assembly = PRIOR assembly_id

ORDER SIBLINGS BY assembly_name;

表示在每个层级（LEVEL）上按字母表顺序进行排序，这对生成树状报表非常有用。