本文讨论了如何使用 ADONET 访问 Oracle 存储过程（称为 SQL 编程块）和函数（返回单个值的编程块）
您可以使用以下托管数据提供程序连接到 Oracle 数据库Microsoft NET 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)ISBEGIN 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)ISBEGIN DELETE FROM job_history WHERE employee_id = p_employee_id;END DELETE_JOB_HISTORY;
以下代码运行了该存储过程
// create the connectionOracleConnection conn = new OracleConnection(Data Source=oracledb; User Id=UserID;Password=Password;);// create the command for the stored procedureOracleCommand cmd = new OracleCommand();cmdConnection = conn;cmdCommandText = COUNT_JOB_HISTORY;cmdCommandType = CommandTypeStoredProcedure;// add the parameter specifying the employee for whom to delete recordscmdParametersAdd(p_employee_id OracleTypeNumber)Value = ;OracleString rowId;// execute the stored procedureconnOpen();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 VARCHARIS 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 connectionOracleConnection conn = new OracleConnection(Data Source=oracledb; User Id=UserID;Password=Password;);// create the command for the functionOracleCommand cmd = new OracleCommand();cmdConnection = conn;cmdCommandText = GET_EMPLOYEE_EMAIL;cmdCommandType = CommandTypeStoredProcedure;// add the parameters including the return parameter to retrieve// the return valuecmdParametersAdd(p_employee_id OracleTypeNumber)Value = ;cmdParametersAdd(p_email OracleTypeVarChar )Direction = ParameterDirectionReturnValue;// execute the functionconnOpen();cmdExecuteNonQuery();connClose();// output the resultConsoleWriteLine(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 ASTYPE 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 ASPROCEDURE GetLocations (cur_Locations OUT T_CURSOR)ISBEGIN OPEN cur_Locations FOR SELECT * FROM LOCATIONS;END GetLocations; Implementation of other procedures ommittedEND CRUD_LOCATIONS;
使用 DataReader
可以通过调用 OracleCommand 对象的 ExecuteReader() 方法来创建 OracleDataReader本节说明如何使用 DataReader 来访问由存储过程 SELECT_JOB_HISTORY 返回的结果集以下为包规范
CREATE OR new PACKAGE SELECT_JOB_HISTORY ASTYPE 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 ASPROCEDURE GetJobHistoryByEmployeeId( p_employee_id IN NUMBER cur_JobHistory OUT T_CURSOR)ISBEGIN OPEN cur_JobHistory FOR SELECT * FROM JOB_HISTORY WHERE employee_id = p_employee_id;END GetJobHistoryByEmployeeId;END SELECT_JOB_HISTORY;
以下代码执行该过程根据结果集创建 DataReader并将 DataReader 的内容输出到控制台
// create connectionOracleConnection conn = new OracleConnection(Data Source=oracledb; User Id=UserID;Password=Password;);// create the command for the stored procedureOracleCommand 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 setcmdParametersAdd(p_employee_id OracleTypeNumber)Value = ;cmdParametersAdd(cur_JobHistory OracleTypeCursor)Direction = ParameterDirectionOutput;// open the connection and create the DataReaderconnOpen();OracleDataReader dr = cmdExecuteReader();// output the results and close the connectionwhile(drRead()){ for(int i = ; i < drFieldCount; i++) ConsoleWrite(dr[i]ToString() + ;); ConsoleWriteLine();}connClose();
对于 HR 架构的默认安装控制台输出显示了员工 的两个记录中每个记录的字段（用分号分隔）
;// :: AM;// :: AM;AC_ACCOUNT;;;// :: AM;// :: AM;AC_MGR;;
上述代码显示包中的过程是使用包名称 (ELECT_JOB_HISTORY) 和过程的名称（在此情况下为 GetJobHistoryByEmployeeId）指定的二者之间用句点分隔
代码还说明了如何定义结果集的 REF CURSOR 参数请注意数据类型为 OracleTypeCursor方向为 ParameterDirectionOutput
还请注意在访问 REF CURSOR 中的结果集的整个过程中连接都保持打开状态
如果包返回多个游标则 DataReader 会按照您向参数集合中添加它们的顺序来访问这些游标而不是按照它们在过程中出现的顺序来访问可使用 DataReader 的 NextResult() 方法前进到下一个游标
返回单个值的存储过程
OracleCommand 类的 ExecuteOracleScalar() 方法用于执行将单个值作为 OracleType 数据类型返回的 SQL 语句或存储过程如果命令返回一个结果集则该方法会返回第一行第一列的值如果返回了 REF CURSOR而不是返回了 REF CURSOR 所指向的第一行第一列的值则该方法会返回一个空引用OracleCommand 类的 ExecuteScalar() 方法类似于 ExecuteOracleScalar() 方法只不过它将值作为 NET 框架数据类型返回
尽管如此在使用 Oracle 存储过程时这两个方法都没有用Oracle 存储过程不能将值作为 RETURN 语句的一部分返回而只能将其作为 OUT 参数返回有关信息请参阅不返回数据的存储过程一节同时除了通过 REF CURSOR 输出参数以外您不能返回结果集下一节将对此进行讨论
您只能使用 RETURN 参数检索 Oracle 函数的返回值（如上一节所述）而不能使用 ExecuteScalar 方法之一进行检索
序列
Oracle 使用序列 来生成唯一编号而不是使用 SQL Server 所用的数据类型 uniqueidentifier无论是哪种情况主要用途都是为主键列生成一系列唯一编号与 uniqueidentifier 数据类型不同序列是与将其用于主键值的一个或多个表无关的数据库对象
Oracle 序列是原子对象并且是一致的也就是说一旦您访问一个序列号Oracle 将在处理下一个请求之前自动递增下一个编号从而确保不会出现重复值
可以使用 CREATE SEQUENCE 命令创建 Oracle 序列该命令所带参数包括增量起始值最大值循环和缓存可使用 NEXTVAL 和 CURRVAL 关键字访问序列值NEXTVAL 返回序列中的下一个编号而 CURRVAL 提供对当前值的访问HR 架构中的序列 LOCATIONS_SEQ 按如下方式定义
CREATE SEQUENCE LOCATIONS_SEQ INCREMENT BY START WITH MAXVALUE MINVALUE NOCYCLE NOCACHE NOORDER
大多数序列代码是不言自明的NOCYCLE 表示序列在达到最小值或最大值后将不再生成其他值NOCACHE 表示序列值在被请求之前不会进行分配可使用预分配机制来改善性能NOORDER 表示在生成编号时不能保证按照请求编号的顺序返回这些编号
下面的代码显示了一个存储过程该过程请求一个序列值在向 LOCATIONS 表中插入记录时使用它设置主键值然后在 OUT 参数中返回该主键值
CREATE OR new PROCEDURE ADD_LOCATION ( 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)ASBEGIN INSERT INTO LOCATIONS ( LOCATION_ID STREET_ADDRESS POSTAL_CODE CITY STATE_PROVINCE COUNTRY_ID) VALUES ( LOCATIONS_SEQNEXTVAL p_street_address p_postal_code p_city p_state_province p_country_id ); SELECT LOCATIONS_SEQCURRVAL INTO p_location_id FROM DUAL;END ADD_LOCATION;
下面的代码调用该存储过程以插入一个记录并检索返回的序列值
// create the connectionOracleConnection conn = new OracleConnection(Data Source=oracledb; User Id=UserID;Password=Password;);// create the command for the stored procedureOracleCommand cmd = new OracleCommand();cmdConnection = conn;cmdCommandText = ADD_LOCATION;cmdCommandType = CommandTypeStoredProcedure;// add the parameters for the stored procedure including the LOCATION_ID// sequence value that is returned in the output parameter p_location_idcmdParametersAdd(p_location_id OracleTypeNumber)Direction = ParameterDirectionOutput;cmdParametersAdd(p_street_address OracleTypeVarChar)Value = Any Street;cmdParametersAdd(p_postal_code OracleTypeVarChar)Value = ;cmdParametersAdd(p_city OracleTypeVarChar)Value = Key West;cmdParametersAdd(p_state_province OracleTypeVarChar)Value = FL;cmdParametersAdd(p_country_id OracleTypeVarChar)Value = US;// execute the command to add the records OracleString rowId;connOpen();int rowsAffected = cmdExecuteOracleNonQuery(out rowId);connClose();// output the resultsConsoleWriteLine(Rows affected: + rowsAffected);ConsoleWriteLine(Location ID: + cmdParameters[p_location_id]Value);
控制台显示一个记录被插入到该表中同时还插入了该序列生成的主键值
Rows affected: Location ID:
使用 DataAdapter 填充数据集
可使用 REF CURSOR 通过 DataAdapter 来填充 DataSet下面的代码利用了使用 DataReader 一节中定义的存储过程 GetJobHistoryByEmployeeId并用它在 REF CURSOR 输出参数中返回的结果集来填充 DataSet
以下是使用 DataAdapter 填充 DataSet 的代码
// create the connectionOracleConnection conn = new OracleConnection(Data Source=oracledb; User Id=UserID;Password=Password;);// create the command for the stored procedureOracleCommand 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 setcmdParametersAdd(p_employee_id OracleTypeNumber)Value = ;cmdParametersAdd(cur_JobHistory OracleTypeCursor)Direction = ParameterDirectionOutput;// createt the DataAdapter from the command and use it to fill the// DataSetOracleDataAdapter da = new OracleDataAdapter(cmd);DataSet ds = new DataSet();daFill(ds);// output the resultsConsoleWriteLine(dsTables[]RowsCount);
对于 HR 架构的默认安装输出表明员工 有两个 JOB_HISTORY 记录
使用 DataAdapter 更新 Oracle
当您使用 REF CURSOR 参数填充 DataSet 时不能简单地使用 OracleDataAdapter 的 Update() 方法这是因为在执行存储过程时Oracle 不能提供确定表名和列名所需的信息要使用 DataAdapter 的 Update() 方法您必须创建在基础表中更新插入和删除记录的过程该方法类似于在 SQL Server 中使用的方法
本节说明如何生成一个可以处理所需的创建检索更新和删除操作的包以便能够从 Oracle 数据库中检索 LOCATION 数据也能够将对 DataSet 数据所做的不连续更改重新更新到 Oracle 数据库包头如下所示
CREATE OR new PACKAGE CRUD_LOCATIONS ASTYPE 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;
包正文如下所示
CREATE OR new PACKAGE BODY CRUD_LOCATIONS AS retrieve all LOCATION recordsPROCEDURE GetLocations (cur_Locations OUT T_CURSOR)ISBEGIN OPEN cur_Locations FOR SELECT * FROM LOCATIONS;END GetLocations; update a LOCATION recordPROCEDURE 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)ISBEGIN UPDATE LOCATIONS SET STREET_ADDRESS = p_street_address POSTAL_CODE = p_postal_code CITY = p_city STATE_PROVINCE = p_state_province COUNTRY_ID = p_country_id WHERE LOCATION_ID = p_location_id; END UpdateLocations; delete a LOCATION recordPROCEDURE DeleteLocations (p_location_id IN NUMBER)ISBEGIN DELETE FROM LOCATIONS WHERE LOCATION_ID = p_location_id;END DeleteLocations; insert a LOCATION recordPROCEDURE 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)ASBEGIN INSERT INTO LOCATIONS ( LOCATION_ID STREET_ADDRESS POSTAL_CODE CITY STATE_PROVINCE COUNTRY_ID) VALUES ( LOCATIONS_SEQNEXTVAL p_street_address p_postal_code p_city p_state_province p_country_id ); SELECT LOCATIONS_SEQCURRVAL INTO p_location_id FROM DUAL;END InsertLocations;END CRUD_LOCATIONS;
下面的代码定义了一个 DataAdapter从而使用上述包中定义的过程来创建检索更新和删除支持 DataAdapter 的数据DataAdapter 既可用来将数据检索到 DataSet 中也可用来将对 DataSet 所做的更改更新到 Oracle 数据库中
// define the connection stringString connString = Data Source=oracledb;User Id=UserID;Password=Password;;// create the data adapterOracleDataAdapter da = new OracleDataAdapter();// define the select command for the data adapterOracleCommand selectCommand = new OracleCommand(CRUD_LOCATIONSGetLocations new OracleConnection(connString));selectCommandCommandType = CommandTypeStoredProcedure;selectCommandParametersAdd(cur_Locations OracleTypeCursor)Direction = ParameterDirectionOutput;daSelectCommand = selectCommand;// define the udpate command for the data adapterOracleCommand updateCommand = new OracleCommand(CRUD_LOCATIONSUpdateLocations new OracleConnection(connString));updateCommandCommandType = CommandTypeStoredProcedure;updateCommandParametersAdd(p_location_id OracleTypeNumber LOCATION_ID);updateCommandParametersAdd(p_street_address OracleTypeVarChar STREET_ADDRESS);updateCommandParametersAdd(p_postal_code OracleTypeVarChar POSTAL_CODE);updateCommandParametersAdd(p_city OracleTypeVarChar CITY);updateCommandParametersAdd(p_state_province OracleTypeVarChar STATE_PROVINCE);updateCommandParametersAdd(p_country_id OracleTypeChar COUNTRY_ID);daUpdateCommand = updateCommand;// define the delete command for the data adapterOracleCommand deleteCommand = new OracleCommand(CRUD_LOCATIONSDeleteLocations new OracleConnection(connString));deleteCommandCommandType = CommandTypeStoredProcedure;deleteCommandParametersAdd(p_location_id OracleTypeNumber LOCATION_ID);daDeleteCommand = deleteCommand;OracleCommand insertCommand = new OracleCommand(CRUD_LOCATIONSInsertLocations new OracleConnection(connString));insertCommandCommandType = CommandTypeStoredProcedure;insertCommandParametersAdd(p_location_id OracleTypeNumber LOCATION_ID);insertCommandParametersAdd(p_street_address OracleTypeVarChar STREET_ADDRESS);insertCommandParametersAdd(p_postal_code OracleTypeVarChar POSTAL_CODE);insertCommandParametersAdd(p_city OracleTypeVarChar CITY);insertCommandParametersAdd(p_state_province OracleTypeVarChar STATE_PROVINCE);insertCommandParametersAdd(p_country_id OracleTypeChar COUNTRY_ID);daInsertCommand = insertCommand;// define a DataTable and fill it using the data adapterDataTable dt = new DataTable();daFill(dt);// do work that adds edits updates or deletes records in the table// call the Update() method of the data adapter to update the Oracle// database with changes made to the datadaUpdate(dt);
使用多个结果集
Oracle 不支持批量查询因此无法从一个命令返回多个结果集使用存储过程时返回多个结果集类似于返回单个结果集必须使用 REF CURSOR 输出参数要返回多个结果集请使用多个 REF CURSOR 输出参数
以下是返回两个结果集（全部 EMPLOYEES 和 JOBS 记录）的包规范
CREATE OR new PACKAGE SELECT_EMPLOYEES_JOBS ASTYPE T_CURSOR IS REF CURSOR;PROCEDURE GetEmployeesAndJobs ( cur_Employees OUT T_CURSOR cur_Jobs OUT T_CURSOR);END SELECT_EMPLOYEES_JOBS;包正文如下所示CREATE OR new PACKAGE BODY SELECT_EMPLOYEES_JOBS ASPROCEDURE GetEmployeesAndJobs( cur_Employees OUT T_CURSOR cur_Jobs OUT T_CURSOR)ISBEGIN return all EMPLOYEES records OPEN cur_Employees FOR SELECT * FROM Employees; return all JOBS records OPEN cur_Jobs FOR SELECT * FROM Jobs;END GetEmployeesAndJobs;END SELECT_EMPLOYEES_JOBS;
以下代码显示了如何使用从上述包中返回的两个结果集来填充 DataSet 中的两个相关表
// create the connectionOracleConnection conn = new OracleConnection(Data Source=oracledb; User Id=UserID;Password=Password;);// define the command for the stored procedureOracleCommand cmd = new OracleCommand();cmdConnection = conn;cmdCommandText = SELECT_EMPLOYEES_JOBSGetEmployeesAndJobs;// add the parameters including the two REF CURSOR types to retrieve// the two result setscmdParametersAdd(cur_Employees OracleTypeCursor)Direction = ParameterDirectionOutput;cmdParametersAdd(cur_Jobs OracleTypeCursor)Direction = ParameterDirectionOutput;cmdCommandType = CommandTypeStoredProcedure;// create the DataAdapter and map tablesOracleDataAdapter da = new OracleDataAdapter(cmd);daTableMappingsAdd(Table EMPLOYEES);daTableMappingsAdd(Table JOBS);// create and fill the DataSetDataSet ds = new DataSet();daFill(ds);// create a relationdsRelationsAdd(EMPLOYEES_JOBS_RELATION dsTables[JOBS]Columns[JOB_ID] dsTables[EMPLOYEES]Columns[JOB_ID]);// output the second employee (zerobased array) and job title// based on the relationConsoleWriteLine(Employee ID: + dsTables[EMPLOYEES]Rows[][EMPLOYEE_ID] + ; Job Title: + dsTables[EMPLOYEES]Rows[]GetParentRow( EMPLOYEES_JOBS_RELATION)[JOB_TITLE]);
控制台输出显示了第二个员工的职务
Employee ID: ; Job Title: Administration Vice President
小结
通过 Oracle NET 数据提供程序可以方便地执行存储过程以及访问返回值（无论返回值是一个还是多个标量值或结果集）可以将 Oracle 过程与 OracleDataAdapter 结合使用从而填充 DataSet处理不连续的数据以及以后将更改更新到 Oracle 数据库
Oracle 过程与 Microsoft SQL Server 存储过程之间的主要区别是Oracle 过程必须将值作为输出参数返回并且必须使用输出参数将结果集作为 REF CURSOR 对象返回给调用程序
