我在数据库里面创建了一个函数:
CREATE OR REPLACE FUNCTION "LIN_PPFUN"
return lin_sr_pp_table
as
PP lin_sr_pp_table;
begin
select
lin_sr_pp(
bd_prodline.prodlinename ,
sum(round(ha_ysd.jfshl, 4)) ,
sum(round(ha_ysd.jfshl* bd_invbasdoc.def2, 4)),
sum(round(ha_ysd.jfshl* bd_invbasdoc.def3, 4)) ,
sum(ha_ysd.jfybje) ,
sum(ha_ysd.jfybwsje) ,
sum(ha_ysd.jfybsj) bulk collect into PP
from ha_ysd
left join bd_invbasdoc on ha_ysd.cinventoryid = bd_invbasdoc.pk_invbasdoc
left join bd_measdoc on (bd_invbasdoc.pk_measdoc = bd_measdoc.pk_measdoc)
left outer join bd_prodline on (bd_invbasdoc.pk_prodline =
bd_prodline.pk_prodline)
retrun pp;
end
如果我在oracle10.2.0.1 上,在运行select * from tbale(lin_ppfun());
在后台看到进程SQL如下:
select select * from tbale(lin_ppfun()); lin_sr_pp(
bd_prodline.prodlinename ,
sum(round(ha_ysd.jfshl, 4)) ,
sum(round(ha_ysd.jfshl* bd_invbasdoc.def2, 4)),
sum(round(ha_ysd.jfshl* bd_invbasdoc.def3, 4)) ,
sum(ha_ysd.jfybje) ,
sum(ha_ysd.jfybwsje) ,
sum(ha_ysd.jfybsj)
from ha_ysd
left join bd_invbasdoc on ha_ysd.cinventoryid = bd_invbasdoc.pk_invbasdoc
left join bd_measdoc on (bd_invbasdoc.pk_measdoc = bd_measdoc.pk_measdoc)
left outer join bd_prodline on (bd_invbasdoc.pk_prodline =
bd_prodline.pk_prodline)
如果我在oracle10.2.0.3 上,在运行select * from tbale(lin_ppfun());得到的是:
select * from tbale(lin_ppfun());
请问这个是BUG,还是有个开关设置,因为目前我的版本是升级到10.2.0.3,但是我想看到的是 lin_sr_pp(
bd_prodline.prodlinename ,
sum(round(ha_ysd.jfshl, 4)) ,
sum(round(ha_ysd.jfshl* bd_invbasdoc.def2, 4)),
sum(round(ha_ysd.jfshl* bd_invbasdoc.def3, 4)) ,
sum(ha_ysd.jfybje) ,
sum(ha_ysd.jfybwsje) ,
sum(ha_ysd.jfybsj)
from ha_ysd
left join bd_invbasdoc on ha_ysd.cinventoryid = bd_invbasdoc.pk_invbasdoc
left join bd_measdoc on (bd_invbasdoc.pk_measdoc = bd_measdoc.pk_measdoc)
left outer join bd_prodline on (bd_invbasdoc.pk_prodline =
bd_prodline.pk_prodline)
这样的形式!!
CREATE OR REPLACE FUNCTION "LIN_PPFUN"
return lin_sr_pp_table
as
PP lin_sr_pp_table;
begin
select
lin_sr_pp(
bd_prodline.prodlinename ,
sum(round(ha_ysd.jfshl, 4)) ,
sum(round(ha_ysd.jfshl* bd_invbasdoc.def2, 4)),
sum(round(ha_ysd.jfshl* bd_invbasdoc.def3, 4)) ,
sum(ha_ysd.jfybje) ,
sum(ha_ysd.jfybwsje) ,
sum(ha_ysd.jfybsj) bulk collect into PP
from ha_ysd
left join bd_invbasdoc on ha_ysd.cinventoryid = bd_invbasdoc.pk_invbasdoc
left join bd_measdoc on (bd_invbasdoc.pk_measdoc = bd_measdoc.pk_measdoc)
left outer join bd_prodline on (bd_invbasdoc.pk_prodline =
bd_prodline.pk_prodline)
retrun pp;
end
如果我在oracle10.2.0.1 上,在运行select * from tbale(lin_ppfun());
在后台看到进程SQL如下:
select select * from tbale(lin_ppfun()); lin_sr_pp(
bd_prodline.prodlinename ,
sum(round(ha_ysd.jfshl, 4)) ,
sum(round(ha_ysd.jfshl* bd_invbasdoc.def2, 4)),
sum(round(ha_ysd.jfshl* bd_invbasdoc.def3, 4)) ,
sum(ha_ysd.jfybje) ,
sum(ha_ysd.jfybwsje) ,
sum(ha_ysd.jfybsj)
from ha_ysd
left join bd_invbasdoc on ha_ysd.cinventoryid = bd_invbasdoc.pk_invbasdoc
left join bd_measdoc on (bd_invbasdoc.pk_measdoc = bd_measdoc.pk_measdoc)
left outer join bd_prodline on (bd_invbasdoc.pk_prodline =
bd_prodline.pk_prodline)
如果我在oracle10.2.0.3 上,在运行select * from tbale(lin_ppfun());得到的是:
select * from tbale(lin_ppfun());
请问这个是BUG,还是有个开关设置,因为目前我的版本是升级到10.2.0.3,但是我想看到的是 lin_sr_pp(
bd_prodline.prodlinename ,
sum(round(ha_ysd.jfshl, 4)) ,
sum(round(ha_ysd.jfshl* bd_invbasdoc.def2, 4)),
sum(round(ha_ysd.jfshl* bd_invbasdoc.def3, 4)) ,
sum(ha_ysd.jfybje) ,
sum(ha_ysd.jfybwsje) ,
sum(ha_ysd.jfybsj)
from ha_ysd
left join bd_invbasdoc on ha_ysd.cinventoryid = bd_invbasdoc.pk_invbasdoc
left join bd_measdoc on (bd_invbasdoc.pk_measdoc = bd_measdoc.pk_measdoc)
left outer join bd_prodline on (bd_invbasdoc.pk_prodline =
bd_prodline.pk_prodline)
这样的形式!!
你这个要用动态sql才行
sql_string varchar2(200);
temp_table varchar2(200);
begin
select LIN_PPFUN() from into temp_table dual;
sql_string:='select * from '|| temp_table;
execute immediate sql_string;
end;
execute immediate v_sql
bulk collect into PP
但是主要的问题,是我在不同版本的ORACOLE查看的进程问题,!!想问问,有什么办法设置!!
能否说明一下你所说的后台进程SQL是如何获得的?
我觉得这才是这个问题的关键。
select v$sql.sql_text from v$session,v$sql where v$session.sql_id=v$sql.sql_id
不过你的这个问题我还真的说不清楚到底是不是版本差异,
不过以我的感觉应该不会是BUG的。