--数据: create table test5 (pid varchar2(10),cid varchar2(10),qty number); insert into test5 values('a','b',2); insert into test5 values('b','c',3); insert into test5 values('c','d',4); insert into test5 values('b','e',7); insert into test5 values('c','f',2);--先创建一个函数实现求字串乘积(动态SQL) 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; /--sql: select cid, func_test2(substr(sys_connect_by_path(qty, '*'), 2)) qty from test5 t where connect_by_isleaf = 1 start with pid='a' connect by prior cid = pid order by cid; --result: CID QTY --------- d 24 e 14 f 12
--上面起始位置写的有点死,改一下 --start with not exists (select 1 from test5 where t.pid=cid) --最终如下:select cid, func_test2(substr(sys_connect_by_path(qty, '*'), 2)) qty from test5 t where connect_by_isleaf = 1 start with not exists (select 1 from test5 where t.pid=cid) connect by prior cid = pid order by cid;
create table test5 (pid varchar2(10),cid varchar2(10),qty number);
insert into test5 values('a','b',2);
insert into test5 values('b','c',3);
insert into test5 values('c','d',4);
insert into test5 values('b','e',7);
insert into test5 values('c','f',2);--先创建一个函数实现求字串乘积(动态SQL)
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;
/--sql:
select cid, func_test2(substr(sys_connect_by_path(qty, '*'), 2)) qty
from test5 t
where connect_by_isleaf = 1
start with pid='a'
connect by prior cid = pid
order by cid;
--result:
CID QTY
---------
d 24
e 14
f 12
http://blog.csdn.net/zhuomingwang/archive/2011/02/19/6194556.aspx
希望这个对你有帮助
--上面起始位置写的有点死,改一下
--start with not exists (select 1 from test5 where t.pid=cid)
--最终如下:select cid, func_test2(substr(sys_connect_by_path(qty, '*'), 2)) qty
from test5 t
where connect_by_isleaf = 1
start with not exists (select 1 from test5 where t.pid=cid)
connect by prior cid = pid
order by cid;
SYS_CONNECT_BY_PATH函数