Oracle 树形结构存储

来源：互联网发布：相亲遇到的奇葩知乎编辑：程序博客网时间：2024/05/22 06:15

创建表

 create table article (  id number primary key,  cont varchar2(4000),  pid number,  isleaf number(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)insert into article values(2,'大象被打趴下',1,0,1);

创建存储过程

 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;

执行存储过程

 exec p(0,0);

执行结果

0 0