.net 调用Oracle存储过程

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

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

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

首先，在Oralce中创建存储过程如下：

[html]  view plain  copy

1. create or replace procedure GetRecords(name_out out varchar2,age_in in varchar2)   
2. as  
3. begin  
4. select NAMEinto name_out from test where AGE = age_in;  
5. end;  

然后，在C#项目中作如下调用：

[html]  view plain  copy

1. string connectionString = "DataSource=YXZHANG;User ID=YXZHANG;Password=YXZHANG";  
2.      string queryString = "getrecords";  
3.     OracleConnection cn = newOracleConnection(connectionString);  
4.     OracleCommand cmd = newOracleCommand(queryString,cn);  
5.     cmd.CommandType = CommandType.StoredProcedure;  
6.     cmd.Parameters.Add("name_out",OracleType.VarChar,20);  
7.     cmd.Parameters["name_out"].Direction = ParameterDirection.Output;  
8.     cmd.Parameters.Add("age_in",21);  
9.       
10.      try  
11.      {  
12.         cn.Open();  
13.         cmd.ExecuteNonQuery();  
14.         Console.WriteLine("Nameis:{0}",cmd.Parameters["name_out"].Value.ToString());  
15.         cn.Close();  
16.      }  
17.      catch( OracleException ex )  
18.      {  
19.         Console.WriteLine("Exception occurred!");  
20.         Console.WriteLine("The exception messageis:{0}",ex.Message.ToString());  
21.      }  
22.      finally  
23.      {  
24.         Console.WriteLine("------------------End-------------------");  
25.      }  

小结：

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

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

首先，在Oralce中创建存储过程如下：

[html]  view plain  copy

1. create or replace procedure insertRecord(UserID in varchar2,  
2. UserName in varchar2,  
3.                                         UserAge in varchar2) is  
4.     begin  
5.      insert into test values(UserID, UserName, UserAge);  
6.     end;  

然后，在C#项目中作如下调用：

[html]  view plain  copy

1. string connectionString= "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";  
2. string queryString ="insertRecord";  
3. OracleConnection cn = newOracleConnection(connectionString);  
4. OracleCommand cmd = newOracleCommand(queryString,cn);  
5. cmd.CommandType = CommandType.StoredProcedure;  
6.      cmd.Parameters.Add("UserID","007");  
7.     cmd.Parameters.Add("UserName","Dell");  
8.     cmd.Parameters.Add("UserAge","40");       
9.      try  
10.      {  
11.         cn.Open();  
12.         cmd.ExecuteNonQuery();  
13.         Console.WriteLine("Record inserted!");  
14.         cn.Close();  
15.      }  
16.      catch( OracleException ex )  
17.      {  
18.         Console.WriteLine("Exception occurred!");  
19.         Console.WriteLine("The exception messageis:{0}",ex.Message.ToString());  
20.      }  
21.      finally  
22.      {  
23.         Console.WriteLine("------------------End-------------------");  
24.      }  

小结：

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

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

三、IDENTITY 和 SEQUENCE

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

首先，在Oracle中创建了一个名为TEST_SEQ的Sequence对象，SQL语句如下：

[html]  view plain  copy

1. create sequence TEST_SEQ  
2. minvalue 100  
3. maxvalue 999  
4. start with 102  
5. increment by 1  
6. nocache;  

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

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

--这次我修改了test表的定义，和前面的示例不同。其中，UserID是PK。

[html]  view plain  copy

1. create or replace procedureInsertRecordWithSequence(UserID   out number,  
2.                                                         UserName in varchar2,  
3.                                                         UserAge  in number)  
4. is  
5. begin  
6.         insert into test(id, name,age)         --插入一条记录，PK值从Sequece获取  
7.         values(test_seq.nextval, UserName,UserAge);  
8. /*返回PK值。注意Dual表的用法*/  
9. select test_seq.currval intoUserIDfrom dual;     
10. end InsertRecordWithSequence;  

接下来，就是在C#中进行调用了：

