C# 从 ADO.NET 调用 Oracle 时批处理多个选择语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1062569/
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
Batch multiple select statements when calling Oracle from ADO.NET
提问by stuart donald
I want to batch multiple select statements to reduce round trips to the database. The code looks something like the pseudo code below. It works perfectly on SQL Server, but does not work on Oracle - Oracle complains about the sql syntax. I have had a look around and the only examples I can find of returning multiple result sets from Oracle are using Stored Procedures. Is it possible to do this in Oracle without using Stored Procedures? I am using the MS Oracle data provider, but could use the ODP.Net one if needed.
我想批处理多个 select 语句以减少到数据库的往返。该代码类似于下面的伪代码。它在 SQL Server 上完美运行，但在 Oracle 上不起作用 - Oracle 抱怨 sql 语法。我环顾四周，我能找到的从 Oracle 返回多个结果集的唯一示例是使用存储过程。是否可以在不使用存储过程的情况下在 Oracle 中执行此操作？我正在使用 MS Oracle 数据提供程序，但如果需要，可以使用 ODP.Net 之一。
var sql = @"
select * from table1
select * from table2
select * from table3";
DbCommand cmd = GetCommand(sql);
using(var reader = cmd.ExecuteReader())
{
dt1.Load(reader);
reader.NextResult();
dt2.Load(reader);
reader.NextResult();
dt3.Load(reader);
}
采纳答案by tuinstoel
You should write an anonymous pl/sql block that returns 3 ref cursors.
您应该编写一个返回 3 个引用游标的匿名 pl/sql 块。
edit1:Here it is done in an anonymous pl/sql block with one cursor. It should work with three too. Oracle ref cursors don't lock data and they are the fastest way to return a result set from a pl/sql procedure or an anonymous pl/sql bloc.
edit1：这里是在一个带有一个游标的匿名 pl/sql 块中完成的。它也应该与三个一起工作。Oracle 引用游标不锁定数据，它们是从 pl/sql 过程或匿名 pl/sql 块返回结果集的最快方法。
http://www.oracle.com/technetwork/issue-archive/2006/06-jan/o16odpnet-087852.html
http://www.oracle.com/technetwork/issue-archive/2006/06-jan/o16odpnet-087852.html
回答by Kirtan
why not use stored procedures instead?
为什么不使用存储过程呢？
But, if you want to batch them in an inline query, you can use a semicolon (;) to seperate the statements.
但是，如果您想在内联查询中对它们进行批处理，则可以使用分号 (;) 来分隔语句。
var sql = @"BEGIN
select * from table1;
select * from table2;
select * from table3;
END;";
EDIT: You take a look at this SO question.
编辑：你看看这个 SO question。
EDIT2: Take a look at this answer.
EDIT2：看看这个答案。
回答by DCookie
How about:
怎么样：
var sql = @"
select * from table1 UNION
select * from table2 UNION
select * from table3";
回答by Daniel Layne
An example in C# with multiple cursors and an input parameter:
带有多个游标和输入参数的 C# 示例：
string ConnectionString = "connectionString";
OracleConnection conn = new OracleConnection(ConnectionString);
StringBuilder sql = new StringBuilder();
sql.Append("begin ");
sql.Append("open :1 for select * from table_1 where id = :id; ");
sql.Append("open :2 for select * from table_2; ");
sql.Append("open :3 for select * from table_3; ");
sql.Append("end;");
OracleCommand comm = new OracleCommand(sql.ToString(),_conn);
comm.Parameters.Add("p_cursor_1", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);
comm.Parameters.Add("p_id", OracleDbType.Int32, Id, ParameterDirection.Input);
comm.Parameters.Add("p_cursor_2", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);
comm.Parameters.Add("p_cursor_3", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);
conn.Open();
OracleDataReader dr = comm.ExecuteReader();
