递归查询

关于递归查询
Oracle递归查询的主要命令为：
select -> from -> where -> start with -> connect by
-> order by
举例说明：（树形结构）
1.建立如下数据库
drop table t_dept;
create table t_dept (
dept_id number(2) not null primary key,
parent_id number(2) ,
dept_name varchar2(10),
amount number(3) );
alter table t_dept add foreign key (parent_id) references
t_dept ( dept_id);
2.插入数据
delete t_dept;
insert into t_dept (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (1,null,'1' ,2);
insert into t_dept (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (2,1 ,'1-2' ,15);
insert into t_dept (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (3,1 ,'1-3' ,8);
insert into t_dept (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (4,2 ,'1-2-4',10);
insert into t_dept (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (5,2 ,'1-2-5',9);
insert into t_dept (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (6,3 ,'1-3-6',17);
insert into t_dept (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (7,3 ,'1-3-7',5);
insert into t_dept (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (8,3 ,'1-3-8',6);
commit;
3.查询（节点2于所有他的下级节点信息）
select a.dept_id,a.dept_name
from t_dept a
where dept_id in(select dept_id from t_dept start with dept_id=2 connect by parent_id=prior dept_id);
等价
select dept_id,dept_name
from t_dept
start with dept_id=2
connect by parent_id=prior dept_id;
4.查询(-- 不包括部门3及其下属部门(部门3和6、7、8都没出现)
SELECT PARENT_ID,DEPT_NAME,AMOUNT
FROM T_DEPT
START WITH DEPT_ID = 1
CONNECT BY PARENT_ID = PRIOR DEPT_ID
AND DEPT_ID <> 3
5.查询(部门1及其所有下级部门，但是仅不包括部门3（排除节点）)
SELECT PARENT_ID,DEPT_NAME,AMOUNT
FROM T_DEPT
where DEPT_ID <>3
START WITH DEPT_ID = 1
CONNECT BY PARENT_ID = PRIOR DEPT_ID
6.查询（部门1及其所有下级部门，且所有部门按照人数升序排列）
SELECT PARENT_ID,DEPT_NAME,AMOUNT
FROM T_DEPT
START WITH DEPT_ID = 1
CONNECT BY PARENT_ID = PRIOR DEPT_ID
ORDER BY AMOUNT ASC
7.查询（部门1及其所有下级部门，每个部门的下一级部门之间，按照人数升序排列(有同一上级的那些部门)）
SELECT PARENT_ID,DEPT_NAME,AMOUNT
FROM T_DEPT
START WITH DEPT_ID = 1
CONNECT BY PARENT_ID = PRIOR DEPT_ID
ORDER SIBLINGS BY AMOUNT ASC -- 同属部门间排序

8.要点总结
A、子句的语法书写顺序。
select -> from -> where -> start with -> connect by -> order by
where写在connect by后面就不行，报错。

B、子句的执行顺序
from -> start with -> connect by -> where -> select -> order by
执行顺序where在connect by之后，可以从前例5证明。
可是书写SQL语句的时候，却只能写前面，注意理解。

C、如何理解“CONNECT BY PRIOR PARENT_ID = DEPT_ID ”的含义呢？
（1） 前提是要理解SQL语句执行时，是一条一条记录来处理的。
（2） 每条满足START WITH语句条件的记录被依次取出，暂且把每次被取出处理的记录，称为当前记录。
（3） “PRIOR PARENT_ID”表明从当前记录得到PARENT_ID，然后
“ = DEPT_ID”说明找到表中所有DEPT_ID等于当前记录PARENT_ID的记
录，也就是找当前记录PARENT_ID所指向的记录。
因为PARENT_ID的取值含义是上级节点，所以说明是向树的根节点方向
的搜索。（我的上级是谁？）
（4） 反之，如果是“CONNECT BY PARENT_ID = PRIOR DEPT_ID”，“PRIOR”
在DEPT_ID一边，就是找所有PARENT_ID等于当前记录DEPT_ID的记录，
是向树的叶子方向的搜索。（我的下级是谁？）
找到结果记录集以后，从第一条记录开始递归处理，依此类推。