Oracle/c#：如何使用带有 select 语句的绑定变量来返回多条记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2915122/
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/c#: How do i use bind variables with select statements to return multiple records?
提问by twiga
I have a question regarding Oracle bind variables and select statements.
我有一个关于 Oracle 绑定变量和选择语句的问题。
What I would like to achieve is do a select on a number different values for the primary key. I would like to pass these values via an array using bind values.
我想要实现的是对主键的多个不同值进行选择。我想使用绑定值通过数组传递这些值。
select * from tb_customers where cust_id = :1
int[] cust_id = { 11, 23, 31, 44 , 51 };
int[] cust_id = { 11, 23, 31, 44 , 51 };
I then bind a DataReaderto get the values into a table.
然后我绑定 aDataReader以将值放入表中。
The problem is that the resulting table only contains a single record (for cust_id=51). Thus it seems that each statement is executed independently (as it should), but I would like the results to be available as a collective (single table).
问题是结果表只包含一条记录（for cust_id=51）。因此，似乎每个语句都是独立执行的（应该如此），但我希望结果可以作为一个集合（单个表）使用。
A workaround is to create a temporary table, insert all the values of cust_idand then do a join against tb_customers. The problem with this approach is that I would require temporary tables for every different type of primary key, as I would like to use this against a number of tables (some even have combined primary keys).
一种解决方法是创建一个临时表，插入 的所有值，cust_id然后对 进行连接tb_customers。这种方法的问题是我需要为每种不同类型的主键使用临时表，因为我想对许多表使用它（有些甚至组合了主键）。
Is there anything I am missing?
有什么我想念的吗？
回答by Posthuma
Not asking the question as to why you would want to do this to begin with. Shouldn't the sql statement be something like
不要问你为什么要这样做的问题。sql语句不应该是这样的
select * from tb_customers where cust_id = 11 or 23 or ...
Edit:
编辑：
I am limited in Oracle but when I look at the documentation I think that you might have to do something like this:
我在 Oracle 中受到限制，但是当我查看文档时，我认为您可能必须执行以下操作：
variable i number
exec :i := 11
select * from tb_customers where cust_id = :i
This would allow you to take advantage of binding. You will have to add each record return to your own collection since it will still only return one at a time.
这将允许您利用绑定。您必须将每个记录返回添加到您自己的集合中，因为它仍然一次只返回一个。
回答by Adam Butler
I know this was asked a while ago but not a brilliant answer.
我知道这是前一段时间问过的，但不是一个很好的答案。
I would do something like this - please excuse the crude psudo code
我会做这样的事情 - 请原谅粗略的伪代码
string bindList = "";
for(int ii=0;ii<cust_id.count;++ii)
{
if(ii == 0)
{
bindList += ":" + ii;
}
else
{
bindList += ",:" + ii;
}
OracleParameter param = new OracleParameter();
param.dbType = types.int;
param.value = cust_id[ii];
command.Parameters.Add(param);
}
query = "select * from tb_customers where cust_id in(" + bindList + ")";
So then query ends up having in(:1,:2,:3,etc) and each of these are bound separately.
那么查询最终有 in(:1,:2,:3,etc) 并且这些中的每一个都单独绑定。
There is also a similar question here: OracleParameter and IN Clause
这里也有一个类似的问题：OracleParameter and IN Clause
