Oracle 循环递归遍历树结构查询

在项目中经常会接触树结构的页面； 经常会写关于查询某个树节点的所有子节点的方法，若使用java的for循环或者递归不但效率低又好性能。所以推荐使用oracle 提供的connect by prior start with  的递归查询用法。

oracle的connect by prior start with  是个双向查询树结构的功能，既可以查询子节点 ，又可以根据子节点查询上流的所有父节点； 举例如下:

准备的SQL：

drop table t_tmp_20151027;
create table t_tmp_20151027(
       code varchar2(10) primary key,
       name varchar2(50),
       parent_code varchar2(10),
       create_time date default sysdate,
       active char(1) default 'Y'
);
comment on column t_tmp_20151027.code is '部门编码';
comment on column t_tmp_20151027.name is '部门名称';
comment on column t_tmp_20151027.parent_code is '父级编码';
comment on column t_tmp_20151027.create_time is '创建日期 默认sysdate';
comment on column t_tmp_20151027.active is '是否有效(Y有效,默认)';

select * from t_tmp_20151027;

insert into t_tmp_20151027(code, name, parent_code) values('D00001', '总裁办公室', null);
insert into t_tmp_20151027(code, name, parent_code) values('D01000', '企业发展办公室', 'D00001');
insert into t_tmp_20151027(code, name, parent_code) values('D10000', '职能运营本部', 'D01000');
insert into t_tmp_20151027(code, name, parent_code) values('D10001', '华东运行本部', 'D10000');
insert into t_tmp_20151027(code, name, parent_code) values('D10002', '华南运行本部', 'D10000');

insert into t_tmp_20151027(code, name, parent_code) values('D20000', '信息技术部', 'D01000');
insert into t_tmp_20151027(code, name, parent_code) values('D20001', 'IT开发部', 'D20000');
insert into t_tmp_20151027(code, name, parent_code) values('D20002', '运维管理部', 'D20000');

具体用法:

--通过父节点查询此节点及向下递归遍历所有的子节点...
select a.*
  from t_tmp_20151027 a, 
       (select distinct code
          from t_tmp_20151027
         where active = 'Y'
        connect by prior code  = parent_code
               and active = 'Y'
         start with code = 'D00001') b
 where 1 = 1
   and a.code = b.code
   and a.active = 'Y';
   
  
--通过子节点查询此节点及向上递归遍历所有的父节点  
select a.*
  from t_tmp_20151027 a, 
       (select distinct code
          from t_tmp_20151027
         where active = 'Y'
        connect by prior parent_code  = code
               and active = 'Y'
         start with code = 'D20000') b
 where 1 = 1
   and a.code = b.code
   and a.active = 'Y';