[html]  view plain  copy

1. string connectionString= "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";  
2.      string queryString ="InsertRecordWithSequence";  
3.     OracleConnection cn = newOracleConnection(connectionString);  
4.     OracleCommand cmd = new OracleCommand(queryString,cn);  
5.     cmd.CommandType = CommandType.StoredProcedure;  
6.     cmd.Parameters.Add("UserID",OracleType.Number);  
7.     cmd.Parameters["UserID"].Direction = ParameterDirection.Output;  
8.      cmd.Parameters.Add("UserName","Jack");  
9.     cmd.Parameters.Add("UserAge",40);   
10.      try  
11.      {  
12.          cn.Open();  
13.          int rowAffected =cmd.ExecuteNonQuery();  
14.          Console.WriteLine("{0}行已插入。",rowAffected);  
15.          Console.WriteLine("插入行的ID为：{0}",cmd.Parameters["UserID"].Value.ToString());  
16.         cn.Close();  
17.      }  
18.      catch( OracleException ex )  
19.      {  
20.         Console.WriteLine("Exception occurred!");  
21.         Console.WriteLine("The exception messageis:{0}",ex.Message.ToString());  
22.      }  
23.      finally  
24.      {  
25.         Console.WriteLine("------------------End-------------------");  
26.      }   

小结：

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

在.NET中调用Oracle存储过程经验总结(3)  

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

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

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

[html]  view plain  copy

1. create or replace package TestPackage is  
2.    typemycursor is ref cursor; --定义游标变量  
3.     procedureGetRecords(ret_cursor out mycursor);-- 定义过程，用游标变量作为返回参数  
4. ndTestPackage;   

包体是这么定义的：

[html]  view plain  copy

1. create orreplace package body TestPackage is  
2. /*过程体*/  
3.          procedure GetRecords(ret_cursor outmycursor) as  
4.          begin  
5.              open ret_cursor for select*from test;  
6.          end GetRecords;  
7. endTestPackage;  

    已经万事具备了，让我们前台调用试试：

[html]  view plain  copy

1. string connectionString= "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";  
2.      string queryString ="TestPackage.GetRecords";    //注意调用方法  
3.     OracleConnection cn = newOracleConnection(connectionString);  
4.     OracleCommand cmd = newOracleCommand(queryString,cn);  
5.     cmd.CommandType = CommandType.StoredProcedure;  
6.     cmd.Parameters.Add("ret_cursor",OracleType.Cursor);    //注意这里的类型  
7.      cmd.Parameters["ret_cursor"].Direction =ParameterDirection.Output;  
8.       
9.      try  
10.      {  
11.         cn.Open();  
12.         OracleDataReader dr = cmd.ExecuteReader();  
13.         int i = 1;  
14.         while( dr.Read() )  
15.         {  
16.              Console.WriteLine("Record {0}:",i++);  
17.              Console.WriteLine("ID:{0}   Name:{1} Age:{2}",  
18. dr.GetOracleNumber(0),  
19. dr.GetOracleString(1),  
20. dr.GetOracleNumber(2));  
21.              Console.WriteLine();  
22.         }  
23.         dr.Close();        //用完DataReader对象要记得及时关闭  
24.         cn.Close();        //DataReader对象未关闭之前，不能关闭连接  
25.      }  
26.      catch( OracleException ex )  
27.      {  
28.         Console.WriteLine("Exception occurred!");  
29.         Console.WriteLine("The exception messageis:{0}",ex.Message.ToString());  
30.      }  
31.      finally  
32.      {  
33.         Console.WriteLine("------------------End-------------------");  
34.      }   

请看结果：

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包。

 

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

[html]  view plain  copy

