oracle存储过程示例

create table article
(
id number primary key,
cont varchar2(4000),
pid number,
isleaf number(1),--0代表非叶子节点，1代表叶子节点
alevel number(2)
);

insert into article values(1,'蚂蚁大战大象',0,0,0);
insert into article values(2,'大象被打趴下了',1,0,1);
insert into article values(3,'蚂蚁也不好过',2,1,2);
insert into article values(4,'瞎说',2,0,2);
insert into article values(5,'没有瞎说',4,1,3);
insert into article values(6,'怎么可能',1,0,1);
insert into article values(7,'怎么没有可能',6,1,2);
insert into article values(8,'可能性事很大的',6,1,2);
insert into article values(9,'大象进医院了',2,0,2);
insert into article values(10,'护士是蚂蚁',9,1,3);

蚂蚁大战大象
    大象被打趴下了
        蚂蚁也不好过
        瞎说
            没有瞎说
 大象进医院了
             护士是蚂蚁
    怎么可能
        怎么没有可能
        可能性很大的
       

create or replace procedure p(v_pid article.pid%type, v_level binary_integer) is
  cursor c is select * from article where pid = v_pid;
  v_preStr varchar2(1024) :='';
begin
  for i in 1..v_level loop
    v_preStr := v_preStr || '****';
  end loop; 
  
  for v_article in c loop
    dbms_output.put_line(v_preStr || v_article.cont);
    if(v_article.isleaf = 0) then
      p(v_article.id,v_level + 1);
    end if;
  end loop;
end;