oracle 带IN语句的Oracle参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1625649/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Parameters with IN statement?
提问by Gareth
Got a c#.net app which I need to modify. The query at the moment effectively does this:
有我需要修改的 ac#.net 应用程序。目前的查询有效地做到了这一点：
select * from contract where contractnum = :ContractNum
(very simplified, just to show we're using an = and one parameter)
（非常简化，只是为了表明我们正在使用 = 和一个参数）
That parameter is read in from the Settings.Settings file on the C# app and has one string in it. I need to modify it to include multiple contracts, so I figure I can change the SQL to:
该参数是从 C# 应用程序的 Settings.Settings 文件中读取的，其中包含一个字符串。我需要修改它以包含多个合同，所以我想我可以将 SQL 更改为：
select * from contract where contractnum in (:ContractNum)
but that returns no results, no matter how I format the string in the parameter.
但无论我如何格式化参数中的字符串，都不会返回任何结果。
Is there a way I can get oracle to do an IN with a parameter?
有没有办法让oracle用参数做一个IN？
采纳答案by OMG Ponies
Have yet to find a db that supports evaluating a single string variable containing commas to separate as the sole INclause.
尚未找到支持评估包含逗号的单个字符串变量作为唯一IN子句的数据库。
Your options are to substring the variable so the comma delimited variable contents are turned into rows, so you can then join onto this. Or to use dynamic SQL, which is a SQL statement constructed as a string in a sproc before the statement is executed.
您的选择是对变量进行子字符串化，以便将逗号分隔的变量内容转换为行，然后您就可以加入该变量。或者使用动态 SQL，它是在执行语句之前在 sproc 中构造为字符串的 SQL 语句。
回答by tuinstoel
You can use an Oracle collection of numbers as a parameter (bind variable) when you use ODP.NET as dataprovider. This works with Oracle server 9, 10 or 11 and ODP.net release >= 11.1.0.6.20 .
当您使用 ODP.NET 作为数据提供者时，您可以使用 Oracle 数字集合作为参数（绑定变量）。这适用于 Oracle 服务器 9、10 或 11 和 ODP.net 版本 >= 11.1.0.6.20 。
A similar solution is possible when you use Devart's .NET dataprovider for Oracle.
当您使用 Devart 的 .NET dataprovider for Oracle 时，类似的解决方案是可能的。
Let's select the contracts with contractnum's 3 and 4.
让我们选择 contractnum 为 3 和 4 的合约。
We have to use an Oracle type to transfer an array of contract numbers to our query.
我们必须使用 Oracle 类型将一组合同编号传输到我们的查询中。
MDSYS.SDO_ELEM_INFO_ARRAYis used because if we use this already predefined Oracle type we don't have to define our own Oracle type. You can fill MDSYS.SDO_ELEM_INFO_ARRAYwith max 1048576 numbers.
MDSYS.SDO_ELEM_INFO_ARRAY之所以使用，是因为如果我们使用这个已经预定义的 Oracle 类型，我们就不必定义我们自己的 Oracle 类型。您最多可以填写MDSYS.SDO_ELEM_INFO_ARRAY1048576 个数字。
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
[OracleCustomTypeMappingAttribute("MDSYS.SDO_ELEM_INFO_ARRAY")]
public class NumberArrayFactory : IOracleArrayTypeFactory
{
public Array CreateArray(int numElems)
{
return new Decimal[numElems];
}
public Array CreateStatusArray(int numElems)
{
return null;
}
}
private void Test()
{
OracleConnectionStringBuilder b = new OracleConnectionStringBuilder();
b.UserID = "sna";
b.Password = "sna";
b.DataSource = "ora11";
using (OracleConnection conn = new OracleConnection(b.ToString()))
{
conn.Open();
using (OracleCommand comm = conn.CreateCommand())
{
comm.CommandText =
@" select /*+ cardinality(tab 10) */ c.* " +
@" from contract c, table(:1) tab " +
@" where c.contractnum = tab.column_value";
OracleParameter p = new OracleParameter();
p.OracleDbType = OracleDbType.Array;
p.Direction = ParameterDirection.Input;
p.UdtTypeName = "MDSYS.SDO_ELEM_INFO_ARRAY";
//select contract 3 and 4
p.Value = new Decimal[] { 3, 4 };
comm.Parameters.Add(p);
int numContracts = 0;
using (OracleDataReader reader = comm.ExecuteReader())
{
while (reader.Read())
{
numContracts++;
}
}
conn.Close();
}
}
}
The index on contract.contractnum isn't used when one omits hint /*+ cardinality(tab 10) */. I assumed contractnum is the primary key so this column will be indexed.
当省略提示 /*+ cardinality(tab 10) */ 时，不会使用 contract.contractnum 上的索引。我假设 contractnum 是主键，因此该列将被索引。
See also here: http://forums.oracle.com/forums/thread.jspa?messageID=3869879#3869879
另见此处：http: //forums.oracle.com/forums/thread.jspa?messageID=3869879#3869879
回答by Vincent Malgrat
you could use a pipelined function to transform a string into a table which could be used with the INoperator. For example (tested with 10gR2):
您可以使用流水线函数将字符串转换为可与IN运算符一起使用的表。例如（用 10gR2 测试）：
SQL> select * from table(demo_pkg.string_to_tab('i,j,k'));
COLUMN_VALUE
-----------------
i
j
k
with the following package:
使用以下软件包：
SQL> CREATE OR REPLACE PACKAGE demo_pkg IS
2 TYPE varchar_tab IS TABLE OF VARCHAR2(4000);
3 FUNCTION string_to_tab(p_string VARCHAR2,
4 p_delimiter VARCHAR2 DEFAULT ',')
5 RETURN varchar_tab PIPELINED;
6 END demo_pkg;
7 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY demo_pkg IS
2 FUNCTION string_to_tab(p_string VARCHAR2,
3 p_delimiter VARCHAR2 DEFAULT ',')
4 RETURN varchar_tab PIPELINED IS
5 l_string VARCHAR2(4000) := p_string;
6 l_first_delimiter NUMBER := instr(p_string, p_delimiter);
7 BEGIN
8 LOOP
9 IF nvl(l_first_delimiter,0) = 0 THEN
10 PIPE ROW(l_string);
11 RETURN;
12 END IF;
13 PIPE ROW(substr(l_string, 1, l_first_delimiter - 1));
14 l_string := substr(l_string, l_first_delimiter + 1);
15 l_first_delimiter := instr(l_string, p_delimiter);
16 END LOOP;
17 END;
18 END demo_pkg;
19 /
Package body created
Your query would look like this:
您的查询将如下所示：
select *
from contract
where contractnum in (select column_value
from table(demo_pkg.string_to_tab(:ContractNum)))
回答by Sergey
For using parameter with IN statement you can use this construction:
对于在 IN 语句中使用参数，您可以使用以下结构：
select * from contract where contractnum
in (select column_value from table (:ContractNum))
where ContractNum is the custom array type.
其中 ContractNum 是自定义数组类型。
回答by saminpa
I know this is an old question but it is one of several in which the selected answer did not solve my problem and I don't want to start yet another thread on this topic so I'll just put down what I found in my travels in the hope that it might help someone.
我知道这是一个老问题，但它是其中选定的答案没有解决我的问题的几个问题之一，我不想再开始关于这个话题的另一个话题，所以我会记下我在旅行中发现的希望它可以帮助某人。
I don't work with Oracle much but, like in SQL Server, it seems that to pass a table-valued parameter you need to have a corresponding UDT (user defined table) to which you have EXECUTE permissions (I could be wrong). This means that other answers suggesting the use of a built-in SYS UDT come with some freight and I couldn't figure out whether it really is possible to pass a table to something that is not a PL/SQL stored procedure in the current version of ODP.net.
我不太使用 Oracle，但是，就像在 SQL Server 中一样，似乎要传递表值参数，您需要具有相应的 UDT（用户定义表），您对其具有 EXECUTE 权限（我可能是错的）。这意味着建议使用内置 SYS UDT 的其他答案会带来一些麻烦，我无法确定是否真的可以将表传递给当前版本中不是 PL/SQL 存储过程的内容ODP.net 的。
Second, the string-parse solution is a kludge for all the obvious reasons (can't cache the execution plan or whatever Oracle calls it, doesn't scale well, etc).
其次，字符串解析解决方案由于所有明显的原因（无法缓存执行计划或 Oracle 调用它的任何内容，不能很好地扩展等）都是一团糟。
So I spent rather a lot of time trying do the IN-clause using a table-valued parameter on a datamart to which I have only READ permission before I was hit by a blinding flash of the obvious (At an ASP.net forum no less). Turns out Oracle supports Xml queries 'natively' so instead of passing an array of values you can pass an xml list (if that is all you need). Again, I may be wrong, but it gets handled as a legitimate bind parameter and this is an example of how simple it is to use (vb.net, ADO.net, ODP.net using NuGet package):
所以我花了很多时间尝试在数据集市上使用表值参数来执行 IN 子句，在我被明显的闪光击中之前我只有 READ 权限（在 ASP.net 论坛上不少）。原来 Oracle 支持“本机”Xml 查询，因此您可以传递一个 xml 列表（如果这就是您所需要的），而不是传递一组值。再说一次，我可能是错的，但它被作为一个合法的绑定参数处理，这是它使用起来多么简单的一个例子（vb.net、ADO.net、ODP.net 使用 NuGet 包）：
Dim xe As New XElement("l", New XElement("i", "ITEM-A"), New XElement("i", "ITEM-B"))
Using conn As New OracleConnection(myConnectionString)
conn.Open()
Using cmd As OracleCommand = conn.CreateCommand()
cmd.CommandType = CommandType.Text
Dim query As String
query = " SELECT s.FOO, q.BAR " & vbCrLf
query &= " FROM TABLE1 s LEFT OUTER JOIN " & vbCrLf
query &= " TABLE2 q ON q.ID = s.ID " & vbCrLf
query &= " WHERE (COALESCE(q.ID, 'NULL') NOT LIKE '%OPTIONAL%') AND "
query &= " (s.ID IN ("
query &= " SELECT stid "
query &= " FROM XMLTable('/l/i' PASSING XMLTYPE(:stid) COLUMNS stid VARCHAR(32) PATH '.')"
query &= " )"
query &= " )"
cmd.CommandText = query
Dim parameter As OracleParameter = cmd.Parameters.Add("stid", OracleDbType.NVarchar2, 4000)
parameter.Value = xe.ToString
Using r As OracleDataReader = cmd.ExecuteReader
While r.Read()
//Do something
End While
End Using
End Using
conn.Close()
This is more of an observation than a carefully researched solution so please comment if there is anything inappropriate about doing it this way.
这更像是一种观察而不是仔细研究的解决方案，因此如果这样做有任何不妥之处，请发表评论。
There is apparently a 4000 character limit using this method (2000 if NVARCHAR) so I had to watch my paging. The informative error message you get if you go over is
使用这种方法显然有 4000 个字符的限制（如果 NVARCHAR，则为 2000 个），所以我不得不看我的分页。如果你过去，你得到的信息性错误消息是
ORA-01460: unimplemented or unreasonable conversion requested
ORA-01460: 请求未实现或不合理的转换
