在.net中调用oracle9i存储过程可以用多个数据提供者，比如oralceclient，oledb。本文将用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_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 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会按照您向参数集合中添加它们的顺序来访问这些游标，而不是按照它们在过程中出现的顺序来访问。可使用datareader的nextresult()方法前进到下一个游标。
五、用返回的结果集填充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 = 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）。然而，通过存储过程更新数据库，并不是那么简单，不能简单地通过dataadapter的update()方法进行更新。必须手动为dataadapter添加insertcommand, deletecommand, updatecommand，因为存储过程对这些操作的细节是不知情的，必须人为给出。
为了达成这个目标，我完善了之前的testpackage包，包头如下：
create or replace package testpackage is
type mycursor is ref cursor;
procedure updaterecords(id_in in number,newname in varchar2,newage in number);
procedure selectrecords(ret_cursor out mycursor);
procedure deleterecords(id_in in number);
procedure insertrecords(name_in in varchar2, age_in in number);
end testpackage;
包体如下：
create or replace package body testpackage is
procedure updaterecords(id_in in number, newname in varchar2, newage in number) as
begin
update test set age = newage, name = newname where id = id_in;
end updaterecords;
procedure selectrecords(ret_cursor out mycursor) as
begin
open ret_cursor for select * from test;
end selectrecords;
procedure deleterecords(id_in in number) as
begin
delete from test where id = id_in;
end deleterecords;
procedure insertrecords(name_in in varchar2, age_in in number) as
begin
insert into test values (test_seq.nextval, name_in, age_in);
--test_seq是一个已建的sequence对象，请参照前面的示例
end insertrecords;
end testpackage;
前台调用代码如下，有点繁琐，请耐心阅读：
string connectionstring = "data source=yxzhang;user id=yxzhang;password=yxzhang";
string querystring = "testpackage.selectrecords";
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();
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 = new oraclecommand("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 = new oraclecommand("testpackage.deleterecords",cn);
deletecmd.commandtype = commandtype.storedprocedure;
deletecmd.parameters.add("id_in",oracletype.number,3,"id");
da.deletecommand = deletecmd; //为dataadapter指定deletecommand
oraclecommand insertcmd = new oraclecommand("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 message is:{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()方法更新数据库，当然它需要你给出更新数据库的一些操作（为其定义处理过程）。
由于笔者是边学边写，以上示例不免有些不妥之处，请多多指教！
