mysql sql语句中变量_mysql – 获取一个字符串变量并在“IN”语句中使用它

我有一个接受名为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;

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

标签：mysql,sql,mysqli,oracle,oracle11g

来源： https://codeday.me/bug/20190623/1266674.html