oracle返回多个结果集,Oracle查询/存储过程返回多个结果集

CREATE OR REPLACE PACKAGE SELECT_JOB_HISTORY AS

TYPE T_CURSOR IS REF CURSOR;

PROCEDURE GetJobHistoryByEmployeeId

(

p_employee_id IN NUMBER,

cur_JobHistory OUT T_CURSOR

);

END SELECT_JOB_HISTORY;包：

CREATE OR REPLACE PACKAGE BODY SELECT_JOB_HISTORY AS

PROCEDURE GetJobHistoryByEmployeeId

(

p_employee_id IN NUMBER,

cur_JobHistory OUT T_CURSOR

)

IS

BEGIN

OPEN cur_JobHistory FOR

SELECT * FROM JOB_HISTORY

WHERE employee_id = p_employee_id;

END GetJobHistoryByEmployeeId;

END SELECT_JOB_HISTORY;客户：

// create connection

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

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

// create the command for the stored procedure

OracleCommand cmd = new OracleCommand();

cmd.Connection = conn;

cmd.CommandText = "SELECT_JOB_HISTORY.GetJobHistoryByEmployeeId";

cmd.CommandType = CommandType.StoredProcedure;

// add the parameters for the stored procedure including the REF CURSOR

// to retrieve the result set

cmd.Parameters.Add("p_employee_id", OracleType.Number).Value = 101;

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

ParameterDirection.Output;

// open the connection and create the DataReader

conn.Open();

OracleDataReader dr = cmd.ExecuteReader();

// output the results and close the connection.

while(dr.Read())

{

for(int i = 0; i < dr.FieldCount; i++)

Console.Write(dr[i].ToString() + ";");

Console.WriteLine();

}

conn.Close();如果你必须加入这些表，你也可以使用普通连接并在客户端上拆分结果(imho这就是很多ORM的工作方式)。