oracle 树状输出,Oracle中的树状展示与储存

[size=medium]树状结构的存储与展示:

drop table article;

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);

cmmmit;

展示树状展示:

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;

显示出错的信息:show error;

set serveroutput on;

调用存储:exec p(0,0);

SQL> exec p(0,0);

蚂蚁大战大象

----大象被打趴下了

--------蚂蚁也不好过

--------瞎说

------------没有瞎说

--------大象进医院了

------------护士是蚂蚁

----怎么可能

--------怎么没有可能

--------可能性很大

PL/SQL 过程已成功完成。