oracle 如何在 SQL Developer 中为多值参数输入绑定
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22075001/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to enter binds for a multi-valued parameter in SQL Developer
提问by Nathan Hughes
I have a lot of SQL with named parameters that I need to be able to execute in SQL Developer. For SQL where the parameters are scalar values it's easy to paste the SQL into a worksheet and SQL Developer will prompt me (in a dialog with the title "Enter Binds") to enter the parameter values. But for cases where the parameter needs to hold multiple values, like this:
我有很多带有命名参数的 SQL，我需要能够在 SQL Developer 中执行它们。对于参数为标量值的 SQL，很容易将 SQL 粘贴到工作表中，SQL Developer 将提示我（在标题为“输入绑定”的对话框中）输入参数值。但是对于参数需要保存多个值的情况，如下所示：
select count(*) from foo
where foo.id in (:ids)
where, say, :idsneeds to be replaced with 1,2,3so that the query executed is
例如，其中:ids需要替换为，1,2,3以便执行的查询是
select count(*) from foo
where foo.id in (1,2,3)
I try entering the values into the dialog (and I've tried delimiting with commas, or just spaces, or wrapping everything in parens), and regardless what I try I get the error message:
我尝试在对话框中输入值（并且我尝试用逗号分隔，或者只是空格，或者将所有内容都包装在括号中），无论我尝试什么，我都会收到错误消息：
ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause:
*Action:
Is there a syntax for entering the multiple values into the "Enter Binds" dialog so that SQL Developer will be able to perform the substitution correctly? Or are the bindings strictly limited to scalar values?
是否有将多个值输入“输入绑定”对话框的语法，以便 SQL Developer 能够正确执行替换？还是绑定严格限于标量值？
I'm using Oracle SQL Developer 3.2.20.09.
我使用的是 Oracle SQL Developer 3.2.20.09。
采纳答案by Alex Poole
This isn't a SQL Developer restriction, it's just how bind variables work. You're effectively doing:
这不是 SQL Developer 的限制，它只是绑定变量的工作方式。你正在有效地做：
select count(*) from foo
where foo.id in ('1,2,3')
... which is really in (to_number('1,2,3')), hence the error. It'll work for a single value, give odd results for two values if your decimal separator is a comma, and fail for anything more.
...这是真的in (to_number('1,2,3'))，因此错误。它适用于单个值，如果您的小数点分隔符是逗号，则为两个值提供奇数结果，并且失败。
You can't enter multiple values at a bind prompt, or supply multiple values to an in()with a single bind. You can cheatbe a bit inventive though. The xmltablefunction will convert the comma-separated string into rows with one value in each:
您不能在绑定提示下输入多个值，也不能通过in()单个绑定向一个提供多个值。不过，你可以作弊有点创造性。该xmltable函数会将逗号分隔的字符串转换为每行一个值的行：
var ids varchar2(50);
exec :ids := '1,2,3';
select * from xmltable(:ids);
COLUMN_VALUE
------------
1
2
3
You can then use that as a look-up table:
然后您可以将其用作查找表：
select count(*)
from xmltable(:ids) x
join foo f on f.id = to_number(x.column_value);
COUNT(*)
----------
3
