oracle pl/sql递归查询

--测试用表
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
*/