需求：PL/SQL递归求解，如何实现？

gelyon 2010-03-04 12:24:30

表结构及数据:
SQL> select * from temp;

parent child quty
------ -------- ----
a b 3
a c 1
b d 4
b e 1
b f 4
e h 2

........
........

解释：1个a由3个b和1个c构成，一个b又由4个d、一个e、4个f构成。。。。。

现在的需求：

(1)输入一个父节点，查询出此父节点最终的子节点及数量。

举例：

如输入a,输出
d:12
h:6
f:12
h:1

即：

递归过程结果：

|-> 4d d: 1*3*4=12
|-> 3b |-> 1e -> 2h h: 1*3*1*2=6
| |-> 4f f: 1*3*4=12
1a |
|-> 1c c: 1*1=1

需求(2):

输入不止一个，如输入a、b,最后输出是 a和b 所有最终子节点和（即如果a、b中最终子节点有相同的，则最终相加求和）。

举例:

输入a、b,输出:
d:16
h:8
f:16
c:1

如何实现用PL/SQL实现此需求呢？？

...全文

493 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

小灰狼W 2010-03-04

打赏
举报

--(1)

with temp as(

  select 'a' parent,'b' child,3 quty from dual

  union all select 'a','c',1 from dual

  union all select 'b','d',4 from dual

  union all select 'b','e',1 from dual

  union all select 'b','f',4 from dual

  union all select 'e','h',2 from dual)



select child, func_test2(substr(sys_connect_by_path(quty, '*'), 2)) num

  from temp t

 where connect_by_isleaf = 1

 start with parent = 'a'

connect by prior child = parent;



--(2)

with temp as(

  select 'a' parent,'b' child,3 quty from dual

  union all select 'a','c',1 from dual

  union all select 'b','d',4 from dual

  union all select 'b','e',1 from dual

  union all select 'b','f',4 from dual

  union all select 'e','h',2 from dual)

  

select child,

       sum(func_test2(substr(sys_connect_by_path(quty, '*'), 2))) num

  from temp t

 where connect_by_isleaf = 1

 start with parent in ('a', 'b')

connect by prior child = parent

 group by child;

小灰狼W 2010-03-04

打赏
举报

自定义函数如下，其实很简单

create or replace function func_test2(str in varchar2)

return number

as

num number;

begin

execute immediate 'select '||str||' from dual' into num;

return num;

end;

利用该函数来解
（1）

with temp as(

  select 'a' parent,'b' child,3 quty from dual

  union all select 'a','c',1 from dual

  union all select 'b','d',4 from dual

  union all select 'b','e',1 from dual

  union all select 'b','f',4 from dual

  union all select 'e','h',2 from dual)

--以上为测试数据

select child,

  func_test2(substr(sys_connect_by_path(quty,'*'),2))num

from temp t

where connect_by_isleaf=1

start with not exists(

  select 1 from temp where child=t.parent)

connect by prior child=parent

小灰狼W 2010-03-04

打赏
举报

oracle自带函数不能实现连乘
不过既然要使用pl/sql来解，可以建个函数处理

zhangwonderful 2010-03-04

打赏
举报

引用 3 楼 wildwave 的回复:

SQL code--(1)withtempas(select'a' parent,'b' child,3 qutyfrom dualunionallselect'a','c',1from dualunionallselect'b','d',4from dualunionallselect'b','e',1from dualunionallselect'b','f',4from dualunionallselect'e','h',2from dual)select child, func_test2(substr(sys_connect_by_path(quty,'*'),2)) numfromtemp twhere connect_by_isleaf=1
startwith parent='a'
connectby prior child= parent;--(2)withtempas(select'a' parent,'b' child,3 qutyfrom dualunionallselect'a','c',1from dualunionallselect'b','d',4from dualunionallselect'b','e',1from dualunionallselect'b','f',4from dualunionallselect'e','h',2from dual)select child,sum(func_test2(substr(sys_connect_by_path(quty,'*'),2))) numfromtemp twhere connect_by_isleaf=1
startwith parentin ('a','b')
connectby prior child= parentgroupby child;

强，学习了

gelyon 2010-03-04