PowerBuilder中在调用oracle存储过程时,怎么使用绑定变量

PowerBuilder中在调用oracle存储过程时,如何使用绑定变量?
在oracle的SQL追踪中发现大量如下记录:

SQL code

 
 BEGIN         TheCompChargeVerify( ParmComp => '15049910024408' , ParmNeedPayNo => '200909041141 
 41' , ParmCycle => '199805' , ParmInsure => '020101' , ParmFlag => '0' , ParmOperator => 'sa2'   
  ,RETERRTEXT=>:RETERRTEXT,RETERR=>:RETERR); END;   
 / 
 BEGIN         TheCompChargeVerify( ParmComp => '15049910024408' , ParmNeedPayNo => '200909041141 
 41' , ParmCycle => '199805' , ParmInsure => '020301' , ParmFlag => '0' , ParmOperator => 'sa2'   
  ,RETERRTEXT=>:RETERRTEXT,RETERR=>:RETERR); END;   
 / 
 BEGIN         TheCompChargeVerify( ParmComp => '15049910024408' , ParmNeedPayNo => '200909041141 
 41' , ParmCycle => '199806' , ParmInsure => '020101' , ParmFlag => '0' , ParmOperator => 'sa2'   
  ,RETERRTEXT=>:RETERRTEXT,RETERR=>:RETERR); END;   
 / 
 BEGIN         TheCompChargeVerify( ParmComp => '15049910024408' , ParmNeedPayNo => '200909041141 
 41' , ParmCycle => '199806' , ParmInsure => '020301' , ParmFlag => '0' , ParmOperator => 'sa2'   
  ,RETERRTEXT=>:RETERRTEXT,RETERR=>:RETERR); END;   
 / 
 BEGIN         TheCompChargeVerify( ParmComp => '15049910024408' , ParmNeedPayNo => '200909041141 
 41' , ParmCycle => '199807' , ParmInsure => '020101' , ParmFlag => '0' , ParmOperator => 'sa2'   
  ,RETERRTEXT=>:RETERRTEXT,RETERR=>:RETERR); END;   
 / 
 ...... 
 

根据这个记录,我估计是开发人员绑定变量使用的有问题,导致出现大量此类记录
这段代码是通过PB调用的
我对PB一点都不懂,PB开发人员给的调用代码如下:

SQL code

string Ls_RetErrText,ls_ID,ls_aka_append,ls_AAE140,ls_AAE002int Li_RetErr long ll_rows,ll_row DECLARE TheCompChargeVerifyePro PROCEDURE FOR TheCompChargeVerify             ( ParmComp      => :parmcomp,          ParmNeedPayNo => :ls_ID,          ParmCycle     => :ls_AAE002,          ParmInsure    => :ls_AAE140,          ParmFlag      => :ls_aka_append,          ParmOperator  => :ThisCurrEmpPurData.oper_name         )        USING SQLCA;          for ll_row = 1 to ll_rows     if dw_n.getitemnumber(ll_row,'flag') = 1 then         ls_ID         = dw_n.getitemstring(ll_row,'ID')         ls_aka_append = dw_n.getitemstring(ll_row,'aka_append')         ls_AAE140     = dw_n.getitemstring(ll_row,'AAE140')         ls_AAE002     = dw_n.getitemstring(ll_row,'AAE002')                  EXECUTE TheCompChargeVerifyePro;                 if sqlca.sqlcode < 0 then             lasterrtext = 'Source:t_comp_needpay_system->thecompchargeverify:调用单位缴费到帐复核过程时出错:' + sqlca.sqlerrtext             ROLLBACK USING SQLCA;             CLOSE TheCompChargeVerifyePro;             return false         end if                 FETCH TheCompChargeVerifyePro INTO :Ls_RetErrText,:Li_RetErr;          CLOSE TheCompChargeVerifyePro;                 if Li_RetErr <> 1 then             lasterrtext = '调用单位到帐复核过程时出错,错误编码为( ' + string( Li_RetErr ) + ' ),错误原因为:' + Ls_RetErrText             ROLLBACK USING SQLCA;             RETURN FALSE;         end if    end if;next    COMMIT USING SQLCA;   return true

上边的代码有什么问题吗?希望高手帮忙看看,不胜感激!

[解决办法]
这样试试，把定义放到循环里看看


SQL code
string Ls_RetErrText,ls_ID,ls_aka_append,ls_AAE140,ls_AAE002
int Li_RetErr 
long ll_rows,ll_row

 

 
for ll_row = 1 to ll_rows
if dw_n.getitemnumber(ll_row,'flag') = 1 then
ls_ID = dw_n.getitemstring(ll_row,'ID')
ls_aka_append = dw_n.getitemstring(ll_row,'aka_append')
ls_AAE140 = dw_n.getitemstring(ll_row,'AAE140')
ls_AAE002 = dw_n.getitemstring(ll_row,'AAE002')

DECLARE TheCompChargeVerifyePro PROCEDURE FOR TheCompChargeVerify 
( ParmComp => :parmcomp,
ParmNeedPayNo => :ls_ID,
ParmCycle => :ls_AAE002,
ParmInsure => :ls_AAE140,
ParmFlag => :ls_aka_append,
ParmOperator => :ThisCurrEmpPurData.oper_name
)
USING SQLCA;

EXECUTE TheCompChargeVerifyePro;

if sqlca.sqlcode < 0 then
lasterrtext = 'Source:t_comp_needpay_system->thecompchargeverify:调用单位缴费到帐复核过程时出错:' + sqlca.sqlerrtext
ROLLBACK USING SQLCA;
CLOSE TheCompChargeVerifyePro;
return false
end if

FETCH TheCompChargeVerifyePro INTO :Ls_RetErrText,:Li_RetErr; 
CLOSE TheCompChargeVerifyePro;

if Li_RetErr <> 1 then
lasterrtext = '调用单位到帐复核过程时出错,错误编码为( ' + string( Li_RetErr ) + ' ),错误原因为:' + Ls_RetErrText
ROLLBACK USING SQLCA;
RETURN FALSE;
end if

end if;
next
 
COMMIT USING SQLCA;
 
return true


[解决办法]
学习中...
帮你顶一下!
[解决办法]

SQL code

COMMIT USING SQLCA;
[解决办法]
这个有什么问题吗？按照代码来看就是应该有这么多SQL记录才对，没有的话才真是有问题了。

for ll_row = 1 to ll_rows
    if dw_n.getitemnumber(ll_row,'flag') = 1 then

这里是个循环，从1循环到ll_rows，如果flag=1，每次循环都会执行一次TheCompChargeVerifyePro，那么在ORACLE中就会产生一次SQL执行记录。
我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html 

 

查看更多 下一篇