存儲過程執行報無效數字
存儲過程
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;
