plsql存储过程中execute immediate为什么这样用不行呢,求解
- SQL code
execute immediate 'select count(*) from partranslog where merch_id=' || mcht || ' and sett_postdate>=' || to_char(startdate) || ' and sett_postdate<=' || to_char(enddate) || strsql into recordcount using mcht,startdate,enddate;
如果不加后边部分就可以:|| strsql into recordcount using mcht,startdate,enddate
存储过程如下:
- SQL code
create or replace procedure trans(mcht in varchar,sett in varchar,startcardno in varchar,endcardno in varchar,tx_code in varchar,banlace in varchar,startdate in varchar,enddate in varchar,traceno in varchar,batchno in varchar,termid in varchar,document in varchar,cardhold in varchar,lxname in varchar,telphone in varchar,gkdate in varchar,pagesize in number,currpage in number,recordcount out number)asstrsql varchar(1000);v_con number(8);begin recordcount:=88888; strsql:=' where merch_id=mcht and sett_postdate>=to_char(startdate) and sett_postdate<=to_char(enddate)'; if length(startcardno)>0 and length(endcardno)>0 then strsql:=' and cardno>=startcartno and cardno<=endcardno'; end if; execute immediate 'select count(*) from partranslog where merch_id=' || mcht || ' and sett_postdate>=' || to_char(startdate) || ' and sett_postdate<=' || to_char(enddate) || strsql into recordcount using mcht,startdate,enddate; update proce set name=recordcount;end trans;/
------解决方案--------------------
------解决方案--------------------
- SQL code
create or replace procedure trans(mcht in varchar, sett in varchar, startcardno in varchar, endcardno in varchar, tx_code in varchar, banlace in varchar, startdate in varchar, enddate in varchar, traceno in varchar, batchno in varchar, termid in varchar, document in varchar, cardhold in varchar, lxname in varchar, telphone in varchar, gkdate in varchar, pagesize in number, currpage in number, recordcount out number) as strsql varchar(1000); v_con number(8);begin recordcount := 88888; strsql := ' where merch_id=mcht and sett_postdate>=to_char(startdate) and sett_postdate<=to_char(enddate)'; --startdate,enddate是变量来的,你不能直接这样写进去 if length(startcardno) > 0 and length(endcardno) > 0 then strsql := ' and cardno>=startcartno and cardno<=endcardno'; end if; execute immediate 'select count(*) from partranslog where merch_id=' || mcht || ' and sett_postdate>=' || to_char(startdate) || ' and sett_postdate<=' || to_char (enddate) || strsql --前面那段已经是 where条件还不,后面还把strsql拼上去,这肯定有问题了, into recordcount using mcht, startdate, enddate; --你上面都没有用到绑定变量,你用using有问题 update proce set name = recordcount;end trans;/