??? 对常见几种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) := '';
be