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

首页 > C++

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

51自学网 http://www.wanshiok.com

以下代码运行了该存储过程。

// create the 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 = "COUNT_JOB_HISTORY";
cmd.CommandType = CommandType.StoredProcedure;

// add the parameter specifying the employee for whom to delete records
cmd.Parameters.Add("p_employee_id", OracleType.Number).Value = 102;

OracleString rowId;
// execute the stored procedure
conn.Open();
int rowsAffected = cmd.ExecuteNonQuery();
conn.Close();

Console.WriteLine("Rows affected: " + rowsAffected);

如果您尚未修改默认的 HR 安装，则 JOB_HISTORY 表中员工 102 的记录被删除，并且向控制台输出以下内容：

Rows affected: 1

访问返回值

RETURN 语句立即将控制从存储过程返回到调用程序。Oracle 存储过程中的 RETURN 语句无法像在 T-SQL 中那样返回值。

Oracle 函数是计算并返回单个值的子程序。它们的结构类似于存储过程，不同之处在于它们总是具有必须返回值的 RETURN 子句。

下面是一个返回指定员工的电子邮件的函数：

CREATE OR new FUNCTION GET_EMPLOYEE_EMAIL (
    p_employee_id NUMBER
)
RETURN VARCHAR2
IS p_email VARCHAR2(25);
BEGIN
    SELECT EMAIL INTO p_email FROM EMPLOYEES
    WHERE EMPLOYEE_ID = p_employee_id;

    RETURN p_email;
END GET_EMPLOYEE_EMAIL;

执行函数的方式与执行存储过程的方式相同。可使用 ParameterDirection.ReturnValue 参数获得由函数返回的结果。以下代码显示了使用方法：

// create the connection
OracleConnection conn = new OracleConnection("Data Source=oracledb;
User Id=UserID;Password=Password;");

// create the command for the function
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "GET_EMPLOYEE_EMAIL";
cmd.CommandType = CommandType.StoredProcedure;

// add the parameters, including the return parameter to retrieve
// the return value
cmd.Parameters.Add("p_employee_id", OracleType.Number).Value = 101;
cmd.Parameters.Add("p_email", OracleType.VarChar, 25).Direction =
ParameterDirection.ReturnValue;

// execute the function
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

// output the result
Console.WriteLine("Email address is: " + cmd.Parameters["p_email"].Value);

控制台输出显示了员工 101 的电子邮件地址。

Email address is: NKOCHHAR
结果集与 REF CURSOR
可使用 REF CURSOR 数据类型来处理 Oracle 结果集。REF CURSOR 是一个指向 PL/SQL 查询所返回的结果集的指针。与普通的游标不同，REF CURSOR 是一个变量，它是对游标的引用，可以在执行时将其设置为指向不同的结果集。使用 REF CURSOR 输出参数可以将 Oracle 结构化程序的结果集传递回调用应用程序。通过在调用应用程序中定义 OracleType.Cursor 数据类型的输出参数，可以访问 REF CURSOR 所指向的结果集。在使用 REF CURSOR 的过程中，OracleConnection 必须保持打开状态。

包
PL/SQL 和 T-SQL 中的存储过程之间的一个重大差异是 PL/SQL 所使用的 Oracle 包结构。在 T-SQL 中没有等效元素。包是在逻辑上相关的编程块（如存储过程和函数）的容器。它包含两个部分：

• 规范：定义包的名称，并为包中的每个存储过程或函数提供方法签名（原型）。规范头还定义所有全局声明。规范的样式类似于 C 或 C++ 头文件。

• 正文：包含包头中定义的存储过程和函数的代码。

每个存储过程或函数的参数都出现在括号内，并且用逗号分隔。每个参数还根据需要用以下三个标识符中的一个进行标记：

• IN：该值从调用应用程序传递到 PL/SQL 块。如果未指定标识符，则 IN 为默认传递方向。

• OUT：该值由存储过程生成，并传递回调用应用程序。

• INOUT：该值被传递到 PL/SQL 块，可能在该块内部进行修改，然后返回到调用应用程序。

每个参数也都被标记以指示数据类型。

以下包规范定义了四个过程，它们在 HR 架构的 LOCATIONS 表中创建、检索、更新和删除数据。

CREATE OR new PACKAGE CRUD_LOCATIONS AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GetLocations (cur_Locations OUT T_CURSOR);
PROCEDURE UpdateLocations (p_location_id IN NUMBER,
    p_street_address IN VARCHAR2,
    p_postal_code IN VARCHAR2,
    p_city IN VARCHAR2,
    p_state_province IN VARCHAR2,
    p_country_id IN CHAR);
PROCEDURE DeleteLocations (p_location_id IN NUMBER);
PROCEDURE InsertLocations (p_location_id OUT NUMBER,
    p_street_address IN VARCHAR2,
    p_postal_code IN VARCHAR2,
    p_city IN VARCHAR2,
    p_state_province IN VARCHAR2,
    p_country_id IN CHAR);
END CRUD_LOCATIONS;

以下代码摘自上述包规范的包正文，说明了 GetLocations 包中的第一个过程的实现细节：

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

2/2 首页上一页 1 2

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

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