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

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

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

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

概述

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

类

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

类 说明

OracleCommand

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

OracleConnection

打开的数据库连接

OracleParameter

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

OracleParameterCollection

OracleParameter 对象的集合

OracleType

Oracle 数据类型和结构的枚举

执行存储过程

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

在 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 安装中包含的一个示例

将 SystemDataOracleClientdll（用于 Oracle 的 Microsoft NET 框架数据提供程序）的引用添加到项目中

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

using SystemDataOracleClient;

创建一个 OracleConnection 对象

OracleConnection conn = new OracleConnection(Data Source=oracledb;

User Id=UserID;Password=Password;);

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

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

OracleCommand cmd = new OracleCommand();

cmdConnection = conn;

cmdCommandText = COUNT_JOB_HISTORY;

cmdCommandType = CommandTypeStoredProcedure;

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

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

cmdParametersAdd(reccount OracleTypeNumber)Direction =

ParameterDirectionOutput;

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

cmdParametersAdd(reccount OracleTypeNumber);

cmdParameters[reccount]Direction = ParameterDirectionOutput;

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

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

方法 说明

ExecuteReader

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

ExecuteNonQuery

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

ExecuteOracleNonQuery

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

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

ExecuteScalar

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

ExecuteOracleScalar

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

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

connOpen();

cmdExecuteNonQuery();

connClose();

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

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

ConsoleWriteLine(cmdParameters[reccount]Value);

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

OracleConnection conn = new OracleConnection(Data Source=oracledb;

User Id=UserID;Password=Password;);

OracleCommand cmd = new OracleCommand();

cmdConnection = conn;

cmdCommandText = COUNT_JOB_HISTORY;

cmdCommandType = CommandTypeStoredProcedure;

cmdParametersAdd(reccount OracleTypeNumber)Direction =

ParameterDirectionOutput;

connOpen();

cmdExecuteNonQuery();

connClose();

ConsoleWriteLine(cmdParameters[reccount]Value);

不返回数据的存储过程

OracleCommand 类的 ExecuteOracleNonQuery() 方法用于执行不返回任何行的 SQL 语句或存储过程该方法返回一个 int 值表示受 UPDATEINSERT 和 DELETE 命令影响的行数如果没有任何行受到影响则返回 如果您所执行的 INSERTDELETE 或 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();

cmdConnection = conn;

cmdCommandText = COUNT_JOB_HISTORY;

cmdCommandType = CommandTypeStoredProcedure;

// add the parameter specifying the employee for whom to delete records

cmdParametersAdd(p_employee_id OracleTypeNumber)Value = ;

OracleString rowId;

// execute the stored procedure

connOpen();

int rowsAffected = cmdExecuteNonQuery();

connClose();

ConsoleWriteLine(Rows affected: + rowsAffected);

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

Rows affected:

访问返回值

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

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

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

CREATE OR new FUNCTION GET_EMPLOYEE_EMAIL (

p_employee_id NUMBER

)

RETURN VARCHAR

IS p_email VARCHAR();

BEGIN

SELECT EMAIL INTO p_email FROM EMPLOYEES

WHERE EMPLOYEE_ID = p_employee_id;

RETURN p_email;

END GET_EMPLOYEE_EMAIL;

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

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

cmdConnection = conn;

cmdCommandText = GET_EMPLOYEE_EMAIL;

cmdCommandType = CommandTypeStoredProcedure;

// add the parameters including the return parameter to retrieve

// the return value

cmdParametersAdd(p_employee_id OracleTypeNumber)Value = ;

cmdParametersAdd(p_email OracleTypeVarChar )Direction =

ParameterDirectionReturnValue;

// execute the function

connOpen();

cmdExecuteNonQuery();

connClose();

// output the result

ConsoleWriteLine(Email address is: + cmdParameters[p_email]Value);

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

Email address is: NKOCHHAR

结果集与 REF CURSOR

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

包

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

· 规范定义包的名称并为包中的每个存储过程或函数提供方法签名（原型）规范头还定义所有全局声明规范的样式类似于 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 VARCHAR

p_postal_code IN VARCHAR

p_city IN VARCHAR

p_state_province IN VARCHAR

p_country_id IN CHAR);

PROCEDURE DeleteLocations (p_location_id IN NUMBER);

PROCEDURE InsertLocations (p_location_id OUT NUMBER

p_street_address IN VARCHAR

p_postal_code IN VARCHAR

p_city IN VARCHAR

p_state_province IN VARCHAR

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

cmdConnection = conn;

cmdCommandText = SELECT_JOB_HISTORYGetJobHistoryByEmployeeId;

cmdCommandType = CommandTypeStoredProcedure;

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

// to retrieve the result set

cmdParametersAdd(p_employee_id OracleTypeNumber)Value = ;

cmdParametersAdd(cur_JobHistory OracleTypeCursor)Direction =

ParameterDirectionOutput;

// open the connection and create the DataReader

connOpen();

OracleDataReader dr = cmdExecuteReader();

// output the results and close the connection

while(drRead())

{

for(int i = ; i <drFieldCount; i++)

ConsoleWrite(dr[i]ToString() + ;);

ConsoleWriteLine();

}

connClose();