表格構(gòu):

? id???????? name? ? level ? parent_id??? note
?1??????? 廣州????? 1?????????? 0??????????????
?2??????? 天河區(qū)? 2???????? 1??
?3??????? 黃埔區(qū) 2???????? 1
?4??????? 東埔鎮(zhèn)? 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;

/*構(gòu)建一棵樹*/
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;


查詢的構(gòu)語句:

?? 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='葉子結(jié)點id'
??? order by level desc