oracle中的递归查询语句

　　创建一个demo表 department：

create table department (
 code VARCHAR2(10) not null,
 name VARCHAR2(50),
 pcode VARCHAR2(10),
 mgrname VARCHAR2(50)
);
insert into department values('ROOT','顶级部门', null, 'R大大');
insert into department values('A','A部门', 'ROOT', 'A老大');
insert into department values('A01','A01部门', 'A', 'A01老大');
insert into department values('A02','A02部门', 'A', 'A02老大');
insert into department values('B','B部门', 'ROOT', 'B老大');
insert into department values('B01','B01部门', 'B', 'B01老大');
insert into department values('B02','B02部门', 'B', 'B02老大');

1.递归查询某个部门的所有子级部门：　

select t.* from department t start with t.code='ROOT' connect by prior t.code = t.pcode ;

　　查询结果：

　　

 

 2.递归查询某个部门的所有上级部门：

select t.* from department t start with t.code='B02' connect by t.code = prior t.pcode ;

　　查询结果：