1. string connectionString= "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";  
2.      string queryString = "TestPackage.GetRecords";  
3.     OracleConnection cn = newOracleConnection(connectionString);  
4.     OracleCommand cmd = newOracleCommand(queryString,cn);  
5.     cmd.CommandType = CommandType.StoredProcedure;  
6.     cmd.Parameters.Add("ret_cursor",OracleType.Cursor);  
7.     cmd.Parameters["ret_cursor"].Direction = ParameterDirection.Output;  
8.       
9.      try  
10.      {  
11.         cn.Open();  
12.         OracleDataAdapter da = newOracleDataAdapter(cmd);  
13.         DataSet ds = new DataSet();  
14.         da.Fill(ds,"TestTable");  
15.         cn.Close();  
16.         for( int i =0;i <= ds.Tables["TestTable"].Rows.Count-1;i++ )  
17.         {  
18.              string id =ds.Tables["TestTable"].Rows[i]["ID"].ToString();  
19.              string name =ds.Tables["TestTable"].Rows[i]["NAME"].ToString();  
20.              string age =ds.Tables["TestTable"].Rows[i]["AGE"].ToString();  
21.              Console.WriteLine("Record {0}:",i+1);  
22.              Console.WriteLine("ID:{0}\tName:{1}\tAge:{2}\n",id,name,age);  
23.         }  
24.      }  
25.      catch( OracleException ex )  
26.      {  
27.         Console.WriteLine("Exception occurred!");  
28.         Console.WriteLine("The exception messageis:{0}",ex.Message.ToString());  
29.      }  
30.      finally  
31.      {  
32.         Console.WriteLine("------------------End-------------------");  
33.      }   

小结：

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

 在.NET中调用Oracle存储过程经验总结(5) 

六、用DataAdapter更新数据库

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

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

[html]  view plain  copy

1. create or replace package TestPackage is  
2.      typemycursor is ref cursor;  
3.      procedureUpdateRecords(id_in in number,newNamein varchar2,newAgein number);  
4.      procedureSelectRecords(ret_cursor out mycursor);  
5.      procedureDeleteRecords(id_in in number);  
6.      procedureInsertRecords(name_in in varchar2, age_inin number);  
7. endTestPackage;   

包体如下：

[html]  view plain  copy

1. create orreplace package body TestPackage is  
2.     procedure UpdateRecords(id_in in number,newName in varchar2, newAge  in number) as  
3.     begin  
4.      update test set age = newAge, name= newNamewhere id = id_in;  
5.     end UpdateRecords;  
6.   
7.     procedure SelectRecords(ret_cursor outmycursor)as  
8.     begin  
9.        open ret_cursor for select* from test;  
10.     end SelectRecords;  
11.   
12.     procedure DeleteRecords(id_in in number)as  
13.     begin  
14.        delete from test whereid = id_in;  
15.     end DeleteRecords;  
16.   
17.     procedure InsertRecords(name_in in varchar2,age_inin number) as  
18.     begin  
19.        insert into test values(test_seq.nextval, name_in, age_in);  
20. --test_seq是一个已建的Sequence对象，请参照前面的示例  
21. endInsertRecords;  
22.     end TestPackage;   

前台调用代码如下，有点繁琐，请耐心阅读：

     

[html]  view plain  copy

