从 ADO.NET 调用 Oracle 时批处理多个选择语句 [英] Batch multiple select statements when calling Oracle from ADO.NET

问题描述

我想批量处理多个 select 语句以减少到数据库的往返次数.该代码类似于下面的伪代码.它在 SQL Server 上完美运行，但在 Oracle 上不起作用——Oracle 抱怨 sql 语法.我环顾四周，我能找到的从 Oracle 返回多个结果集的唯一示例是使用存储过程.是否可以在不使用存储过程的情况下在 Oracle 中执行此操作?我正在使用 MS Oracle 数据提供程序，但如果需要，可以使用 ODP.Net.

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.

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);
}

推荐答案

您应该编写一个返回 3 个引用游标的匿名 pl/sql 块.

You should write an anonymous pl/sql block that returns 3 ref cursors.

edit1: 这里是在一个带有一个游标的匿名 pl/sql 块中完成的.它也应该与三个一起工作.Oracle 引用游标不锁定数据，它们是从 pl/sql 过程或匿名 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.

http://www.oracle.com/technetwork/issue-archive/2006/06-jan/o16odpnet-087852.html

这篇关于从 ADO.NET 调用 Oracle 时批处理多个选择语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！