Oracle层次查询研究

Oracle 层次查询研究[@more@]

1、环境

SQL> select * from v$version

2 /

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

PL/SQL Release 10.2.0.3.0 - Production

CORE 10.2.0.3.0 Production

TNS for HPUX: Version 10.2.0.3.0 - Production

NLSRTL Version 10.2.0.3.0 - Production

2、基础知识

A、树形结构

上图是一个虚拟的公司树形结构。层次结构与树形结构相同，如计算

机的目录管理就是层次结构。

根据上图建一张department表用于存储图1中的数据：

CREATE TABLE DEPARTMENT

( DEPTNO NUMBER,

DEPTNAME VARCHAR2(100),

PDEPTNO NUMBER

)

SQL> desc department

名称 是否为空? 类型

----------------------------------------- -------- ----------------------------

DEPTNO NUMBER

DEPTNAME VARCHAR2(100)

PDEPTNO NUMBER

SQL> insert into department

2 values(1, '公司',null);

SQL> insert into department

2 values(2, '技术部',1);

SQL> insert into department

2 values(3, '运维部',1);

SQL> insert into department

2 values(4, '财务部',1);

SQL> insert into department

2 values(5, '研发中心',2);

SQL> insert into department

2 values(6, '开发1部',2);

SQL> insert into department

2 values(7, '数据中心',3);

SQL> insert into department

2 values(8, 'oracle运维',7);

SQL> insert into department

2 values(9, 'db 2运维',7);

SQL> select count(*) from department;

COUNT(*)

----------

9

SQL> commit;

提交完成。

SQL> select * from department;

DEPTNO DEPTNAME PDEPTNO

---------- -------------------- ----------

1 公司

2 技术部 1

3 运维部 1

4 财务部 1

5 研发中心 2

6 开发1部 2

7 数据中心 3

8 oracle运维 7

9 db 2运维 7

已选择9行。

B、Oracle语法

start with标识层次中根所在的行，可以有多行。

connect by 标识层次中父节点行与子节点行的关系。

查询步骤：

（1）根据start with找到层次中的根节点行，可以有多行，其可以使用子查询；

（2）根据connect by找到根节点的子节点行，如果connect by中含有循环，则Oracle报错；

（3）将（2）中的子节点行作为根节点行，继续找其子节点行；

（4）where语句用于剔除结果集中不满足条件的行，但不会剔除其子节点行；

（5）查询结果以特定秩序显示，以保证层次结构不乱。

限制条件：

（1）不能有表的JOIN，也不能从有JOIN的视图中获取数据；

（2）ORDER BY子句会破坏返回结果的层次顺序。如果要保持层次的秩序，用ORDER SIBLINGS BY；

（3）connect by不能包含子查询，其condition中必须含有prior关键词。

3、查询的使用

以下查询显示所有子节点：

SQL> select * from department

2 start with deptno = 1

3 connect by prior deptno = pdeptno

4 /

DEPTNO DEPTNAME PDEPTNO

---------- -------------------- ----------

1 公司

2 技术部 1

5 研发中心 2

6 开发1部 2

3 运维部 1

7 数据中心 3

8 oracle运维 7

9 db 2运维 7

4 财务部 1

已选择9行。

以下查询显示技术部及其子节点：

SQL> select * from department

2 start with deptno = 2

3 connect by prior deptno = pdeptno

4 /

DEPTNO DEPTNAME PDEPTNO

---------- -------------------- ----------

2 技术部 1

5 研发中心 2

6 开发1部 2

以下查询显示where语句的作用：

SQL> l

1 select * from department

2 where deptno != 2

3 start with deptno = 1

4* connect by prior deptno = pdeptno

SQL> /

DEPTNO DEPTNAME PDEPTNO

---------- -------------------- ----------

1 公司

5 研发中心 2

6 开发1部 2

3 运维部 1

7 数据中心 3

8 oracle运维 7

9 db 2运维 7

4 财务部 1

如上结果所示，where语句只剔除结果集中不符合条件的节点，对于其子节点，并不剔除。

以下查询显示相同的条件放在connect by子句中的作用：

SQL> l

1 select * from department

2 start with deptno = 1

3* connect by prior deptno = pdeptno and deptno != 2

SQL> /

DEPTNO DEPTNAME PDEPTNO

---------- -------------------- ----------

1 公司

3 运维部 1

7 数据中心 3

8 oracle运维 7

9 db 2运维 7

4 财务部 1

以上查询裁剪掉了deptno为2的整个分枝，感兴趣的朋友可以自行比较相同条件放在where子句和connect by子句中的区别，其实connect by中的限制条件只针对子节点进行过滤。

level伪列的使用：

以下查询使用level使层次关系非常清楚：

SQL> edit

已写入 file afiedt.buf

1 select level , lpad(' ', 2*(level -1)) || deptname as deptname

2 from department

3 start with deptno = 2

4* connect by prior deptno = pdeptno

SQL> /

LEVEL DEPTNAME

---------- --------------------

1 技术部

2 研发中心

2 开发1部

如果在connect by子句中使用level < 2，则查询返回2层以内的子节点。

prior的位置：以上查询是将prior放在deptno前，是通过指定节点查找其子节点。如果将prior放在pdeptno之前，则通过指定节点查找其父节点。感兴趣的朋友可以自己尝试。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9523925/viewspace-1032137/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9523925/viewspace-1032137/