Oracle 递归案例

Oracle 递归案例

前提准备

-- 部门表
create table department (
 code VARCHAR2(10) not null,
 name VARCHAR2(50),
 pcode VARCHAR2(10),
 mgrname VARCHAR2(50)
);
-- 造数据
insert into department values('ROOT','顶级部门', null, '顶级BOSS');
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老大');
insert into department values('A021','A02部门', 'A02', 'A021老大');

递归查询某个部门(ROOT)的所有子级部门

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

递归查询某个部门(A)的所有子级部门

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

递归查询某个部门(B02)的所有上级部门

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

递归查询某个部门(A021)的所有上级部门

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