`
javatoyou
  • 浏览: 1008487 次
  • 性别: Icon_minigender_2
  • 来自: 北京
文章分类
社区版块
存档分类
最新评论

在.NET中调用Oracle9i存储过程经验总结

阅读更多

.NET中调用Oracle9i存储过程经验总结<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

.NET中调用Oracle9i存储过程可以用多个数据提供者,比如OralceClientOleDb。本文将用OralceClient为例。.NET语言用C#

一、调用一个带输入、输出参数的存储过程

首先,在Oralce中创建存储过程如下:

create or replace procedure GetRecords(name_out out varchar2,age_in in varchar2) as

begin

select NAME into name_out from test where AGE = age_in;

end;

然后,在C#项目中作如下调用:

string connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";

string queryString = "getrecords";

OracleConnection cn = new OracleConnection(connectionString);

OracleCommand cmd = new OracleCommand(queryString,cn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("name_out",OracleType.VarChar,20);

cmd.Parameters["name_out"].Direction = ParameterDirection.Output;

cmd.Parameters.Add("age_in",21);

try

{

cn.Open();

cmd.ExecuteNonQuery();

Console.WriteLine("Name is:{0}",cmd.Parameters["name_out"].Value.ToString());

cn.Close();

}

catch( OracleException ex )

{

Console.WriteLine("Exception occurred!");

Console.WriteLine("The exception message is:{0}",ex.Message.ToString());

}

finally

{

Console.WriteLine("------------------End-------------------");

}

小结:

以上是很平常的调用方法,但是我在做这个示例程序的时候,却不是那么一帆风顺。这里要指出OracleClient的一个不尽如人意之处,就是对于参数名称,必须和存储过程定义中的参数同名,否则就会报错。比如将代码中的“name_out”改为别的名称的话,就会报异常。但我试着用OleDb代替OralceClient,却没有这个问题。不知道在新版本的数据提供程序中会否改进这一点?

二、调用不返回数据的存储过程

首先,在Oralce中创建存储过程如下:

create or replace procedure insertRecord(UserID in varchar2,

UserName in varchar2,
UserAge in varchar2) is
begin
insert into
test values (UserID, UserName, UserAge);
end;

然后,在C#项目中作如下调用:

string connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";

string queryString = "insertRecord";

OracleConnection cn = new OracleConnection(connectionString);

OracleCommand cmd = new OracleCommand(queryString,cn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("UserID","007");

cmd.Parameters.Add("UserName","Dell");

cmd.Parameters.Add("UserAge","40");

try

{

cn.Open();

cmd.ExecuteNonQuery();

Console.WriteLine("Record inserted!");

cn.Close();

}

catch( OracleException ex )

{

Console.WriteLine("Exception occurred!");

Console.WriteLine("The exception message is:{0}",ex.Message.ToString());

}

finally

{

Console.WriteLine("------------------End-------------------");

}

小结:

不返回数据的存储过程一般有Delete, Insert, Update等。虽然它们的SQL语句不同,但是调用方面都是一样的。只要传入正确的参数,一般不会出什么问题。不过还是要注意,在使用OralceClient作为数据提供者的时候,参数名称一定要和存储过程定义中的一致!至于参数添加的顺序倒是无所谓的,因为有参数名称作为区别。

三、IDENTITY SEQUENCE

Sql Server中,定义一个列为递增列很容易,但我在Oracle中却怎么也找不到设置的方法。不过查了点资料后知道Oracle中有个叫Sequence的对象,产生一个唯一的序列号,类似于Sql Server中的IDENTITY。于是,我做了如下实验:

首先,在Oracle中创建了一个名为TEST_SEQSequence对象,SQL语句如下:

create sequence TEST_SEQ

minvalue 100

maxvalue 999

start with 102

increment by 1

nocache;

语法应该是比较易懂的,最小最大值分别用minvalue,maxvalue表示,初始值是102(这个数字是动态变化的,我创建的时候设的是100,后因插入了2条数据后就自动增加了2),increment当然就是步长了。在PL/SQL中可以用test_seq.nextval访问下一个序列号,用test_seq.currval访问当前的序列号。

定义完了Sequence,接下来就是创建一个存储过程InsertRecordWithSequence

--这次我修改了test表的定义,和前面的示例不同。其中,UserIDPK

create or replace procedure InsertRecordWithSequence(UserID out number,

UserName in varchar2,

UserAge in number)

is

begin

insert into test(id, name, age) --插入一条记录,PK值从Sequece获取

values(test_seq.nextval, UserName, UserAge);

/*返回PK值。注意Dual表的用法*/

select test_seq.currval into UserID from dual;

end InsertRecordWithSequence;

接下来,就是在C#中进行调用了:

string connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";

string queryString = "InsertRecordWithSequence";

OracleConnection cn = new OracleConnection(connectionString);

OracleCommand cmd = new OracleCommand(queryString,cn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("UserID",OracleType.Number);

cmd.Parameters["UserID"].Direction = ParameterDirection.Output;

cmd.Parameters.Add("UserName","Jack");

cmd.Parameters.Add("UserAge",40);

try

{

cn.Open();

int rowAffected = cmd.ExecuteNonQuery();

Console.WriteLine("{0}行已插入。",rowAffected);

Console.WriteLine("插入行的ID为:{0}",cmd.Parameters["UserID"].Value.ToString());

cn.Close();

}

catch( OracleException ex )

{

Console.WriteLine("Exception occurred!");

Console.WriteLine("The exception message is:{0}",ex.Message.ToString());

}

finally

{

Console.WriteLine("------------------End-------------------");

}

小结:

使用Sequece对象可以很容易地创建唯一序列,在存储过程中的调用也十分方便,只要sequence_name.nextval以及sequence.currval就能得到下一个以及当前的序列值。倒是Dual表值得注意。

四、使用DataReader读取返回的结果集

为了让存储过程返回结果集,必须定义一个游标变量作为输出参数。这和Sql Server中有着很大的不同!并且还要用到Oracle中“包”(Package)的概念,似乎有点繁琐,但熟悉后也会觉得很方便。

关于“包”的概念,有很多内容可以参考,在此就不赘述了。首先,我创建了一个名为TestPackage的包,包头是这么定义的:

create or replace package TestPackage is

type mycursor is ref cursor; -- 定义游标变量

procedure GetRecords(ret_cursor out mycursor); -- 定义过程,用游标变量作为返回参数

end TestPackage;

包体是这么定义的:

create or replace package body TestPackage is

/*过程体*/

procedure GetRecords(ret_cursor out mycursor) as

begin

open ret_cursor for select * from test;

end GetRecords;

end TestPackage;

已经万事具备了,让我们前台调用试试:

string connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";

string queryString = "TestPackage.GetRecords"; //注意调用方法

OracleConnection cn = new OracleConnection(connectionString);

OracleCommand cmd = new OracleCommand(queryString,cn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("ret_cursor",OracleType.Cursor); //注意这里的类型

cmd.Parameters["ret_cursor"].Direction = ParameterDirection.Output;

try

{

cn.Open();

OracleDataReader dr = cmd.ExecuteReader();

int i = 1;

while( dr.Read() )

{

Console.WriteLine("Record {0}:",i++);

Console.WriteLine("ID:{0} Name:{1} Age:{2}",

dr.GetOracleNumber(0),

dr.GetOracleString(1),

dr.GetOracleNumber(2));

Console.WriteLine();

}

dr.Close(); //用完DataReader对象要记得及时关闭

cn.Close(); //DataReader对象未关闭之前,不能关闭连接

}

catch( OracleException ex )

{

Console.WriteLine("Exception occurred!");

Console.WriteLine("The exception message is:{0}",ex.Message.ToString());

}

finally

{

Console.WriteLine("------------------End-------------------");

}

请看结果:

Record 1:

ID:100 Name:Tony Age:23

Record 2:

ID:101 Name:Jack Age:40

------------------End-------------------

小结:

包是Oracle特有的概念,Sql Server中找不到相匹配的东西。在我看来,包有点像VC++的类,包头就是.h文件,包体就是.cpp文件。包头只负责定义,包体则负责具体实现。如果包返回多个游标,则DataReader会按照您向参数集合中添加它们的顺序来访问这些游标,而不是按照它们在过程中出现的顺序来访问。可使用DataReaderNextResult()方法前进到下一个游标。

五、用返回的结果集填充DataSet

ADO.NET舍弃了原来ADORecordSet的概念,而使用全新的DataSet来替代。DataSet可以提供更加强大的功能!有了前面的基础,利用ADO.NETOralce返回DataSet也是非常简单的。主要思想就是用DataAdapter接收返回的游标,再用DataAdapterFill()方法填充数据集。在下面的例子中,我仍用了前一个例子中建好的TestPackage包。

下面是前台调用主要代码:

string connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";

string queryString = "TestPackage.GetRecords";

OracleConnection cn = new OracleConnection(connectionString);

OracleCommand cmd = new OracleCommand(queryString,cn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("ret_cursor",OracleType.Cursor);

cmd.Parameters["ret_cursor"].Direction = ParameterDirection.Output;

try

{

cn.Open();

OracleDataAdapter da = new OracleDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds,"TestTable");

cn.Close();

for( int i = 0;i <= ds.Tables["TestTable"].Rows.Count-1;i++ )

{

string id = ds.Tables["TestTable"].Rows[i]["ID"].ToString();

string name = ds.Tables["TestTable"].Rows[i]["NAME"].ToString();

string age = ds.Tables["TestTable"].Rows[i]["AGE"].ToString();

Console.WriteLine("Record {0}:",i+1);

Console.WriteLine("ID:{0}\tName:{1}\tAge:{2}\n",id,name,age);

}

}

catch( OracleException ex )

{

Console.WriteLine("Exception occurred!");

Console.WriteLine("The exception message is:{0}",ex.Message.ToString());

}

finally

{

Console.WriteLine("------------------End-------------------");

}

小结:

程序调用后的结果和刚才用DataReader调用的结果一样。这里只说明怎样利用ADO.NET调用Oracle存储过程,以及怎样填充至数据集中。至于怎样操纵DataSet,不是本文的讨论范围。有兴趣的读者可以参考MSDN以及相关书籍。

六、DataAdapter更新数据库

通常用DataAdapter取回DataSet,将会对DataSet进行一些修改,继而更新数据库(如果只是为了获取数据,微软推荐使用DataReader代替DataSet)。然而,通过存储过程更新数据库,并不是那么简单,不能简单地通过DataAdapterUpdate()方法进行更新。必须手动为DataAdapter添加InsertCommand, DeleteCommand, UpdateCommand,因为存储过程对这些操作的细节是不知情的,必须人为给出。

为了达成这个目标,我完善了之前的TestPackage包,包头如下:

create or replace package TestPackage is

background: #e6e6e6; margin: 0cm 0cm 0p

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics