使用ADO.NET访问Oracle 9i存储过程(上)

核心提示： CREATE OR new PACKAGE BODY CRUD_LOCATIONS ASPROCEDURE GetLocations (cur_Locations OUT T_CURSOR)ISBEGIN OPEN cur_Locations FOR SELECT * FROM LOCAT

CREATE OR new PACKAGE BODY CRUD_LOCATIONS AS
PROCEDURE GetLocations (cur_Locations OUT T_CURSOR)
IS
BEGIN
　　 OPEN cur_Locations FOR
　　 SELECT * FROM LOCATIONS;
END GetLocations;
　　-- Implementation of other procedures ommitted.
　　END CRUD_LOCATIONS;

使用 DataReader

可以通过调用 OracleCommand 对象的 ExecuteReader() 方法来创建 OracleDataReader。本节说明如何使用 DataReader 来访问由存储过程 SELECT_JOB_HISTORY 返回的结果集。以下为包规范：

CREATE OR new 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;

包正文定义了一个过程，该过程检索指定员工的工作经历的结果集，并将其作为 REF CURSOR 输出参数返回：

　CREATE OR new 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;

以下代码执行该过程，根据结果集创建 DataReader，并将 DataReader 的内容输出到控制台。

　　// 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();