第一种
第三种:
加一个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;