表格构:
id name level parent_id note
1 广州 1 0
2 天河区 2 1
3 黄埔区 2 1
4 东埔镇 3 2
所用的存储过程
CREATE OR REPLACE PACKAGE BODY TOOLS as
/*Inc:字符串自递增长度*/
Function IncString(in_String in varchar2,Inc_Count number default 1) return varchar2
as
result varchar2(4000);
v_count Integer;
begin
v_count := 1;
result := null;
loop
exit when v_count>=Inc_Count;
result := result||in_String;
v_count :=v_count+1;
end loop;
return result;
end;
/*构建一棵树*/
Function BuildTree(in_Level in Number) return varchar2
as
result varchar2(4000);
begin
if in_Level=1 then
return null;
end if;
return '|'||incString('-',(in_Level-1)*4);
end;
查询的构语句:
select a.addr_id ,tools.BuildTree(2)||a.addr_name from addr_info a
connect by a.addr_id = prior a.parent_id
start with a.addr_id='叶子结点id'
order by level desc
posted on 2006-04-03 15:32
有猫相伴的日子 阅读(389)
评论(1) 编辑 收藏 所属分类:
pl/sql