从ADO.NET调用甲骨文在批处理多个SELECT语句 [英] Batch multiple select statements when calling Oracle from ADO.NET

问题描述

我想批多个SELECT语句，以减少往返数据库。在code看起来像下面的伪code。它的工作原理完全SQL Server上，但不能在Oracle工作 - 甲骨文抱怨的SQL语法。我有一个环顾四周，唯一的例子我能找到返回从Oracle多个结果集是使用存储过程。是否有可能做到这一点在Oracle数据库，未使用存储过程？我使用的MS Oracle数据提供者，但如果需要，可以使用ODP.Net之一。

  VAR SQL = @
            请从表1 *
            请从表2 *
            请从表3*;

的DbCommand CMD = GetCommand（SQL）;
使用（VAR读卡器= cmd.ExecuteReader（））
{
   dt1.Load（读卡器）;
   reader.NextResult（）;
   dt2.Load（读卡器）;
   reader.NextResult（）;
   dt3.Load（读卡器）;
}
 

解决方案

您应该编写一个返回3引用游标匿名PL / SQL块。

EDIT1：这是与一个光标匿名PL / SQL块完成的。它应该有三个工作了。甲骨文REF游标不锁定数据，他们是返回结果从PL / SQL过程或匿名PL / SQL集团设定的最快方法。

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

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

解决方案

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

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调用甲骨文在批处理多个SELECT语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！