将:var与"IN"一起使用WHERE子句中的运算符

问题描述：

我通常使用MySQL或SQL Server，并且在Oracle SQL Developer中遇到了相当大的问题.

I'm usually working with MySQL or SQL Server and I've experienced quite a problem in Oracle SQL Developer.

我有这样的查询(使复制问题变得简单)

I have query like this (making it simple just to replicate my issue):

SELECT *
FROM table t1
WHERE t1.date > :START_DATE AND t1.date < :END_DATE AND t1.id IN (:IDS)

当我运行此查询时，对话框窗口将打开，并提示我输入变量.

When I run this query, the dialog window opens and I'm prompted to enter the variables.

问题是当我输入逗号分隔的ID(例如5,6,7或带引号'5,6,7')时，出现此错误:

Problem is when I enter comma separated ids like 5,6,7 or with quotes '5,6,7' I get this error:

ORA-01722:无效的号码
01722. 00000-无效编号"
*原因:
*动作:

ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause:
*Action:

这里有什么想法吗?

PS:必须有对话框提示才能输入变量.同事对SQL不友好. ;)

PS: There has to be dialog prompt to enter variables. Colleagues are not SQL friendly. ;)

答

问题在于绑定变量:ids包含文字值(而不是文字值列表)，因此您的查询是:

The issue is that the bind variable :ids contains a literal value (rather than a list of literal values) so your query is:

AND t1.id IN ( '5,6,7' )

代替:

AND t1.id IN ( 5, 6, 7 )

您需要做的是传递集合(您可以使用外部语言从数组中定义该集合)并直接作为绑定变量传递):

What you need to do is either pass in a collection (which you could define from an array in an external language and pass in directly as a bind variable):

CREATE OR REPLACE TYPE intlist IS TABLE OF INTEGER;
/

SELECT *
FROM table t1
WHERE t1.date > :START_DATE
AND   t1.date < :END_DATE
AND   t1.id MEMBER OF intlist( 5, 6, 7 )

或使用LIKE比较列表:

SELECT *
FROM table t1
WHERE t1.date > :START_DATE
AND   t1.date < :END_DATE
AND   ',' || :ids || ',' LIKE '%,' || t1.id || ',%'

或传入分隔字符串文字并将其拆分:

SELECT *
FROM table t1
WHERE t1.date > :START_DATE
AND   t1.date < :END_DATE
AND   t1.id   IN ( SELECT TO_NUMBER( REGEXP_SUBSTR( :ids, '\d+', 1, LEVEL ) )
                   FROM   DUAL
                   CONNECT BY LEVEL <= REGEXP_COUNT( :ids, '\d+' ) );

(或者，如果使用替代变量而不是绑定变量，则逗号分隔的数字列表将在IN子句中起作用.)

(Or, a comma spearated list of numbers would work in the IN clause if you used a substitution variable instead of a bind variable.)

将:var与"IN"一起使用WHERE子句中的运算符

相关推荐