本篇文章详细介绍了Oracle的递归查询语法，利用此语法，可以方便地实现递归的双向查询：

?

一、测试准备
1、先假设有如下部门结构。
?????? 1
???? / \
??? 2??? 3
?? /\??? /|\
4 5 6 7 8

?

2、然后建立测试表和数据。
drop table t_dept_temp;
create table t_dept_temp(
DEPT_ID??? NUMBER(2)??? NOT NULL,
PARENT_ID NUMBER(2)??? ,
DEPT_NAME VARCHAR2(10) ,
AMOUNT???? NUMBER(3)?????????? --人数
);

delete t_dept_temp;
insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (1,null,'1'??? ,2);
insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (2,1?? ,'1-2' ,15);
insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (3,1?? ,'1-3' ,8);
insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (4,2?? ,'1-2-4',10);
insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (5,2?? ,'1-2-5',9);
insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (6,3?? ,'1-3-6',17);
insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (7,3?? ,'1-3-7',5);
insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (8,3?? ,'1-3-8',6);
insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (9,4?? ,'1-2-4-9',3);
commit;

?

SQL> select * from t_dept_temp;

?

DEPT_ID PARENT_ID DEPT_NAME AMOUNT
------- --------- ---------- ------
????? 1?????????? 1?????????????? 2
????? 2???????? 1 1-2??????????? 15
????? 3???????? 1 1-3???????????? 8
????? 4???????? 2 1-2-4????????? 10
????? 5???????? 2 1-2-5?????????? 9
????? 6???????? 3 1-3-6????????? 17
????? 7???????? 3 1-3-7?????????? 5
????? 8???????? 3 1-3-8?????????? 6

?

3、调整一下输出格式
col DEPT_ID format A10;

?

二、接下来实现各种查询要求
1、部门2及其所有下级部门。
SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,
?????? PARENT_ID,DEPT_NAME,AMOUNT
FROM t_dept_temp
CONNECT BY PARENT_ID = PRIOR DEPT_ID -- 找出所有PARENT_ID等于当前记录DEPT_ID的记录。
START WITH DEPT_ID = 2??????????????? -- 从部门2开始递归查询。
;
DEPT_ID??? PARENT_ID DEPT_NAME AMOUNT
---------- --------- ---------- ------
2????????????????? 1 1-2??????????? 15
4??????????????? 2 1-2-4????????? 10
5??????????????? 2 1-2-5?????????? 9

?

?

2、部门4及其所有上级部门
SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,
?????? PARENT_ID,DEPT_NAME,AMOUNT
FROM T_DEPT_TEMP
CONNECT BY PRIOR PARENT_ID = DEPT_ID -- 找出所有DEPT_ID等于当前记录PARENT_ID的记录
START WITH DEPT_ID = 4?????????????? -- 从部门4开始递归查询。
;
DEPT_ID??? PARENT_ID DEPT_NAME AMOUNT
---------- --------- ---------- ------
4????????????????? 2 1-2-4????????? 10
2??????????????? 1 1-2??????????? 15
??? 1??????????????? 1?????????????? 2

?

?

3、部门1的所有下级部门。
SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,
?????? PARENT_ID,DEPT_NAME,AMOUNT
FROM T_DEPT_TEMP
START WITH DEPT_ID = 1
CONNECT BY PARENT_ID = PRIOR DEPT_ID;
DEPT_ID??? PARENT_ID DEPT_NAME AMOUNT
---------- --------- ---------- ------
1??????????????????? 1?????????????? 2
2??????????????? 1 1-2??????????? 15
??? 4????????????? 2 1-2-4????????? 10
??? 5????????????? 2 1-2-5?????????? 9
3??????????????? 1 1-3???????????? 8
??? 6????????????? 3 1-3-6????????? 17
??? 7????????????? 3 1-3-7?????????? 5
??? 8????????????? 3 1-3-8?????????? 6

?

?

4、部门1及其所有下级部门，但是不包括部门3及其下级部门。（排除树枝）
SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,
?????? PARENT_ID,DEPT_NAME,AMOUNT
FROM T_DEPT_TEMP
START WITH DEPT_ID = 1
CONNECT BY PARENT_ID = PRIOR DEPT_ID
???????? AND DEPT_ID <> 3??? -- 不包括部门3及其下属部门(部门3和6、7、8都没出现)
;
DEPT_ID??? PARENT_ID DEPT_NAME AMOUNT
---------- --------- ---------- ------
1??????????????????? 1?????????????? 2
2??????????????? 1 1-2??????????? 15
??? 4????????????? 2 1-2-4????????? 10
??? 5????????????? 2 1-2-5?????????? 9