表格構:
? 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
有貓相伴的日子 閱讀(418)
評論(1) 編輯 收藏 所屬分類:
pl/sql