Oracle中存储过程传入按逗号分割的数值参数时报错的问题

前两天写存储过程,需要用到多参数传入,传入的参数是给一个select语句的where条件使用的。
但是写的时候遇到一个麻烦的问题,这个where条件的参数必须是number型的,而且使用的是in,也就是多个参数,比如select * from table_name_1 where id in (1,2,3,4,5),其中的1,2,3,4,5即是通过存储过程的参数传入的。
这里有个问题,如果在sql中,where id in (1,2,3,4,5)这条语句会认为1,2,3,4,5是五个单独的number型,而不会将其作为一个字符串。但是如果在存储过程中作为一个整体的参数进行传入时,即会将1,2,3,4,5判定为‘1,2,3,4,5’,而’1,2,3,4,5’是一个字符串,这时候sql执行的实际上是select * from table_name_1 where id in (‘1,2,3,4,5’),而id是一个number型的,这时候就会报错。
原存储过程大致如下:

如上述的存储过程,由于传入的参数an_id是一个number型的,如果是一个单独的数字,过程执行没有问题,但如果是一个字符串,如’1,2,3,4,5’,这样就会报错,因为’1,2,3,4,5’是一个varchar。
如果将传入的参数an_id 改为varchar型的,如create or replace procedure test_prc(an_id in varchar),这时候虽然在传入的时候类型一致了,但是在执行select语句时——where id in (an_id),由于表table_name_1的id列是number型的, 传入的实际上是’1,2,3,4,5’,这时候,也会报错。
这种情况据我所知可以有两种方法来解决,第一种是使用动态sql;第二种是使用instr函数来解决。有的朋友使用的是动态sql,我没使用这种方法,第一是自己不太会写,第二是觉得太繁琐。这里重点说的是使用instr函数。以后可能也会尝试使用动态sql来试试看,到时候再来更新吧。
其实使用instr函数也没有什么多说的,仔细看懂了理解了就行了,先贴出结果吧。

INSTR函数是个字符串搜索函数,默认情况下有两个参数:instr(string,set),用于在string中寻找set的位置,如果找到,返回set子串的第一个字符的位置,如果没有找到,则返回0。
之所以在开始和结束都加上’,’,是为去掉当查询11的时候,会将1和11都取出来。
至此,问题解决。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

Time limit is exhausted. Please reload CAPTCHA.