Oracle tree calculates the product of leaf nodes to the root node

1. // Has the following binary tree and converts it into a table structure:
2. Parent_id child_id weight
3. -------------------
4. A B 2
5. B c 3
6. C d 4
7. B e 7
8. C f 2
9. // Calculate the product of the edge weight between the leaf node and the root node:
10. Leaf weight
11. ----------
12. D 24
13. E 14
14. F 12
15. // Data
16. Create table tree (parent_id varchar2 (10), child_id varchar2 (10), weight number (2 ));
17. Insert into tree values ('A','B', 2 );
18. Insert into tree values ('B','C', 3 );
19. Insert into tree values ('C','D', 4 );
20. Insert into tree values ('B','E', 7 );
21. Insert into tree values ('C','F', 2 );
22. // Create a function to calculate the string product (dynamic SQL)
23. Create or replace function func_tree (strInVarchar2)
24. ReturnNumber
25. As
26. Num number;
27. Begin
28. Execute immediate'Select'| Str |'From dual'Into num;
29. ReturnNum;
30. End func_tree;
31. // SQL code:
32. Select child_id, func_tree (substr (sys_connect_by_path (weight,'*'), 2) weight
33. From tree t
34. Where connect_by_isleaf = 1
35. Start with not exists (select 1 from tree where t. parent_id = child_id)
36. Connect by prior child_id = parent_id
37. Order by child_id;
38. // Result:
39. CHILD_ID WEIGHT
40. --------------------
41. D 24
42. E 14
43. F 12