存储过程执行报无效数字解决方法

存储过程执行报无效数字
存储过程

CREATE OR REPLACE PROCEDURE UpdatePiecesChn(pieceidStr  in varchar2,

                                            updateChnid in NUMBER,

                                            fromChnid   in number) is

begin



  delete from sc_st_chn_ps

   where pieceid in (select pieceid

                       from sc_st_chn_ps

                      where chnid = updateChnid

                        and pieceid in (pieceidStr))

     and chnid = fromChnid;

  update sc_st_chn_ps

     set chnid = updateChnid

   where pieceid in (pieceidStr)

     and chnid = fromChnid;

  commit;

end UpdatePiecesChn;

然后调用

 exec UpdatePiecesChn('1555,1557,1560,1576,1577,1578,1547,1561,1573,1574,1586,1588,1590,1592,1593,1663,1664,2675,2676,2679,2681,2682,2683,2705,2708,2709,3722,3682,3723,5330,5331,5332,5563',504,198);

会报错误，错误提示信息：ORA-01722: 无效数字

我大概也知道原因是 in后面pieceidStr对应的值不合法
正确的应该是

  delete from sc_st_chn_ps

   where pieceid in (select pieceid

                       from sc_st_chn_ps

                      where chnid = updateChnid

                        and pieceid in (1555,1557,1560,1576,1577,1578,1547,1561,1573,1574,1586,1588,1590,1592,1593,1663,1664,2675,2676,2679,2681,2682,2683,2705,2708,2709,3722,3682,3723,5330,5331,5332,5563))

而我这边变为了

  delete from sc_st_chn_ps

   where pieceid in (select pieceid

                       from sc_st_chn_ps

                      where chnid = updateChnid

                        and pieceid in (‘1555,1557,1560,1576,1577,1578,1547,1561,1573,1574,1586,1588,1590,1592,1593,1663,1664,2675,2676,2679,2681,2682,2683,2705,2708,2709,3722,3682,3723,5330,5331,5332,5563’))

请问这个该怎么改? 这一串字符是前台传过来的，谢谢


------解决思路----------------------
这种的用动态拼装sql

CREATE OR REPLACE PROCEDURE UpdatePiecesChn(pieceidStr  in varchar2,

                                            updateChnid in NUMBER,

                                            fromChnid   in number) is

v_del_sql varchar2(100);

v_upd_sql varchar2(100);

begin

  v_del_sql:='delete from sc_st_chn_ps where pieceid in (select pieceid  from sc_st_chn_ps where chnid = updateChnid and pieceid in ('
------解决思路----------------------
pieceidStr
------解决思路----------------------
')) and chnid = fromChnid';

  v_upd_sql:='update sc_st_chn_ps set chnid = updateChnid  where pieceid in ('
------解决思路----------------------
pieceidStr
------解决思路----------------------
')  and chnid = fromChnid';

  execute immediate v_del_sql;

  execute immediate v_upd_sql;

  commit;

end UpdatePiecesChn;

------解决思路----------------------

-- 这个出错了，系统 会把 pieceidStr 当做一个数值来看，是一个数值，而不是多个。

   where pieceid in (pieceidStr)

-- 采用楼上的动态语句是一个方法，也可以使用 instr 函数，但是存在一个隐式转化

where instr(  ',' 
------解决思路----------------------
 pieceidStr  
------解决思路----------------------
 ',' ,  ',' 
------解决思路----------------------
 piecdeid 
------解决思路----------------------
 ',') > 0 

------解决思路----------------------

引用:

这种的用动态拼装sql

CREATE OR REPLACE PROCEDURE UpdatePiecesChn(pieceidStr  in varchar2,

                                            updateChnid in NUMBER,

                                            fromChnid   in number) is

v_del_sql varchar2(100);

v_upd_sql varchar2(100);

begin

  v_del_sql:='delete from sc_st_chn_ps where pieceid in (select pieceid  from sc_st_chn_ps where chnid = updateChnid and pieceid in ('
------解决思路----------------------
pieceidStr
------解决思路----------------------
')) and chnid = fromChnid';

  v_upd_sql:='update sc_st_chn_ps set chnid = updateChnid  where pieceid in ('
------解决思路----------------------
pieceidStr
------解决思路----------------------
')  and chnid = fromChnid';

  execute immediate v_del_sql;

  execute immediate v_upd_sql;

  commit;

end UpdatePiecesChn;