一个function接收3个参数,返回结果集,但这3个参数在sqlstr定义中需要反复使用function querySjq(czryssswjgdm in varchar2,cxrqq in varchar2,cxrqz in varchar2)
return ResultSet is
sqlstr varchar2(1000);
rs ResultSet;
begin
sqlstr:='select nsrdzdah,sum(mdse) as mdse from kj_mdt
where nsr_swjg_dm like :czryssswjgdm
and to_char(xhrq,''yyyy-mm-dd'')>=:cxrqq
and to_char(xhrq,''yyyy-mm-dd'')<=:cxrqz
or(to_char(xgrq,''yyyy-mm-dd'')>=:cxrqq
and to_char(xgrq,''yyyy-mm-dd'')<=:cxrqz)
group by nsrdzdah';
open rs for sqlstr using czryssswjgdm,cxrqq,cxrqz,cxrqq,cxrqz;
return rs;
end querySjq;在open rs for sqlstr using 后面需要对每个参数进行顺序指定,这样做太麻烦了,参数能一次性传递到sqlstr中吗?有什么简便方法实现吗?
return ResultSet is
sqlstr varchar2(1000);
rs ResultSet;
begin
sqlstr:='select nsrdzdah,sum(mdse) as mdse from kj_mdt
where nsr_swjg_dm like :czryssswjgdm
and to_char(xhrq,''yyyy-mm-dd'')>=:cxrqq
and to_char(xhrq,''yyyy-mm-dd'')<=:cxrqz
or(to_char(xgrq,''yyyy-mm-dd'')>=:cxrqq
and to_char(xgrq,''yyyy-mm-dd'')<=:cxrqz)
group by nsrdzdah';
open rs for sqlstr using czryssswjgdm,cxrqq,cxrqz,cxrqq,cxrqz;
return rs;
end querySjq;在open rs for sqlstr using 后面需要对每个参数进行顺序指定,这样做太麻烦了,参数能一次性传递到sqlstr中吗?有什么简便方法实现吗?
不需要这样传递参数也可以的。1.声明一个cursor cr
2.打开cursor,参数前面不需要加冒号的。比如open cr for select nsrdzdah,sum(mdse) as mdse from kj_mdt
where nsr_swjg_dm like czryssswjgdm 。。
3.fetch cr into rs
4.return rs