1. string connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";  
2.      string queryString ="TestPackage.SelectRecords";  
3.     OracleConnection cn = newOracleConnection(connectionString);  
4.     OracleCommand cmd = newOracleCommand(queryString,cn);  
5.     cmd.CommandType = CommandType.StoredProcedure;  
6.     cmd.Parameters.Add("ret_cursor",OracleType.Cursor);  
7.      cmd.Parameters["ret_cursor"].Direction =ParameterDirection.Output;  
8.       
9.      try  
10.      {  
11.          cn.Open();  
12.          OracleDataAdapter da = newOracleDataAdapter(cmd);  
13.          DataSet ds = new DataSet();  
14.          da.Fill(ds,"TestTable");  
15.          cn.Close();  
16.          int count =ds.Tables["TestTable"].Rows.Count;  
17.           
18.          /*打印原始记录*/  
19.          Console.WriteLine("Old Records is:");  
20.          for( int i = 0;i <= count - 1;i++ )  
21.         {  
22.              string id =ds.Tables["TestTable"].Rows[i]["ID"].ToString();  
23.              string name =ds.Tables["TestTable"].Rows[i]["NAME"].ToString();  
24.              string age =ds.Tables["TestTable"].Rows[i]["AGE"].ToString();  
25.    
26.              Console.WriteLine("Record {0}:",i+1);  
27.              Console.WriteLine("ID:{0}\tName:{1}\tAge:{2}\n",id,name,age);  
28.         }  
29.           
30.         da.SelectCommand = cmd;    //为DataAdapter指定SelectCommand  
31.    
32.         OracleCommand updateCmd = newOracleCommand("TestPackage.UpdateRecords",cn);  
33.         updateCmd.CommandType = CommandType.StoredProcedure;  
34.         updateCmd.Parameters.Add("id_in",OracleType.Number,3,"ID");  
35.         updateCmd.Parameters.Add("newName",OracleType.VarChar,20,"NAME");  
36.         updateCmd.Parameters.Add("newAge",OracleType.Number,3,"AGE");  
37.         da.UpdateCommand = updateCmd;        //为DataAdapter指定UpdateCommand  
38.    
39.         OracleCommand deleteCmd = newOracleCommand("TestPackage.DeleteRecords",cn);  
40.         deleteCmd.CommandType = CommandType.StoredProcedure;  
41.         deleteCmd.Parameters.Add("id_in",OracleType.Number,3,"ID");  
42.         da.DeleteCommand = deleteCmd;        //为DataAdapter指定DeleteCommand  
43.    
44.         OracleCommand insertCmd = newOracleCommand("TestPackage.InsertRecords",cn);  
45.          insertCmd.CommandType = CommandType.StoredProcedure;  
46.         insertCmd.Parameters.Add("name_in",OracleType.VarChar,20,"NAME");  
47.         insertCmd.Parameters.Add("age_in",OracleType.Number,3,"AGE");  
48.         da.InsertCommand = insertCmd;        //为DataAdapter指定InsertCommand  
49.    
50.         DataTable newTable = ds.Tables["TestTable"];  
51.           
52.         /*修改第一条记录*/  
53.         newTable.Rows[0]["AGE"] = 22;  
54.         newTable.Rows[0]["NAME"] = "John";  
55.           
56.         /*删除一条记录*/  
57.         newTable.Rows[2].Delete();  
58.    
59.         /*插入一条记录*/  
60.         DataRow newRow = newTable.NewRow();  
61.         newRow["NAME"] = "Bob";  
62.         newRow["AGE"] = 99;  
63.         newTable.Rows.Add(newRow);  
64.    
65.         cn.Open();  
66.         da.Update(newTable);       //将更改更新到数据库  
67.         newTable.Clear();            //清空DataTable  
68.         da.Fill(newTable);           //获取更改后的数据  
69.         cn.Close();  
70.    
71.         /*打印新记录*/  
72.         Console.WriteLine("New Records is:");  
73.         for( int i =0;i <= newTable.Rows.Count - 1;i++ )  
74.         {  
75.              string id =newTable.Rows[i]["ID"].ToString();  
76.              string name =newTable.Rows[i]["NAME"].ToString();  
77.              string age =newTable.Rows[i]["AGE"].ToString();  
78.    
79.              Console.WriteLine("Record {0}:",i+1);  
80.             Console.WriteLine("ID:{0}\tName:{1}\tAge:{2}\n",id,name,age);  
81.         }  
82.      }  
83.      catch( OracleException ex )  
84.      {  
85.          Console.WriteLine("Exception occurred!");  
86.          Console.WriteLine("The exception messageis:{0}",ex.Message.ToString());  
87.      }  
88.      finally  
89.      {  
90.         Console.WriteLine("------------------End-------------------");  
91. }   

运行结果如下：

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()方法更新数据库，当然它需要你给出更新数据库的一些操作（为其定义处理过程）。

由于笔者是边学边写，以上示例不免有些不妥之处，请多多指教！