![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
在.NET中调用Oracle9i存储过程经验总结
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
在.NET中调用Oracle9i存储过程可以用多个数据提供者,比如OralceClient,OleDb。本文将用OralceClient为例。.NET语言用C#。
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
一、调用一个带输入、输出参数的存储过程
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
首先,在Oralce中创建存储过程如下:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
create or replace procedure GetRecords(name_out
out varchar2,age_in
in varchar2)
as
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
begin
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
select NAME into name_out from test where AGE = age_in;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
end;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
然后,在C#项目中作如下调用:
string connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";
string queryString = "getrecords";
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
OracleConnection cn =
new OracleConnection(connectionString);
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
OracleCommand cmd =
new OracleCommand(queryString,cn);
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
cmd.CommandType = CommandType.StoredProcedure;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
cmd.Parameters.Add("name_out",OracleType.VarChar,20);
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
cmd.Parameters["name_out"].Direction = ParameterDirection.Output;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
cmd.Parameters.Add("age_in",21);
try
{
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
cn.Open();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
cmd.ExecuteNonQuery();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("Name is:{0}",cmd.Parameters["name_out"].Value.ToString());
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
cn.Close();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
}
catch( OracleException ex )
{
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("Exception occurred!");
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("The exception message is:{0}",ex.Message.ToString());
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
}
finally
{
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("------------------End-------------------");
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
}
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
小结:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
以上是很平常的调用方法,但是我在做这个示例程序的时候,却不是那么一帆风顺。这里要指出OracleClient的一个不尽如人意之处,就是对于参数名称,必须和存储过程定义中的参数同名,否则就会报错。比如将代码中的“name_out”改为别的名称的话,就会报异常。但我试着用OleDb代替OralceClient,却没有这个问题。不知道在新版本的数据提供程序中会否改进这一点?
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
二、调用不返回数据的存储过程
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
首先,在Oralce中创建存储过程如下:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
create or replace procedure insertRecord(UserID
in varchar2,
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
UserName
in varchar2,
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
UserAge
in varchar2)
is
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
begin
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
insert into test values (UserID, UserName, UserAge);
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
end;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
然后,在C#项目中作如下调用:
string connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";
string queryString = "insertRecord";
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
OracleConnection cn =
new OracleConnection(connectionString);
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
OracleCommand cmd =
new OracleCommand(queryString,cn);
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
cmd.CommandType = CommandType.StoredProcedure;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
cmd.Parameters.Add("UserID","007");
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
cmd.Parameters.Add("UserName","Dell");
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
cmd.Parameters.Add("UserAge","40");
try
{
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
cn.Open();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
cmd.ExecuteNonQuery();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("Record inserted!");
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
cn.Close();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
}
catch( OracleException ex )
{
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("Exception occurred!");
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("The exception message is:{0}",ex.Message.ToString());
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
}
finally
{
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("------------------End-------------------");
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
}
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
小结:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
不返回数据的存储过程一般有Delete, Insert, Update等。虽然它们的SQL语句不同,但是调用方面都是一样的。只要传入正确的参数,一般不会出什么问题。不过还是要注意,在使用OralceClient作为数据提供者的时候,参数名称一定要和存储过程定义中的一致!至于参数添加的顺序倒是无所谓的,因为有参数名称作为区别。
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
三、IDENTITY 和 SEQUENCE
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
Sql Server中,定义一个列为递增列很容易,但我在Oracle中却怎么也找不到设置的方法。不过查了点资料后知道Oracle中有个叫Sequence的对象,产生一个唯一的序列号,类似于Sql Server中的IDENTITY。于是,我做了如下实验:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
首先,在Oracle中创建了一个名为TEST_SEQ的Sequence对象,SQL语句如下:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
create sequence TEST_SEQ
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
minvalue 100
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
maxvalue 999
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
start with 102
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
increment by 1
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
nocache;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
语法应该是比较易懂的,最小最大值分别用minvalue,maxvalue表示,初始值是102(这个数字是动态变化的,我创建的时候设的是100,后因插入了2条数据后就自动增加了2),increment当然就是步长了。在PL/SQL中可以用test_seq.nextval访问下一个序列号,用test_seq.currval访问当前的序列号。
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
定义完了Sequence,接下来就是创建一个存储过程InsertRecordWithSequence:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
--这次我修改了test表的定义,和前面的示例不同。其中,UserID是PK。
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
create or replace procedure InsertRecordWithSequence(UserID
out number,
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
UserName
in varchar2,
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
UserAge
in number)
is
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
begin
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
insert into test(id, name, age) --插入一条记录,PK值从Sequece获取
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
values(test_seq.nextval, UserName, UserAge);
/**/
/*返回PK值。注意Dual表的用法*/
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
select test_seq.currval into UserID from dual;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
end InsertRecordWithSequence;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
接下来,就是在C#中进行调用了:
string connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";
string queryString = "InsertRecordWithSequence";
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
OracleConnection cn =
new OracleConnection(connectionString);
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
OracleCommand cmd =
new OracleCommand(queryString,cn);
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
cmd.CommandType = CommandType.StoredProcedure;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
cmd.Parameters.Add("UserID",OracleType.Number);
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
cmd.Parameters["UserID"].Direction = ParameterDirection.Output;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
cmd.Parameters.Add("UserName","Jack");
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
cmd.Parameters.Add("UserAge",40);
try
{
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
cn.Open();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
int rowAffected = cmd.ExecuteNonQuery();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("{0}行已插入。",rowAffected);
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("插入行的ID为:{0}",cmd.Parameters["UserID"].Value.ToString());
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
cn.Close();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
}
catch( OracleException ex )
{
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("Exception occurred!");
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("The exception message is:{0}",ex.Message.ToString());
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
}
finally
{
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("------------------End-------------------");
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
}
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
小结:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
使用Sequece对象可以很容易地创建唯一序列,在存储过程中的调用也十分方便,只要sequence_name.nextval以及sequence.currval就能得到下一个以及当前的序列值。倒是Dual表值得注意。
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
四、使用DataReader读取返回的结果集
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
为了让存储过程返回结果集,必须定义一个游标变量作为输出参数。这和Sql Server中有着很大的不同!并且还要用到Oracle中“包”(Package)的概念,似乎有点繁琐,但熟悉后也会觉得很方便。
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
关于“包”的概念,有很多内容可以参考,在此就不赘述了。首先,我创建了一个名为TestPackage的包,包头是这么定义的:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
create or replace package TestPackage
is
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
type mycursor
is
ref cursor; -- 定义游标变量
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
procedure GetRecords(ret_cursor
out mycursor); -- 定义过程,用游标变量作为返回参数
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
end TestPackage;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
包体是这么定义的:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
create or replace package body TestPackage
is
/**/
/*过程体*/
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
procedure GetRecords(ret_cursor
out mycursor)
as
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
begin
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
open ret_cursor
for select * from test;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
end GetRecords;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
end TestPackage;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
数据库中调试方法:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
在包头文件中选择相映的过程,右键选择text,f9执行后,点value后面的
![dot.gif](https://www.cnblogs.com/Images/dot.gif?x-oss-process=image/resize,w_1400/format,webp)
查看数据集的结果。
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
已经万事具备了,让我们前台调用试试:
string connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";
string queryString = "TestPackage.GetRecords";
//
注意调用方法
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
OracleConnection cn =
new OracleConnection(connectionString);
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
OracleCommand cmd =
new OracleCommand(queryString,cn);
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
cmd.CommandType = CommandType.StoredProcedure;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
cmd.Parameters.Add("ret_cursor",OracleType.Cursor);
//
注意这里的类型
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
cmd.Parameters["ret_cursor"].Direction = ParameterDirection.Output;
try
{
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
cn.Open();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
OracleDataReader dr = cmd.ExecuteReader();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
int i = 1;
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
while( dr.Read() )
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![ExpandedSubBlockStart.gif](http://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif?x-oss-process=image/resize,w_1400/format,webp)
![ContractedSubBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
{
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("Record {0}:",i++);
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("ID:{0} Name:{1} Age:{2}",
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
dr.GetOracleNumber(0),
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
dr.GetOracleString(1),
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
dr.GetOracleNumber(2));
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
}
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
dr.Close(); //用完DataReader对象要记得及时关闭
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
cn.Close(); //DataReader对象未关闭之前,不能关闭连接
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
}
catch( OracleException ex )
{
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("Exception occurred!");
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("The exception message is:{0}",ex.Message.ToString());
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
}
finally
{
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("------------------End-------------------");
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
}
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
请看结果:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
Record 1:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
ID:100 Name:Tony Age:23
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
Record 2:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
ID:101 Name:Jack Age:40
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
------------------End-------------------
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
小结:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
包是Oracle特有的概念,Sql Server中找不到相匹配的东西。在我看来,包有点像VC++的类,包头就是.h文件,包体就是.cpp文件。包头只负责定义,包体则负责具体实现。如果包返回多个游标,则DataReader会按照您向参数集合中添加它们的顺序来访问这些游标,而不是按照它们在过程中出现的顺序来访问。可使用DataReader的NextResult()方法前进到下一个游标。
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
五、用返回的结果集填充DataSet
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
ADO.NET舍弃了原来ADO中RecordSet的概念,而使用全新的DataSet来替代。DataSet可以提供更加强大的功能!有了前面的基础,利用ADO.NET从Oralce返回DataSet也是非常简单的。主要思想就是用DataAdapter接收返回的游标,再用DataAdapter的Fill()方法填充数据集。在下面的例子中,我仍用了前一个例子中建好的TestPackage包。
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
下面是前台调用主要代码:
string connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";
string queryString = "TestPackage.GetRecords";
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
OracleConnection cn =
new OracleConnection(connectionString);
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
OracleCommand cmd =
new OracleCommand(queryString,cn);
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
cmd.CommandType = CommandType.StoredProcedure;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
cmd.Parameters.Add("ret_cursor",OracleType.Cursor);
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
cmd.Parameters["ret_cursor"].Direction = ParameterDirection.Output;
try
{
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
cn.Open();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
OracleDataAdapter da = new OracleDataAdapter(cmd);
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
DataSet ds = new DataSet();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
da.Fill(ds,"TestTable");
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
cn.Close();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
for( int i = 0;i <= ds.Tables["TestTable"].Rows.Count-1;i++ )
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![ExpandedSubBlockStart.gif](http://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif?x-oss-process=image/resize,w_1400/format,webp)
![ContractedSubBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
{
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
string id = ds.Tables["TestTable"].Rows[i]["ID"].ToString();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
string name = ds.Tables["TestTable"].Rows[i]["NAME"].ToString();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
string age = ds.Tables["TestTable"].Rows[i]["AGE"].ToString();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("Record {0}:",i+1);
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("ID:{0}\tName:{1}\tAge:{2}\n",id,name,age);
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
}
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
}
catch( OracleException ex )
{
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("Exception occurred!");
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("The exception message is:{0}",ex.Message.ToString());
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
}
finally
{
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("------------------End-------------------");
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
}
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
小结:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
程序调用后的结果和刚才用DataReader调用的结果一样。这里只说明怎样利用ADO.NET调用Oracle存储过程,以及怎样填充至数据集中。至于怎样操纵DataSet,不是本文的讨论范围。有兴趣的读者可以参考MSDN以及相关书籍。
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
六、用DataAdapter更新数据库
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
通常用DataAdapter取回DataSet,将会对DataSet进行一些修改,继而更新数据库(如果只是为了获取数据,微软推荐使用DataReader代替DataSet)。然而,通过存储过程更新数据库,并不是那么简单,不能简单地通过DataAdapter的Update()方法进行更新。必须手动为DataAdapter添加InsertCommand, DeleteCommand, UpdateCommand,因为存储过程对这些操作的细节是不知情的,必须人为给出。
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
为了达成这个目标,我完善了之前的TestPackage包,包头如下:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
create or replace package TestPackage
is
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
type mycursor
is
ref cursor;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
procedure UpdateRecords(id_in
in number,newName
in varchar2,newAge
in number);
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
procedure SelectRecords(ret_cursor
out mycursor);
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
procedure DeleteRecords(id_in
in number);
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
procedure InsertRecords(name_in
in varchar2, age_in
in number);
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
end TestPackage;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
包体如下:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
create or replace package body TestPackage
is
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
procedure UpdateRecords(id_in
in number, newName
in varchar2, newAge
in number)
as
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
begin
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
update test
set age = newAge, name = newName where id = id_in;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
end UpdateRecords;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
procedure SelectRecords(ret_cursor
out mycursor)
as
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
begin
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
open ret_cursor
for select * from test;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
end SelectRecords;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
procedure DeleteRecords(id_in
in number)
as
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
begin
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
delete from test where id = id_in;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
end DeleteRecords;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
procedure InsertRecords(name_in
in varchar2, age_in
in number)
as
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
begin
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
insert into test values (test_seq.nextval, name_in, age_in);
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
--test_seq是一个已建的Sequence对象,请参照前面的示例
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
end InsertRecords;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
end TestPackage;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
前台调用代码如下,有点繁琐,请耐心阅读:
string connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";
string queryString = "TestPackage.SelectRecords";
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
OracleConnection cn =
new OracleConnection(connectionString);
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
OracleCommand cmd =
new OracleCommand(queryString,cn);
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
cmd.CommandType = CommandType.StoredProcedure;
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
cmd.Parameters.Add("ret_cursor",OracleType.Cursor);
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
cmd.Parameters["ret_cursor"].Direction = ParameterDirection.Output;
try
{
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
cn.Open();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
OracleDataAdapter da = new OracleDataAdapter(cmd);
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
DataSet ds = new DataSet();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
da.Fill(ds,"TestTable");
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
cn.Close();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
int count = ds.Tables["TestTable"].Rows.Count;
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![ExpandedSubBlockStart.gif](http://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif?x-oss-process=image/resize,w_1400/format,webp)
/**//*打印原始记录*/
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("Old Records is:");
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
for( int i = 0;i <= count - 1;i++ )
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![ExpandedSubBlockStart.gif](http://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif?x-oss-process=image/resize,w_1400/format,webp)
![ContractedSubBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
{
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
string id = ds.Tables["TestTable"].Rows[i]["ID"].ToString();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
string name = ds.Tables["TestTable"].Rows[i]["NAME"].ToString();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
string age = ds.Tables["TestTable"].Rows[i]["AGE"].ToString();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("Record {0}:",i+1);
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("ID:{0}\tName:{1}\tAge:{2}\n",id,name,age);
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
}
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
da.SelectCommand = cmd; //为DataAdapter指定SelectCommand
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
OracleCommand updateCmd = new OracleCommand("TestPackage.UpdateRecords",cn);
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
updateCmd.CommandType = CommandType.StoredProcedure;
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
updateCmd.Parameters.Add("id_in",OracleType.Number,3,"ID");
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
updateCmd.Parameters.Add("newName",OracleType.VarChar,20,"NAME");
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
updateCmd.Parameters.Add("newAge",OracleType.Number,3,"AGE");
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
da.UpdateCommand = updateCmd; //为DataAdapter指定UpdateCommand
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
OracleCommand deleteCmd = new OracleCommand("TestPackage.DeleteRecords",cn);
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
deleteCmd.CommandType = CommandType.StoredProcedure;
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
deleteCmd.Parameters.Add("id_in",OracleType.Number,3,"ID");
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
da.DeleteCommand = deleteCmd; //为DataAdapter指定DeleteCommand
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
OracleCommand insertCmd = new OracleCommand("TestPackage.InsertRecords",cn);
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
insertCmd.CommandType = CommandType.StoredProcedure;
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
insertCmd.Parameters.Add("name_in",OracleType.VarChar,20,"NAME");
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
insertCmd.Parameters.Add("age_in",OracleType.Number,3,"AGE");
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
da.InsertCommand = insertCmd; //为DataAdapter指定InsertCommand
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
DataTable newTable = ds.Tables["TestTable"];
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![ExpandedSubBlockStart.gif](http://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif?x-oss-process=image/resize,w_1400/format,webp)
/**//*修改第一条记录*/
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
newTable.Rows[0]["AGE"] = 22;
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
newTable.Rows[0]["NAME"] = "John";
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![ExpandedSubBlockStart.gif](http://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif?x-oss-process=image/resize,w_1400/format,webp)
/**//*删除一条记录*/
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
newTable.Rows[2].Delete();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![ExpandedSubBlockStart.gif](http://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif?x-oss-process=image/resize,w_1400/format,webp)
/**//*插入一条记录*/
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
DataRow newRow = newTable.NewRow();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
newRow["NAME"] = "Bob";
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
newRow["AGE"] = 99;
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
newTable.Rows.Add(newRow);
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
cn.Open();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
da.Update(newTable); //将更改更新到数据库
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
newTable.Clear(); //清空DataTable
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
da.Fill(newTable); //获取更改后的数据
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
cn.Close();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![ExpandedSubBlockStart.gif](http://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif?x-oss-process=image/resize,w_1400/format,webp)
/**//*打印新记录*/
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("New Records is:");
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
for( int i = 0;i <= newTable.Rows.Count - 1;i++ )
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![ExpandedSubBlockStart.gif](http://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif?x-oss-process=image/resize,w_1400/format,webp)
![ContractedSubBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
{
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
string id = newTable.Rows[i]["ID"].ToString();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
string name = newTable.Rows[i]["NAME"].ToString();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
string age = newTable.Rows[i]["AGE"].ToString();
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("Record {0}:",i+1);
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("ID:{0}\tName:{1}\tAge:{2}\n",id,name,age);
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
}
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
}
catch( OracleException ex )
{
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("Exception occurred!");
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("The exception message is:{0}",ex.Message.ToString());
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
}
finally
{
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
Console.WriteLine("------------------End-------------------");
![InBlock.gif](http://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif?x-oss-process=image/resize,w_1400/format,webp)
}
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
运行结果如下:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
Old Records
is:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
Record 1:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
ID:100 Name:Tony Age:23
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
Record 2:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
ID:101 Name:Jack Age:34
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
Record 3:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
ID:103 Name:newAdd Age:100
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
New Records
is:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
Record 1:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
ID:100 Name:John Age:22
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
Record 2:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
ID:101 Name:Jack Age:34
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
Record 3:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
ID:104 Name:Bob Age:99
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
------------------End-------------------
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
小结:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
使用Update()方法更新数据库是非常有用的,但是只局限于单个表,对于表连接的情况,还是直接用OracleCommand的ExcuteNonQuery()方法去执行更新操作比较方便。
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
七、本文总结:
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
在.NET中利用ADO.NET调用Oralce9i中的存储过程,函数,包等,可以提高效率,也能完成一些复杂的业务逻辑。然而调用Oracle存储过程和Sql Server中有着很大不同,最需要明确的一个思想就是:在Oracle的过程中,所有返回值必须以输出参数的形式出现。在Oracle中,要返回一个结果集,就必须把REF CURSOR作为返回参数,因此就要用到包。包类似于C++中的类,封装了一些全局变量、常量和函数,在函数中又可以有自己的私有变量。通过返回给应用程序的游标,应用程序就可以用DataReader或者DataAdapter接收它,进而做一些处理。还还可以用DataAdapter的Update()方法更新数据库,当然它需要你给出更新数据库的一些操作(为其定义处理过程)。
![None.gif](http://www.cnblogs.com/Images/OutliningIndicators/None.gif?x-oss-process=image/resize,w_1400/format,webp)
由于笔者是边学边写,以上示例不免有些不妥之处,请多多指教!