如何在SQL Developer中输入多值参数的绑定 [英] How to enter binds for a multi-valued parameter in SQL Developer

问题描述

我有很多带有命名参数的SQL，我需要这些命名参数才能在SQL Developer中执行.对于参数为标量值的SQL，将SQL粘贴到工作表中很容易，并且SQL Developer会提示我(在标题为"Enter Binds"的对话框中)输入参数值.但是对于参数需要保留多个值的情况，如下所示:

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:

select count(*) from foo 
where foo.id in (:ids)

其中:ids需要替换为1,2,3，以便执行的查询为

where, say, :ids needs to be replaced with 1,2,3 so that the query executed is

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:

是否存在用于在输入绑定"对话框中输入多个值的语法，以便SQL Developer能够正确执行替换?还是将绑定严格限制为标量值?

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?

我正在使用Oracle SQL Developer 3.2.20.09.

I'm using Oracle SQL Developer 3.2.20.09.

推荐答案

这不是SQL Developer的限制，它只是绑定变量的工作方式.您正在有效地进行以下操作:

This isn't a SQL Developer restriction, it's just how bind variables work. You're effectively doing:

select count(*) from foo 
where foo.id in ('1,2,3')

...实际上是in (to_number('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().不过，您可以 cheat 发挥一些创造力. xmltable函数会将逗号分隔的字符串转换为各具有一个值的行:

You can't enter multiple values at a bind prompt, or supply multiple values to an in() with a single bind. You can cheat be a bit inventive though. The xmltable function will convert the comma-separated string into rows with one value in each:

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 

这篇关于如何在SQL Developer中输入多值参数的绑定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！