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

最新推荐文章于 2022-03-02 17:28:21 发布

weixin_39752087

最新推荐文章于 2022-03-02 17:28:21 发布

阅读量1.4k

点赞数

CC 4.0 BY-SA版权

文章标签： oracle存储过程无效字符

本文链接：https://blog.youkuaiyun.com/weixin_39752087/article/details/111513581

博客围绕Oracle存储过程执行报错展开，执行存储过程时提示ORA - 01722无效数字，原因是in后面pieceidStr对应的值不合法。给出了动态拼装sql和使用instr函数两种解决思路，前者将sql语句动态拼接后执行，后者存在隐式转化。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

存储过程执行报无效数字

存储过程

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;