求execute immediate用法

Question

plsql存储过程中execute immediate为什么这样用不行呢,求解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存储过程如下: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)';
  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;
/

Answer 1

你上面那几个不是绑定变量，不需要using mcht,startdate,enddate;
还有就是最后先用变量保存你的sql语句，然后执行，这样方便看你执行的sql有没有问题

Answer 2

不用using mcht,startdate,enddate也不能执行

Answer 3

那就检查你拼出来的sql有没有问题了。先用变量把你拼出的sql打印出来看看拼的有没有问题，特别注意字符时，拼的时候两个''代表一个字符'

Answer 4

strsql变量的结果是: and cardno>=startcartno and cardno<=endcardno,startcartno代表输入的起始卡号,endcardno输入的终止卡号

Answer 5

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;
/

Answer 6

原来的存储过程是这样的,想把它改成动态拼接EXEC SQL SELECT count(*) into :maxrow
                                               FROM (select DISTINCT TRAN_SLA_KEY,
                                                           TX_CODE,          
                                                            cardno,          
                                                            valid_date,      
                                                            sett_postdate,  
                                                            substr(TRAN_SLA_KEY,9,8), 
                                                            input_mode,      
                                                            merch_id,             
                                                            REQ_TXNAMT,          
                                                            integral,        
                                                            trace_no,        
                                                            sys_no,          
                                                            orig_traceno,    
                                                            rsp_code,        
                                                            compl_flag,
                                                            TRANDATE,
                                                            substr(TRAN_SLA_KEY,17,5),
                                                            ADDIDATA1,
                                                            MER_ORDERNO,
                                                            REJ_CODE
                                                      from translog a where 
                                                         ((addidata1 like '%'||:PerName||'%') or (:PerName = 'Z'))
                                                     and ((addidata1 like '%'||:PerTel||'%') or (:PerTel = 'Z'))
                                                     and ((addidata1 like '%'||:PerDate||'%') or (:PerDate = 'Z'))
                                                     and ((mer_orderno = :XMLpkgDATABuf.cardholder) or (:XMLpkgDATABuf.cardholder = 'Z'))
                                              /****  ((valid_date  = :XMLpkgDATABuf.valid_date) or (:XMLpkgDATABuf.valid_date = 'Z')) delete by GuWei 20110704 *****/
                                                     and ((EXISTS(select * from merchant where a.MERCH_ID=MERCH_ID and  EXISTS (select * from grppou where substr(merchant.GRP_NAME,1,8)=substr(MERGRP,1,8) and GRP_LEADER  = :XMLpkgDATABuf.merch_id)) or (merch_id  = :XMLpkgDATABuf.merch_id))
                                                          or (:XMLpkgDATABuf.merch_id = 'Z'))
                                                     and ((REQ_TXNAMT   = :XMLpkgDATABuf.amount) or (:XMLpkgDATABuf.amount = 1000000000))
                                                     and ((term_id  = :XMLpkgDATABuf.set_term) or (:XMLpkgDATABuf.set_term = 'Z'))
                                                     and ((trace_no  = :XMLpkgDATABuf.trace_no) or (:XMLpkgDATABuf.trace_no = 'Z'))
                                                     and ((orig_traceno  = :XMLpkgDATABuf.orig_traceno) or (:XMLpkgDATABuf.orig_traceno = 'Z'))
                                          /***  and ((sys_no  = :XMLpkgDATABuf.sys_no) or (:XMLpkgDATABuf.sys_no = 'Z')) delete by GuWei 20110704 ***/
                                                     and ((TRANDATE  = :tmp_makedate) or (:tmp_makedate = 'Z'))
                                                     and ((substr(TRAN_SLA_KEY,17,5)  = :XMLpkgDATABuf.instcode) or (:XMLpkgDATABuf.instcode = 'Z'))
                                                     and ((substr(TRAN_SLA_KEY,9,8)  = :XMLpkgDATABuf.term_id) or (:XMLpkgDATABuf.term_id = 'Z'))
                                      and ((BATCHID  = :XMLpkgDATABuf.batchid) or (:XMLpkgDATABuf.batchid = 'Z'))
                                                                and (((substr(cardno,5,5) = :XMLpkgDATABuf.MerOnCode) or (:XMLpkgDATABuf.MerOnCode = 'Z')) or ((substr(addidata1,5,5) = :XMLpkgDATABuf.MerOnCode) or (:XMLpkgDATABuf.MerOnCode = 'Z')))
                                                     and ((merch_id = :XMLpkgDATABuf.Rev_Id) or (:XMLpkgDATABuf.Rev_Id = 'Z'))
                                                     and ((((SUBSTR(cardno,1,18) >= :XMLpkgDATABuf.startno) or (:XMLpkgDATABuf.startno = 'Z'))
                                      and ((SUBSTR(cardno,1,18) <= :XMLpkgDATABuf.endno) or (:XMLpkgDATABuf.endno = 'Z')))
                                      or  (((SUBSTR(ADDIDATA1,1,18) <= :XMLpkgDATABuf.startno) and (SUBSTR(ADDIDATA1,20,18) >= :XMLpkgDATABuf.startno) or (:XMLpkgDATABuf.startno = 'Z'))
                                      and ((SUBSTR(ADDIDATA1,1,18) <= :XMLpkgDATABuf.endno) and (SUBSTR(ADDIDATA1,20,18) >= :XMLpkgDATABuf.endno) or (:XMLpkgDATABuf.endno = 'Z'))))
                                                     and ((tx_code  = :XMLpkgDATABuf.TxCode) or (:XMLpkgDATABuf.TxCode = 'Z'))
                                                     and ((sett_postdate  >= to_char(:XMLpkgDATABuf.sett_postdate)))
                                                     and ((sett_postdate  <= to_char(:XMLpkgDATABuf.tran_dt)))

Answer 7

动态拼接SQL语句,条件不确定,可能1个,也可能10个(如下图所示), 绑定变量using怎样写CREATE OR REPLACE PROCEDURE "CARD"."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);
begin
  recordcount:=888;
  strsql:='select count(*) from partranslog 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:=strsql||' and cardno>=:startcartno and cardno<=:endcardno';
    execute immediate strsql into recordcount using mcht,startdate,enddate,startcardno,endcardno;
  else
    execute immediate strsql into recordcount using mcht,startdate,enddate;
  end if;
  update proce set name=recordcount;
end trans;
/