代码之家 › 专栏 › 技术社区 › Caverman

Oracle参数化SQL using IN子句,多个值不工作

plsql oracle

Caverman · 技术社区 · 8 年前

我有一个参数化的SQL语句,它使用IN子句用一个查询更新多个记录。它是一个整数字段,RID(记录ID)来执行更新。如果我只传递一个RID,它可以工作,但是如果传递多个值,我将得到 Error: ORA-01722: invalid number .

这是代码:

sbQuery.Append("update EXC_LOG set supv_emp_id=:userId, status=:exceptionStatus, supv_comments=:exceptionComment ");
sbQuery.Append("where RID in (:rid)");
ctx.Database.ExecuteSqlCommand(sbQuery.ToString(),
                                new OracleParameter("userId", UserId),
                                new OracleParameter("exceptionStatus", exceptionStatus),
                                new OracleParameter("exceptionComment", comment),
                                new OracleParameter("rid", rid));

如果传入一个值RID,则有效;但如果传入多个逗号分隔的值(例如:123455668899),则会出现无效数字错误。

使用参数时,如何传入多个整数值?

1 回复 | 直到 8 年前

Alex Poole 8 年前

您正在向中传递一个字符串参数 IN() . 如果它恰好包含一个数字,那么您实际上正在执行:

where RID in ('12345')

它是通过隐式转换进行处理的,因为 RID 列是数字,如下所示:

where RID in (to_number('12345'))

这很好。但是对于一个字符串参数中的多个值,您实际上要做的是:

where RID in (to_number('12345,5566,8899'))

和 to_number('12345,5566,8899') 将抛出ORA-01722:无效数字。

有多种方法可以将分隔字符串解压为单独的值,但一种简单的方法是将它们视为XPath序列并通过XMLTable调用将其放入:

sbQuery.Append("where RID in (select RID from XMLTable(:rid columns RID number path '.'))");

作为这种方法的演示,首先,XMLTable调用如何使用SQL*Plus绑定变量扩展字符串:

var rid varchar2(30);

exec :rd := '12345,5566,8899';

select RID from XMLTable('12345,5566,8899' columns RID number path '.');

       RID
----------
     12345
      5566
      8899

然后在针对虚拟表的虚拟查询中:

with EXC_LOG (RID, SUPV_EMP_ID, STATUS, SUPV_COMMENT) as (
            select 12345, 123, 'OK', 'Blah blah' from dual
  union all select 8899, 234, 'Failed', 'Some comment' from dual
  union all select 99999, 456, 'Active', 'Workign on it' from dual
)
select *
from EXC_LOG
where RID in (select RID from XMLTable('12345,5566,8899' columns RID number path '.'));

       RID SUPV_EMP_ID STATUS SUPV_COMMENT 
---------- ----------- ------ -------------
     12345         123 OK     Blah blah    
      8899         234 Failed Some comment

您的代码将只使用相同的过滤器进行更新而不是选择。