我有一个接受名为p_my_list_of_numbers的参数的过程.这是一个逗号分隔的字符串,看起来像’1,4,5,8,9,22,89′.

 PROCEDURE my_procedure ( p_my_list_of_numbers VARCHAR2)
 BEGIN

       SELECT * FROM my_table WHERE ID IN (1,4,5,8,9,22,89); //THIS RETURNS DATA
       SELECT * FROM my_table WHERE ID IN p_my_list_of_numbers; //DOES NOT RETURN ANYTHING

 END;

如何获取这个长字符串并能够在select查询中使用它以便它返回数据？

解决方法:

您可以使用以下子查询：

select regexp_substr('1,4,5,8,9,22,89','[^,]+', 1, level) from dual
connect by regexp_substr('1,4,5,8,9,22,89', '[^,]+', 1, level) is not null;

这会将逗号分隔值列表拆分为结果集.您的程序看起来与此类似：

 PROCEDURE my_procedure ( p_my_list_of_numbers VARCHAR2)
 BEGIN

       SELECT * FROM my_table 
       WHERE ID IN (
          select regexp_substr(p_my_list_of_numbers,'[^,]+', 1, level) 
          from dual
          connect by regexp_substr(p_my_list_of_numbers, '[^,]+', 1, level) is not null); 

 END;

当然,您可能想验证您的输入,但我认为这只是您问题的示例程序.