本文讨论了如何使用 ADO.NET访问 Oracle存储过程（称为 SQL 编程块）和函数（返回单个值的编程块）。

　　您可以使用以下托管数据提供程序连接到 Oracle 数据库：Microsoft.NET Oracle 提供程序、OLE DB .NET 提供程序、ODBC .NET 数据提供程序以及 Oracle 的 ODP.NET 提供程序。本文使用用于 Oracle 的 Microsoft?.NET 框架数据提供程序。使用 Oracle ODP.NET 数据提供程序或用于 OLE DB 的 Microsoft .NET 框架数据提供程序时可使用不同的功能。

　　Oracle .NET 数据提供程序随 .NET 框架 1.1 一起提供。如果您使用的是 .NET 框架 1.0，您将需要下载.NET Managed Provider for Oracle。无论是哪个版本，数据提供程序类都位于 System.Data.OracleClient 命名空间中。

　　概述

　　PL/SQL 是 SQL 的 Oracle 实现。它与 Microsoft?SQL Server? 所使用的 T-SQL 类似，但也有一些不同之处，本文稍后对此进行了详细讨论。与 T-SQL 一样，PL/SQL 扩展了标准SQL。PL/SQL 用于定义命名编程块，如存储过程、函数和触发器。

　　类

　　可使用 System.Data.OracleClient 命名空间中类的子集来执行 Oracle 存储过程和函数。下表对这些类进行了说明：

　　类 说明

　　OracleCommand

　　针对 Oracle 数据库执行的存储过程的 SQL 语句。

　　OracleConnection

　　打开的数据库连接。

　　OracleParameter

　　OracleCommand 的参数，也可能是它到 DataColumn 的映射。

　　OracleParameterCollection

　　OracleParameter 对象的集合。

　　OracleType

　　Oracle 数据类型和结构的枚举。

　　执行存储过程

　　执行 Oracle 存储过程与执行 SQL Server 存储过程类似。下面的步骤说明了如何执行 Oracle 存储过程和检索它返回的结果。

　　1.

　　在 HR 架构中创建一个名为 COUNT_JOB_HISTORY 的存储过程，以计算 JOB_HISTORY 表中的记录数。

　　CREATE OR new PROCEDURE COUNT_JOB_HISTORY

　　(

　　reccount OUT NUMBER

　　)

　　IS

　　BEGIN

　　SELECT COUNT(*) INTO reccount

　　FROM JOB_HISTORY;

　　END COUNT_JOB_HISTORY;

　　HR 架构是默认 Oracle 安装中包含的一个示例。

　　2.

　　将 System.Data.OracleClient.dll（用于 Oracle 的 Microsoft .NET 框架数据提供程序）的引用添加到项目中。

　　3.

　　使用 using 指令导入 OracleClient 类中的类型。

　　using System.Data.OracleClient;

　　4.

　　创建一个 OracleConnection 对象。

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

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

　　用您的值替换 Oracle 数据库的名称、用户名和密码。

　　5.

　　创建一个 OracleCommand 对象。将其 Connection 属性设置为第 4 步中创建的连接。将其 CommandText 设置为存储过程的名称，并将其 CommandText 属性设置为 CommandType.StoredProcedure。当您调用第 8 步中介绍的一个 Execute() 方法时，该命令对象将执行指定的存储过程。

　　OracleCommand cmd = new OracleCommand();

　　cmd.Connection = conn;

　　cmd.CommandText = "COUNT_JOB_HISTORY";

　　cmd.CommandType = CommandType.StoredProcedure;

　　如果您的存储过程名称含有特殊字符，您就必须使用转义序列。您可以通过重置 CommandText 属性来重用现有的 OracleCommand 对象。

　　6.

　　创建输入、输出和返回值的 OracleParameter 对象，并将其添加到 OracleCommand 对象的参数集合中。

　　cmd.Parameters.Add("reccount", OracleType.Number).Direction =

　　ParameterDirection.Output;

　　该行代码是以下两行代码的简写形式：

　　cmd.Parameters.Add("reccount", OracleType.Number);

　　cmd.Parameters["reccount"].Direction = ParameterDirection.Output;

7.

　　如果您要检索结果集，请创建 DataSet、DataTable 或 DataReader。在本示例中，我们只是获取第 6 步中创建的输出参数中的计数。

　　8.

　　使用 OracleCommand 对象的一个 Execute 方法打开连接并执行存储过程，如下所示：

　　方法 说明

　　ExecuteReader

　　通过执行能够返回结果集的存储过程生成 OracleDataReader。

　　ExecuteNonQuery

　　执行不返回结果集的查询或过程，返回受影响的行数。

　　ExecuteOracleNonQuery

　　执行查询，返回受影响的行数。

　　该方法还使用 OracleString 参数来返回 UPDATE、INSERT 或 DELETE 查询所修改的最后一行的行 ID。

　　ExecuteScalar

　　执行一个查询或过程，并且返回查询或过程的返回值，或者将结果集第一行第一列的值作为 .NET 框架数据类型返回。

　　ExecuteOracleScalar

　　执行一个查询或过程，并且返回查询或过程的返回值，或者将结果集第一行第一列的值作为 OracleType 数据类型返回。

　　使用完连接后，不要忘记将其关闭。

　　conn.Open();

　　cmd.ExecuteNonQuery();

　　conn.Close();

　　如果您要使用 DataAdapter 来填充 DataTable 或 DataSet，可以依靠 DataAdapter 来打开和关闭连接。

　　9.

　　处理结果。在我们的示例中，可在显示到控制台的输出参数中得到记录数：

　　Console.WriteLine(cmd.Parameters["reccount"].Value);

　　下面是在本示例中开发的用于执行存储过程和检索结果的代码：

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

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

　　OracleCommand cmd = new OracleCommand();

　　cmd.Connection = conn;

　　cmd.CommandText = "COUNT_JOB_HISTORY";

　　cmd.CommandType = CommandType.StoredProcedure;

　　cmd.Parameters.Add("reccount", OracleType.Number).Direction =

　　ParameterDirection.Output;

　　conn.Open();

　　cmd.ExecuteNonQuery();

　　conn.Close();

　　Console.WriteLine(cmd.Parameters["reccount"].Value);

不返回数据的存储过程

　　OracleCommand 类的 ExecuteOracleNonQuery() 方法用于执行不返回任何行的 SQL 语句或存储过程。该方法返回一个 int 值，表示受 UPDATE、INSERT 和 DELETE 命令影响的行数；如果没有任何行受到影响，则返回 -1。如果您所执行的 INSERT、DELETE 或 UPDATE 语句恰好影响一行，则该方法具有单个参数 OracleString out rowid，该参数唯一标识 Oracle 数据库中受影响的行。可以使用该值来优化后续相关查询。

　　还可以使用 OracleCommand 类的 ExecuteNonQuery() 方法来执行不返回数据的存储过程，但您将无法获得上面介绍的唯一行标识符。

　　尽管上述命令都不会返回任何数据，但映射到参数的输出参数和返回值仍然使用数据进行填充。这使您可以使用上述任一命令从存储过程返回一个或多个标量值。

　　以下 Oracle 存储过程删除了由单个输入参数指定的员工的所有工作经历，并且不返回任何数据。

　　CREATE OR new PROCEDURE DELETE_JOB_HISTORY

　　(

　　p_employee_id NUMBER

　　)

　　IS

　　BEGIN

　　DELETE FROM job_history

　　WHERE employee_id = p_employee_id;

　　END DELETE_JOB_HISTORY;

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

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

评论 (0) All

登陆 还没注册？