Retrieve multiple Oracle Ref Cursor using .NET data Provider for Oracle

本文介绍如何使用Oracle数据库中的REFCURSOR返回多个结果集,并通过.NET Framework中的System.Data.OracleClient进行访问。文中详细展示了创建存储过程及客户端访问的具体步骤。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

Introduction
In my earlier article Multiple Result Sets in ADO.net using SqlClient ,we have seen how to retrieve multiple results using Sqlclient against SQL Server. This was a fairly simple and straight forward. We can achieve the same against Oracle database with a slight difference. We need to iron out couple of things before we get to nuts and bolts of it.
We need System.Data.OracleClient.Dll assembly that is very similar to System.Data.SqlClient.Dll,.NET Managed Provider forOracle is not a part of .NET Version 1.0, a separate download click here to download . For .NET version 1.1, it’s going to be part of the bundle. If you are new to .Net framework provider for Oracle read my articles on code101 Boost performance with .net data provider for oracle
We need a stored procedure to return multiple results set using REF CURSOR. For novice, A ref cursor is a PL/SQL data type that you can use in a query to fetch data. Each ref cursor query is associated with a PL/SQL function that returns a strongly typed ref cursor.
Lets get feet dirty
Create a oracle package, I assume you know what a package by the way it has two parts Specification and body
Package Specification
CREATE OR REPLACE PACKAGE PKG_MUltiResultset as

 TYPE MyRefCur is REF CURSOR;

 procedure GetReadOnlyData(EmpCur OUT MyRefCur,

     DeptCur OUT MyRefCur,

     SalCur OUT MyRefCur);

END;Package body CREATE OR REPLACE PACKAGE BODY PKG_MUltiResultset as

  PROCEDURE GetReadOnlyData(EmpCur OUT MyRefCur,

         DeptCur OUT MyRefCur,

      SalCur OUT MyRefCur)

  IS

  BEGIN

    open EmpCur for select * from emp;

    open DeptCur for select * from dept;

    open SalCur for select * from salgrade;

  END;

 END;
Lets us get to the client part to access this stored procedure. We will establish connection to Oracle database retrieve data from three tables in one shot and fill up the dataset and bind the data to DataGrids.I am using Dataset you can try it with OracleDataReader as well.
NOTE: The Prefix is always Oracle for all the classes in Syatem.Data.OracleClient name space. Reference name spaces

本文来自优快云博客,转载请标明出处:http://blog.youkuaiyun.com/yangyibang/archive/2008/06/19/2566857.aspx

 

Added: 回传多个result set的方式,可直接使用Procedure进行.此时使用SYS_REFCURSOR 代替Ref Cursor...且不需使用Package进行控制。

如:

CREATE OR REPLACE PROCEDURE GETMOKPNINFORESULT(MOLIKE     IN VARCHAR2,
                                               DELINE     IN VARCHAR2,
                                               SCHESTDATE DATE,
                                               SCHEENDATE DATE,
                                               CUST       VARCHAR2,
                                               ResultCUR     OUT  SYS_REFCURSOR,
                                               TempMOTable OUT sys_refcursor,
                                               MOBaseInfo OUT SYS_REFCURSOR) AS

begin

end;

 

 

---sample

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值