本文讨论了如何使用 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 的电子邮件地址。
|||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();
新闻热点
疑难解答
