参数定义 i_ward_id in varchar2
存储过程 where ward_id in (i_ward_id )
经测试发现存错过程写死如where ward_id in (‘11’,‘22’,‘33’ )是没有问题的。
但是如果从程序传过来的参数i_ward_id =“‘11’,‘22’,‘33’”就会出现问题。
经查资料找到解决办法如下:
SELECT REGEXP_SUBSTR('17,20,23', '[^,]+', 1, LEVEL, 'i') AS STR
FROM DUAL CONNECT BY LEVEL <=
LENGTH('17,20,23') - LENGTH(REGEXP_REPLACE('17,20,23', ',', ''))+1;
oracle函数参考:https://www.cnblogs.com/yuany69/p/6093017.html
目的是把字符串集合拆分成一列,进行in
如何使用:
oracle中
where ward_id in (
SELECT REGEXP_SUBSTR(i_ward_id, '[^,]+', 1, LEVEL, 'i') AS STR FROM DUAL
CONNECT BY LEVEL <= LENGTH(i_ward_id) - LENGTH(REGEXP_REPLACE(i_ward_id, ',', ''))+1
)
程序代码中拼装字符串,并作为参数传入
var ward_id = lstResult.Select(q => q.WARD_ID );
strWards = string.Join(",", tempList);