对常见几种tree的表的设计
第一种
create table article
(
id number primary key,
cont varchar2(4000),
pid number
);
加一个pid关联id,也是最常用(递归)方式之一
insert into article values (1, '地区', 0);
insert into article values (2, '北京', 1);
insert into article values (3, '海淀区', 2);
insert into article values (4, '东城区', 2);
insert into article values (5, '王府井', 4);
insert into article values (6, '上海', 1);
insert into article values (7, '徐汇区', 6);
insert into article values (8, '美罗城', 7);
insert into article values (9, '普陀区', 6);
insert into article values (10, '中山北路', 9);
create or replace procedure p (v_id article.id%type, v_grade binary_integer)
is
cursor c is select * from article where pid = v_id;
v_preStr varchar2(1024);
begin
for v_i in 1..v_grade loop
v_preStr := v_preStr || '----';
end loop;
for v_a in c loop
dbms_output.put_line(v_preStr || v_a.cont);
p (v_a.id, v_grade + 1);
end loop;
end;
begin
p(0, 0);
end;
输出
地区
----北京
--------海淀区
--------东城区
------------王府井
----上海
--------徐汇区
------------美罗城
--------普陀区
------------中山北路
第二种:
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, 0, 2);
insert into article values (8, '美罗城', 7, 1, 2);
insert into article values (9, '普陀区', 6, 0, 2);
insert into article values (10, '中山北路', 9, 1, 3);
commit;
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;
begin
p(0, 0);
end;
地区
****北京
********海淀区
********东城区
************王府井
****上海
********徐汇区
************美罗城
********普陀区
************中山北路
第三种:
drop table article;
create table article
(
id number primary key,
cont varchar2(4000),
str char(8), --number(8) --str代表层次
grade number(1)
);
insert into article values (1, '地区', '01000000', 1);
insert into article values (2, '北京', '01010000', 2);
insert into article values (3, '海淀区', '01010100', 3);
insert into article values (4, '东城区', '01010200', 3);
insert into article values (5, '王府井', '01010201', 4);
insert into article values (6, '上海', '01020000', 2);
insert into article values (7, '徐汇区', '01020100', 3);
insert into article values (8, '美罗城', '01020101', 4);
insert into article values (9, '普陀区', '01020200', 3);
insert into article values (10, '中山北路', '01020201', 4);
commit;
select * from article order by str;
declare
cursor c is select * from article order by str;
v_preStr varchar2(1024) := '';
begin
for v_temp in c loop
v_preStr := '';
for i in 1..v_temp.grade loop
v_preStr := v_preStr || '****';
end loop;
dbms_output.put_line(v_preStr || v_temp.cont);
end loop;
end;
****地区
********北京
************海淀区
************东城区
****************王府井
********上海
************徐汇区
****************美罗城
************普陀区
****************中山北路