存储过程执行报无效数字

[shixitong]

存储过程

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’))

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

[IceIsabel]

这种的用动态拼装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;

[卖水果的net]

-- 这个出错了，系统 会把 pieceidStr 当做一个数值来看，是一个数值，而不是多个。
   where pieceid in (pieceidStr)
-- 采用楼上的动态语句是一个方法，也可以使用 instr 函数，但是存在一个隐式转化
where instr(  ',' || pieceidStr  || ',' ,  ',' || piecdeid || ',') > 0 

[lyangsoft]

引用 1 楼 IceIsabel 的回复:

这种的用动态拼装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;

[binsweet]

那你可以把 in 改为 instr 比如： instr (‘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’,pieceid)>0