plsql存储过程中用逗号分隔的参数

问题描述：

create or replace procedure PROC_MYDATA (inputStr IN VARCHAR2,     
  p_RecordSet      IN OUT SYS_REFCURSOR) is
begin
  OPEN p_RecordSet FOR
     (select * from myTable where name in (inputStr));
end PROC_MYDATA;

在"PLSQL测试"窗口中，我正在尝试设置，

In the PLSQL Test window, I am trying to set,

inputStr ='A'，'B'

inputStr = 'A','B'

我收到此错误:

ORA-01722: invalid number

我还尝试将转义字符放在单引号中.

I also tried to put escape character for single quote.

inputStr ='''A'''，'''B'''

inputStr = '''A''','''B'''

相同的错误.

有人可以帮助我了解我在做什么错吗?

Can someone please help me understand what am I doing wrong?

答

恐怕这种方式不起作用:

I'm afraid it doesn't work this way:

SELECT * from myTable where name in (inputStr);

您可以使用动态SQL，例如@Bob Jarvis的回答，也可以执行以下操作:

You can use dynamic SQL, as in @Bob Jarvis' answer, or you can do the following:

SELECT * FROM myTable WHERE REGEXP_LIKE(name, '^(' || REPLACE(inputStr, ',', '|') || ')$');

后者的困难在于，在Oracle中，正则表达式的最大长度为512个字节.因此，您的inputStr将被限制为508个字节(因为我们为锚点和分组添加了四个字节).

The difficulty with the latter is that, in Oracle, a regular expression can be at most 512 bytes long. So your inputStr would be limited to 508 bytes (since we're adding four bytes for the anchors and the grouping).

plsql存储过程中用逗号分隔的参数

相关推荐