Oracle tree計算葉子節(jié)點到根節(jié)點的乘積

//有下面一棵二叉樹，轉(zhuǎn)換為表結(jié)構(gòu):   
parent_id  child_id  weight   
------     -------   ------   
a          b              2    
b          c              3   
c          d              4   
b          e              7   
c          f              2   
//計算葉子節(jié)點到根節(jié)點之間邊的權(quán)值的乘積:   
leaf weight   
---- ------   
d        24   
e        14   
f        12   
//數(shù)據(jù)   
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);   
//創(chuàng)建一個函數(shù)實現(xiàn)求字串乘積(動態(tài)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;   
//結(jié)果:   
CHILD_ID       WEIGHT   
---------- ----------   
d                  24   
e                  14   
f                  12 

服務(wù)器租用/服務(wù)器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]