sql - Oracle SQL 变量 : SELECT with STRING list of values in an IN clause

Question

这可以正常工作:

DEFINE numbers = '1,2,3,4,5';
SELECT * FROM table1 WHERE items IN (&numbers);

这样做:

SELECT * FROM table1 WHERE items IN ('a','b','c','d'); error

这会返回一个snytax ERROR:

DEFINE letters = 'a','b','c','d';
SELECT * FROM table1 WHERE items IN (&letters);  -- result syntax ERROR

我如何使用 WHERE ... IN 子句使用由 DEFINE 创建的字符串列表？

Answer 1

用双引号将 DEFINE 值括起来:

define letters = "'a','b','c','d'"

set verify on

select * from dual where dummy in (&letters);

old   1: select * from dual where dummy in (&letters)
new   1: select * from dual where dummy in ('a','b','c','d')

no rows selected

请注意，挑战实际上是如何DEFINE 一个多词字符串，因为这是同一个问题:

SQL> define letters = I love kittens
SQL> define letters
DEFINE LETTERS         = "I" (CHAR)

SQL> define letters = 'a','b','c','d'
SQL> define letters
DEFINE LETTERS         = "a" (CHAR)

双引号解决了这个问题:

SQL> define letters = "I love kittens"
SQL> define letters
DEFINE LETTERS         = "I love kittens" (CHAR)

为了完整起见，另一种设置define 变量的方法是通过select 语句使用column new_value 方法。过去，这是为在 SQL*Plus 报告中动态设置页面标题而提供的，但它在脚本中很方便。 (这也是将类型设置为 number 的唯一方法，但没有任何区别。)

SQL> col letters new_value letters
SQL> select q'{'a','b','c','d'}' as letters from dual;

LETTERS
---------------
'a','b','c','d'

1 row selected.

SQL> def letters
DEFINE LETTERS         = "'a','b','c','d'" (CHAR)

SQL> select 123.456 as letters from dual;

   LETTERS
----------
   123.456

1 row selected.

SQL> def letters
DEFINE LETTERS         =    123.456 (NUMBER)