oracle pl/sql递归查询

96vid9a5ki

--测试用表
create table xt(
xtid int ,--id
pid int,--父id
xname varchar(30)
)
--插入几条测试数据
insert into xt values(0,-1,'000');
insert into xt values(1,0,'111');
insert into xt values(2,0,'232');
insert into xt values(3,0,'333');
insert into xt values(4,0,'444');
insert into xt values(5,2,'555');
insert into xt values(6,2,'666');
insert into xt values(7,5,'777');
--创建递归查询存储过程
create or replace procedure get_son_(beginNo in Int,suffix in varchar2)
is
v_suffix varchar2(50) := suffix;--缩进
begin
--游标记录当前传递id的直接子
for v_record in (select * from xt where xt.pid=beginNo) loop
dbms_output.put_line(v_suffix||v_record.pid||'-'||v_record.xtid||'-'||v_record.xname);
get_son_(v_record.xtid,v_suffix||'    ');--递归调用
end loop;
end;
--测试
begin
get_son_(0,'');--递归查询0的所有子
dbms_output.put_line('');
get_son_(2,'');--递归查询2的所有子
end;
/**
输出结果
0-1-111
0-2-232
2-5-555
5-7-777
2-6-666
0-3-333
0-4-444
2-5-555
5-7-777
2-6-666
*/