在Oracle中返回多个结果集

Oracle 不支持批量查询，因此无法从一个命令返回多个结果集。使用存储过程时，返回多个结果集类似于返回单个结果集；必须使用 REF CURSOR 输出参数。要返回多个结果集，请使用多个 REF CURSOR 输出参数。

以下是返回两个结果集（全部 EMPLOYEES 和 JOBS 记录）的包规范：

CREATE OR new PACKAGE SELECT_EMPLOYEES_JOBS AS

TYPE T_CURSOR IS REF CURSOR;

PROCEDURE GetEmployeesAndJobs (

cur_Employees OUT T_CURSOR,

cur_Jobs OUT T_CURSOR

);

END SELECT_EMPLOYEES_JOBS;

包正文如下所示：

CREATE OR new PACKAGE BODY SELECT_EMPLOYEES_JOBS AS

PROCEDURE GetEmployeesAndJobs

(

cur_Employees OUT T_CURSOR,

cur_Jobs OUT T_CURSOR

)

IS

BEGIN

-- return all EMPLOYEES records

OPEN cur_Employees FOR

SELECT * FROM Employees;

-- return all JOBS records

OPEN cur_Jobs FOR

SELECT * FROM Jobs;

END GetEmployeesAndJobs;

END SELECT_EMPLOYEES_JOBS;

以下代码显示了如何使用从上述包中返回的两个结果集来填充 DataSet 中的两个相关表：

// create the connection

OracleConnection conn = new OracleConnection("Data Source=oracledb;

User Id=UserID;Password=Password;");

// define the command for the stored procedure

OracleCommand cmd = new OracleCommand();

cmd.Connection = conn;

cmd.CommandText = "SELECT_EMPLOYEES_JOBS.GetEmployeesAndJobs";

// add the parameters including the two REF CURSOR types to retrieve

// the two result sets

cmd.Parameters.Add("cur_Employees", OracleType.Cursor).Direction =

ParameterDirection.Output;

cmd.Parameters.Add("cur_Jobs", OracleType.Cursor).Direction =

ParameterDirection.Output;

cmd.CommandType = CommandType.StoredProcedure;

// create the DataAdapter and map tables

OracleDataAdapter da = new OracleDataAdapter(cmd);

da.TableMappings.Add("Table", "EMPLOYEES");

da.TableMappings.Add("Table1", "JOBS");

// create and fill the DataSet

DataSet ds = new DataSet();

da.Fill(ds);

// create a relation

ds.Relations.Add("EMPLOYEES_JOBS_RELATION",

ds.Tables["JOBS"].Columns["JOB_ID"],

ds.Tables["EMPLOYEES"].Columns["JOB_ID"]);

// output the second employee (zero-based array) and job title

// based on the relation

Console.WriteLine("Employee ID: " +

ds.Tables["EMPLOYEES"].Rows[1]["EMPLOYEE_ID"] +

"; Job Title: " +

ds.Tables["EMPLOYEES"].Rows[1].GetParentRow(

"EMPLOYEES_JOBS_RELATION")["JOB_TITLE"]);

控制台输出显示了第二个员工的职务：

Employee ID: 101; Job Title: Administration Vice President

来自于:http://www.microsoft.com/china/MSDN/library/data/dataAccess/DMSDNorsps.mspx

遇到的错误：在开发时遇到了未处理的句柄错误，其原因是没有为所有的输出参数返回值。