在. NET 中调用 Oracle9i 存储过程经验总结 (1)
在. NET 中调用 Oracle9i 存储过程可以用多个数据提供者,比如 OralceClient,OleDb。本文将用 OralceClient 为例。.NET 语言用 C#。
一、调用一个带输入、输出参数的存储过程
首先,在 Oralce 中创建存储过程如下:
- create or replace procedure GetRecords(name_out out varchar2, age_in in varchar2) as begin select NAMEinto name_out from test where AGE = age_in;
- end;
然后,在 C# 项目中作如下调用:
- string connectionString = "DataSource=YXZHANG;User ID=YXZHANG;Password=YXZHANG";
- string queryString = "getrecords";
- OracleConnection cn = newOracleConnection(connectionString);
- OracleCommand cmd = newOracleCommand(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("Nameis:{0}", cmd.Parameters["name_out"].Value.ToString());
- cn.Close();
- } catch(OracleException ex) {
- Console.WriteLine("Exception occurred!");
- Console.WriteLine("The exception messageis:{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 = newOracleConnection(connectionString);
- OracleCommand cmd = newOracleCommand(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 messageis:{0}", ex.Message.ToString());
- } finally {
- Console.WriteLine("------------------End-------------------");
- }
小结:
不返回数据的存储过程一般有 Delete, Insert, Update 等。虽然它们的 SQL 语句不同,但是调用方面都是一样的。只要传入正确的参数,一般不会出什么问题。不过还是要注意,在使用 OralceClient 作为数据提供者的时候,参数名称一定要和存储过程定义中的一致!至于参数添加的顺序倒是无所谓的,因为有参数名称作为区别。
在. NET 中调用 Oracle9i 存储过程经验总结 (2)
三、IDENTITY 和 SEQUENCE
Sql Server 中,定义一个列为递增列很容易,但我在 Oracle 中却怎么也找不到设置的方法。不过查了点资料后知道 Oracle 中有个叫 Sequence 的对象,产生一个唯一的序列号,类似于 Sql Server 中的 IDENTITY。于是,我做了如下实验:
首先,在 Oracle 中创建了一个名为 TEST_SEQ 的 Sequence 对象,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 表的定义,和前面的示例不同。其中,UserID 是 PK。
- create or replace procedureInsertRecordWithSequence(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 intoUserIDfrom dual;
- end InsertRecordWithSequence;
接下来,就是在 C# 中进行调用了:
- string connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";
- string queryString = "InsertRecordWithSequence";
- OracleConnection cn = newOracleConnection(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 messageis:{0}", ex.Message.ToString());
- } finally {
- Console.WriteLine("------------------End-------------------");
- }
小结:
使用 Sequece 对象可以很容易地创建唯一序列,在存储过程中的调用也十分方便,只要 sequence_name.nextval 以及 sequence.currval 就能得到下一个以及当前的序列值。倒是 Dual 表值得注意。
在. NET 中调用 Oracle 存储过程经验总结 (3)
四、使用 DataReader 读取返回的结果集
为了让存储过程返回结果集,必须定义一个游标变量作为输出参数。这和 Sql Server 中有着很大的不同!并且还要用到 Oracle 中 "包"(Package)的概念,似乎有点繁琐,但熟悉后也会觉得很方便。
关于 "包" 的概念,有很多内容可以参考,在此就不赘述了。首先,我创建了一个名为 TestPackage 的包,包头是这么定义的:
- create or replace package TestPackage is typemycursor is ref cursor; --定义游标变量procedureGetRecords(ret_cursor out mycursor); --定义过程,用游标变量作为返回参数endTestPackage;
包体是这么定义的:
- create orreplace package body TestPackage is
- /*过程体*/
- procedure GetRecords(ret_cursor outmycursor) as begin open ret_cursor
- for select * from test;
- end GetRecords;
- endTestPackage;
已经万事具备了,让我们前台调用试试:
- string connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";
- string queryString = "TestPackage.GetRecords"; //注意调用方法
- OracleConnection cn = newOracleConnection(connectionString);
- OracleCommand cmd = newOracleCommand(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 messageis:{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 会按照您向参数集合中添加它们的顺序来访问这些游标,而不是按照它们在过程中出现的顺序来访问。可使用 DataReader 的 NextResult() 方法前进到下一个游标。
在. NET 中调用 Oracle 存储过程经验总结 (4)
五、用返回的结果集填充 DataSet
ADO.NET 舍弃了原来 ADO 中 RecordSet 的概念,而使用全新的 DataSet 来替代。DataSet 可以提供更加强大的功能!有了前面的基础,利用 ADO.NET 从 Oralce 返回 DataSet 也是非常简单的。主要思想就是用 DataAdapter 接收返回的游标,再用 DataAdapter 的 Fill() 方法填充数据集。在下面的例子中,我仍用了前一个例子中建好的 TestPackage 包。
下面是前台调用主要代码:
- string connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";
- string queryString = "TestPackage.GetRecords";
- OracleConnection cn = newOracleConnection(connectionString);
- OracleCommand cmd = newOracleCommand(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 = newOracleDataAdapter(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 messageis:{0}", ex.Message.ToString());
- } finally {
- Console.WriteLine("------------------End-------------------");
- }
小结:
程序调用后的结果和刚才用 DataReader 调用的结果一样。这里只说明怎样利用 ADO.NET 调用 Oracle 存储过程,以及怎样填充至数据集中。至于怎样操纵 DataSet,不是本文的讨论范围。有兴趣的读者可以参考 MSDN 以及相关书籍。
在. NET 中调用 Oracle 存储过程经验总结 (5)
六、用 DataAdapter 更新数据库
通常用 DataAdapter 取回 DataSet,将会对 DataSet 进行一些修改,继而更新数据库(如果只是为了获取数据,微软推荐使用 DataReader 代替 DataSet)。然而,通过存储过程更新数据库,并不是那么简单,不能简单地通过 DataAdapter 的 Update() 方法进行更新。必须手动为 DataAdapter 添加 InsertCommand, DeleteCommand,UpdateCommand,因为存储过程对这些操作的细节是不知情的,必须人为给出。
为了达成这个目标,我完善了之前的 TestPackage 包,包头如下:
- create or replace package TestPackage is typemycursor is ref cursor;
- procedureUpdateRecords(id_in in number, newNamein varchar2, newAgein number);
- procedureSelectRecords(ret_cursor out mycursor);
- procedureDeleteRecords(id_in in number);
- procedureInsertRecords(name_in in varchar2, age_inin number);
- endTestPackage;
包体如下:
- create orreplace package body TestPackage is procedure UpdateRecords(id_in in number, newName in varchar2, newAge in number) as begin update test set age = newAge,
- name = newNamewhere id = id_in;
- end UpdateRecords;
- procedure SelectRecords(ret_cursor outmycursor) as begin open ret_cursor
- for select * from test;
- end SelectRecords;
- procedure DeleteRecords(id_in in number) as begin delete from test whereid = id_in;
- end DeleteRecords;
- procedure InsertRecords(name_in in varchar2, age_inin number) as begin insert into test values(test_seq.nextval, name_in, age_in); --test_seq是一个已建的Sequence对象,请参照前面的示例endInsertRecords;
- end TestPackage;
前台调用代码如下,有点繁琐,请耐心阅读:
- string connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";
- string queryString = "TestPackage.SelectRecords";
- OracleConnection cn = newOracleConnection(connectionString);
- OracleCommand cmd = newOracleCommand(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 = newOracleDataAdapter(cmd);
- DataSet ds = new DataSet();
- da.Fill(ds, "TestTable");
- cn.Close();
- int count = ds.Tables["TestTable"].Rows.Count;
- /*打印原始记录*/
- Console.WriteLine("Old Records is:");
- for (int i = 0; i <= 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);
- }
- da.SelectCommand = cmd; //为DataAdapter指定SelectCommand
- OracleCommand updateCmd = newOracleCommand("TestPackage.UpdateRecords", cn);
- updateCmd.CommandType = CommandType.StoredProcedure;
- updateCmd.Parameters.Add("id_in", OracleType.Number, 3, "ID");
- updateCmd.Parameters.Add("newName", OracleType.VarChar, 20, "NAME");
- updateCmd.Parameters.Add("newAge", OracleType.Number, 3, "AGE");
- da.UpdateCommand = updateCmd; //为DataAdapter指定UpdateCommand
- OracleCommand deleteCmd = newOracleCommand("TestPackage.DeleteRecords", cn);
- deleteCmd.CommandType = CommandType.StoredProcedure;
- deleteCmd.Parameters.Add("id_in", OracleType.Number, 3, "ID");
- da.DeleteCommand = deleteCmd; //为DataAdapter指定DeleteCommand
- OracleCommand insertCmd = newOracleCommand("TestPackage.InsertRecords", cn);
- insertCmd.CommandType = CommandType.StoredProcedure;
- insertCmd.Parameters.Add("name_in", OracleType.VarChar, 20, "NAME");
- insertCmd.Parameters.Add("age_in", OracleType.Number, 3, "AGE");
- da.InsertCommand = insertCmd; //为DataAdapter指定InsertCommand
- DataTable newTable = ds.Tables["TestTable"];
- /*修改第一条记录*/
- newTable.Rows[0]["AGE"] = 22;
- newTable.Rows[0]["NAME"] = "John";
- /*删除一条记录*/
- newTable.Rows[2].Delete();
- /*插入一条记录*/
- DataRow newRow = newTable.NewRow();
- newRow["NAME"] = "Bob";
- newRow["AGE"] = 99;
- newTable.Rows.Add(newRow);
- cn.Open();
- da.Update(newTable); //将更改更新到数据库
- newTable.Clear(); //清空DataTable
- da.Fill(newTable); //获取更改后的数据
- cn.Close();
- /*打印新记录*/
- Console.WriteLine("New Records is:");
- for (int i = 0; i <= newTable.Rows.Count - 1; i++) {
- string id = newTable.Rows[i]["ID"].ToString();
- string name = newTable.Rows[i]["NAME"].ToString();
- string age = newTable.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 messageis:{0}", ex.Message.ToString());
- } finally {
- Console.WriteLine("------------------End-------------------");
- }
运行结果如下:
Old Records is:
Record 1:
ID:100 Name:Tony Age:23
Record 2:
ID:101 Name:Jack Age:34
Record 3:
ID:103 Name:newAdd Age:100
New Records is:
Record 1:
ID:100 Name:John Age:22
Record 2:
ID:101 Name:Jack Age:34
Record 3:
ID:104 Name:Bob Age:99
------------------End-------------------
小结:
使用 Update() 方法更新数据库是非常有用的,但是只局限于单个表,对于表连接的情况,还是直接用 OracleCommand 的 ExcuteNonQuery() 方法去执行更新操作比较方便。
七、本文总结:
在. NET 中利用 ADO.NET 调用 Oralce9i 中的存储过程,函数,包等,可以提高效率,也能完成一些复杂的业务逻辑。然而调用 Oracle 存储过程和 Sql Server 中有着很大不同,最需要明确的一个思想就是:在 Oracle 的过程中,所有返回值必须以输出参数的形式出现。在 Oracle 中,要返回一个结果集,就必须把 REF CURSOR 作为返回参数,因此就要用到包。包类似于 C++ 中的类,封装了一些全局变量、常量和函数,在函数中又可以有自己的私有变量。通过返回给应用程序的游标,应用程序就可以用 DataReader 或者 DataAdapter 接收它,进而做一些处理。还还可以用 DataAdapter 的 Update() 方法更新数据库,当然它需要你给出更新数据库的一些操作(为其定义处理过程)。
由于笔者是边学边写,以上示例不免有些不妥之处,请多多指教!
来源: http://lib.csdn.net/article/dotnet/37976