Oracle:我如何参数化一个列表?

I have a query with a where clause that looks like:

我有一个带有where子句的查询，看起来是:

WHERE field IN ( 1, 2, 3 )

Is possible to parametrize this so it looks something like:

可以参数化它，使它看起来像:

WHERE field in ( :list )?

在(:列表)中的哪个字段?

How do you create the :list parameter, assuming it's a list of ints, that could be anywhere from 1 to 10 ints?

如何创建:list参数，假设它是一个int类型的列表，可以是1到10个int类型?

ASP.net, webforms if that makes a difference.

net, webforms，如果有区别的话。

4 个解决方案

#1

Same answer as SQL Server, already asked here: Parameterize an SQL IN clause

与SQL Server相同，这里已经问过:参数化SQL IN子句

#2

There are two ways of accommodating Dynamic IN lists:

表中有两种适应动态的方法:

1. Convert the comma separated list into a derived table (AKA inline view)
2. 将逗号分隔的列表转换为派生表(也称为内联视图)
3. Use dynamic SQL
4. 使用动态SQL

Non-Dynamic

---

Most prefer the non-dynamic SQL approach - this link provides various ways to do it. The biggest reason to use this over:

大多数人更喜欢非动态SQL方法——这个链接提供了各种方法。使用这个的最大原因是:

WHERE :list LIKE '%,' || t.column || ',%'

...is that the above:

…是,上图:

1. Will never be able to use an index
2. 永远不能使用索引吗
3. Won't be able to match the first or last parameter in the list, because commas are required on either end to match
4. 将无法匹配列表中的第一个或最后一个参数，因为任何一端都需要逗号来匹配

The simple fact is, it won't work as intended. A regex, supported on Oracle 10g+, would allow for conditional checking on the column but still faces the problem of rendering an index as moot

简单的事实是，它不会像预期的那样工作。Oracle 10g+支持的regex允许对列进行有条件的检查，但仍然面临将索引呈现为无实际意义的问题

Dynamic SQL

---

Mention "dynamic SQL", and you likely will be hounded about SQL injection attacks. Using a bind variable alleviates the concern.

提到“动态SQL”，您可能会受到SQL注入攻击的困扰。使用绑定变量可以缓解这个问题。

That said, dynamic SQL requires the least change to the query.

也就是说，动态SQL对查询的更改最少。

#3

As far as I know, you cannot. You'd be mixing values and operators in the same place.

据我所知，你不能。你将在同一个地方混合值和运算符。

However, it should be fairly simple to create automatically an array of parameters in ASP.NET and get this dynamically:

但是，在ASP中自动创建参数数组应该相当简单。NET动态获取:

WHERE field IN (:list1, :list2, :list3, :list4)

#4

This question 'how do I bind an in list' seems to come up a lot.

“我如何绑定一个in列表”这个问题似乎经常出现。

The easiest 'trick' to do this in Oracle is using the answer I posted here:

在Oracle中最简单的“技巧”就是使用我在这里发布的答案:

Dynamic query with HibernateCritera API & Oracle - performance

动态查询与hibernate natecritera API & Oracle - performance

I cannot pretent it is my trick, as it came from Tom Kyte's blog, who is an Oracle guru!

我不能相信这是我的诀窍，因为它来自汤姆·凯特的博客，他是一位神谕大师!