数据库

位置：IT落伍者 >> 数据库 >> 浏览文章

ADO.NET访问Oracle 9i存储过程(下)
发布日期：2022年03月29日
 
ADO.NET访问Oracle 9i存储过程(下)

对于 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

)

AS

BEGIN

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 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 = 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_id

cmdParametersAdd(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 results

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

// createt the DataAdapter from the command and use it to fill the

// DataSet

OracleDataAdapter da = new OracleDataAdapter(cmd);

DataSet ds = new DataSet();

daFill(ds);

// output the results

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

包正文如下所示

CREATE OR new PACKAGE BODY CRUD_LOCATIONS AS

retrieve all LOCATION records

PROCEDURE GetLocations (cur_Locations OUT T_CURSOR)

IS

BEGIN

OPEN cur_Locations FOR

SELECT * FROM LOCATIONS;

END GetLocations;

update a LOCATION record

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)

IS

BEGIN

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 record

PROCEDURE DeleteLocations (p_location_id IN NUMBER)

IS

BEGIN

DELETE FROM LOCATIONS

WHERE LOCATION_ID = p_location_id;

END DeleteLocations;

insert a LOCATION record

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

)

AS

BEGIN

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 string

String connString = Data Source=oracledb;User Id=UserID;Password=Password;;

// create the data adapter

OracleDataAdapter da = new OracleDataAdapter();

// define the select command for the data adapter

OracleCommand 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 adapter

OracleCommand 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 adapter

OracleCommand 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 adapter

DataTable 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 data

daUpdate(dt);

使用多个结果集

Oracle 不支持批量查询因此无法从一个命令返回多个结果集使用存储过程时返回多个结果集类似于返回单个结果集必须使用 REF CURSOR 输出参数要返回多个结果集请使用多个 REF CURSOR 输出参数

以下是返回两个结果集（全部 EMPLOYEES 和 JOBS 记录）的包规范

CREATE OR new PACKAGE SELECT_EMPLOYEES_JOBS AS

TYPE 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 AS

PROCEDURE GetEmployeesAndJobs

(

cur_Employees OUT T_CURSOR

cur_Jobs OUT T_CURSOR

)

IS

BEGIN

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 connection

OracleConnection conn = new OracleConnection(Data Source=oracledb;

User Id=UserID;Password=Password;);

// define the command for the stored procedure

OracleCommand cmd = new OracleCommand();

cmdConnection = conn;

cmdCommandText = SELECT_EMPLOYEES_JOBSGetEmployeesAndJobs;

// add the parameters including the two REF CURSOR types to retrieve

// the two result sets

cmdParametersAdd(cur_Employees OracleTypeCursor)Direction =

ParameterDirectionOutput;

cmdParametersAdd(cur_Jobs OracleTypeCursor)Direction =

ParameterDirectionOutput;

cmdCommandType = CommandTypeStoredProcedure;

// create the DataAdapter and map tables

OracleDataAdapter da = new OracleDataAdapter(cmd);

daTableMappingsAdd(Table EMPLOYEES);

daTableMappingsAdd(Table JOBS);

// create and fill the DataSet

DataSet ds = new DataSet();

daFill(ds);

// create a relation

dsRelationsAdd(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 relation

ConsoleWriteLine(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 过程与 MicrosoftSQL Server 存储过程之间的主要区别是Oracle 过程必须将值作为输出参数返回并且必须使用输出参数将结果集作为 REF CURSOR 对象返回给调用程序

               

上一篇：Linq To Sql常用方法使用总结

下一篇：关于使用basepage进行验证的问题