Use multiple result sets
Oracle does not support bulk queries, so you cannot return multiple result sets from one command. When using a stored procedure, returns a plurality of result sets similar to returning a single result set; must use the REF CURSOR output parameter. To return multiple result sets, use multiple Ref cursor output parameters.
The following is a package specification that returns two result sets (all EMPLOYEES and JOBS records:
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;
The Bao Zhengwen as follows:
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 *.
End geteMPloyeesandjobs;
End select_employees_jobs;
The following code shows how to populate two related tables in the DataSet using the two result sets returned from the above package:
// 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 include 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"]. Column ["job_id"],
DS. Tables ["Employees"]. Column ["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"]);
The console output shows the second employee:
Employee ID: 101; Job Title: Administration Vice PRESIDENT
From: http://www.microsoft.com/china/msdn/library/data/dataaccess/dmsdnorsps.mspx
The error encountered: I encountered an unprocessed handle error during development, which is not returned for all output parameters.
