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

来源：互联网发布：unity3d开发的小游戏编辑：程序博客网时间：2024/05/04 20:37

在项目中经常会接触树结构的页面；经常会写关于查询某个树节点的所有子节点的方法，若使用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';

0 0