存储过程执行报无效数字

2015-04-03 03:31:32 531 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Oracle11gR2 并行执行存储过程现在我们有3个存储过程，分别是p1,p2,p3 如果按下列方式执行 begin p1; p2; p3; end; 它们是串行的。我们可以使用DBMS_SCHEDULER包来间接实现并行执行，具体用法如下： begin dbms_scheduler.create_job(job_name => dbms_scheduler.generate_jo... 阅读详情

存储过程

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 Server 存储过程 SQL Server 存储过程阅读详情

shixitong

基础和管理 17,379 成员 95,111 内容

+ 关注 + 加入社区

切换为时间正序

binsweet 2015-04-07

打赏
举报

那你可以把 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

lyangsoft 2015-04-03

打赏
举报

引用 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;

卖水果的net 2015-04-03

打赏
举报


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

IceIsabel 2015-04-03

打赏
举报

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

存储过程执行报无效数字

相关推荐