ORACLE-SQL：定义并使用多字符串变量

I'm currently using a database where some of the numbers are stored in a text format (fyi: external database, not possible to change).

我目前正在使用一个数据库，其中一些数字以文本格式存储（fyi：外部数据库，无法更改）。

My codes have grown over time so I started to use variables at the top of my code to avoid adjusting the code multiple times when running a new analysis

我的代码随着时间的推移而增长，因此我开始在代码顶部使用变量，以避免在运行新分析时多次调整代码

Example:

例：

define var = '1234'

select * from products
where
  art_id = '&&var'
;

This gives code gives me all articles with the ID 1234 - keep in mind, the ID is stored as a text (hence the "'"s)

这给代码提供了ID为1234的所有文章 - 请记住，ID存储为文本（因此“s”）

Now, what do I have to do when I want to query for multiple products?

现在，当我想查询多个产品时，我该怎么办？

This doesn't work:

这不起作用：

define var = '1234','2345'

select * from products
where
  art_id in ('&&var')
;

==> I miss the products with the ID 2345

==>我想念ID为2345的产品

Also, this doesn't work:

此外，这不起作用：

define var = ''1234','2345''

select * from products
where
  art_id in (&&var)
;

==> Error: missing expression

==>错误：缺少表达式

What I obviously want to create is a case in which the where clause is constructed like this:

我显然想要创建的是where子句的构造方式如下：

art_id in ('1234','2345')

Thanks!

谢谢！

1 个解决方案

#1

You need to escape the quotes:

你需要逃避报价：

define var = '''1234'',''2345'''

For example:

例如：

SQL> define var = '''a'', ''b'''
SQL> select 1 from dual where 'a' in ( &&var);
old   1: select 1 from dual where 'a' in ( &&var)
new   1: select 1 from dual where 'a' in ( 'a', 'b')

         1
----------
         1

Please notice that here I used quotes to handle string values; if you had numeric values, you could avoid quoting:

请注意，我在这里使用引号来处理字符串值;如果你有数字值，你可以避免引用：

SQL> define var = '123, 234'
SQL> select 1 from dual where 1 in (&&var);
old   1: select 1 from dual where 1 in (&&var)
new   1: select 1 from dual where 1 in (123, 234)

no rows selected

SQL> select 1 from dual where 123 in (&&var);
old   1: select 1 from dual where 123 in (&&var)
new   1: select 1 from dual where 123 in (123, 234)

         1
----------
         1

1 个解决方案

#1

更多相关文章

随机推荐