oracle存储过程中动态SQL的绑定变量问题,请高手急救!!!
ModuleCalculate:='update T_SALARY_SQL set '||replace(ModuleCalculate,'sal','salary')||' where company_id = :1 and salary_list_cd = :2';
execute immediate ModuleCalculate using company,salaryList;
其中company和salaryList是存储过程传入的变量。
在存储过程中单步调试,进行到上面语句的第一行就报错了:
ORA-06502:PL/SQL:数字或值错误
但是执行
ModuleCalculate:='update T_SALARY_SQL set '||replace(ModuleCalculate,'sal','salary')||' where company_id = :1';
execute immediate ModuleCalculate using company;
和
ModuleCalculate:='update T_SALARY_SQL set '||replace(ModuleCalculate,'sal','salary')||' where salary_list_cd = :2';
execute immediate ModuleCalculate using salaryList;
都正确。