Oracle tree計算葉子節點到根節點的乘積

發布時間: 2012/9/12 17:28:28

//有下面一棵二叉樹，轉換為表結構:
parent_id child_id weight
------     -------   ------
a          b              2
b          c              3
c          d              4
b          e              7
c          f              2
//計算葉子節點到根節點之間邊的權值的乘積:
leaf weight
---- ------
d        24
e        14
f        12
//數據
create table tree (parent_id varchar2(10),child_id varchar2(10),weight number(2));
insert into tree values('a','b',2);
insert into tree values('b','c',3);
insert into tree values('c','d',4);
insert into tree values('b','e',7);
insert into tree values('c','f',2);
//創建一個函數實現求字串乘積(動態SQL)
create or replace function func_tree(str in varchar2)
return number
as
num number;
begin
     execute immediate 'select '||str||' from dual' into num;
     return num;
end func_tree;
//sql代碼:
select child_id, func_tree(substr(sys_connect_by_path(weight, '*'), 2)) weight
from tree t
where connect_by_isleaf = 1
start with not exists (select 1 from tree where t.parent_id=child_id)
connect by prior child_id = parent_id
order by child_id;
//結果:
CHILD_ID       WEIGHT
---------- ----------
d                  24
e                  14
f                  12