在.net中調(diào)用oracle9i存儲過程可以用多個數(shù)據(jù)提供者，比如oralceclient，oledb。本文將用oralceclient為例。.net語言用c#。
一、調(diào)用一個帶輸入、輸出參數(shù)的存儲過程
首先，在oralce中創(chuàng)建存儲過程如下：
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#項目中作如下調(diào)用：
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-------------------");
}
小結(jié)：
以上是很平常的調(diào)用方法，但是我在做這個示例程序的時候，卻不是那么一帆風(fēng)順。這里要指出oracleclient的一個不盡如人意之處，就是對于參數(shù)名稱，必須和存儲過程定義中的參數(shù)同名，否則就會報錯。比如將代碼中的“name_out”改為別的名稱的話，就會報異常。但我試著用oledb代替oralceclient，卻沒有這個問題。不知道在新版本的數(shù)據(jù)提供程序中會否改進(jìn)這一點？
二、調(diào)用不返回數(shù)據(jù)的存儲過程
首先，在oralce中創(chuàng)建存儲過程如下：
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#項目中作如下調(diào)用：
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-------------------");
}
小結(jié)：
不返回數(shù)據(jù)的存儲過程一般有delete, insert, update等。雖然它們的sql語句不同，但是調(diào)用方面都是一樣的。只要傳入正確的參數(shù)，一般不會出什么問題。不過還是要注意，在使用oralceclient作為數(shù)據(jù)提供者的時候，參數(shù)名稱一定要和存儲過程定義中的一致！至于參數(shù)添加的順序倒是無所謂的，因為有參數(shù)名稱作為區(qū)別。
三、identity 和 sequence
sql server中，定義一個列為遞增列很容易，但我在oracle中卻怎么也找不到設(shè)置的方法。不過查了點資料后知道oracle中有個叫sequence的對象，產(chǎn)生一個唯一的序列號，類似于sql server中的identity。于是，我做了如下實驗：
首先，在oracle中創(chuàng)建了一個名為test_seq的sequence對象，sql語句如下：
create sequence test_seq
minvalue 100
maxvalue 999
start with 102
increment by 1
nocache;
語法應(yīng)該是比較易懂的，最小最大值分別用minvalue,maxvalue表示，初始值是102（這個數(shù)字是動態(tài)變化的，我創(chuàng)建的時候設(shè)的是100，后因插入了2條數(shù)據(jù)后就自動增加了2），increment當(dāng)然就是步長了。在pl/sql中可以用test_seq.nextval訪問下一個序列號，用test_seq.currval訪問當(dāng)前的序列號。
定義完了sequence，接下來就是創(chuàng)建一個存儲過程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#中進(jìn)行調(diào)用了：
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-------------------");
}
小結(jié)：
使用sequece對象可以很容易地創(chuàng)建唯一序列，在存儲過程中的調(diào)用也十分方便，只要sequence_name.nextval以及sequence.currval就能得到下一個以及當(dāng)前的序列值。倒是dual表值得注意。
四、使用datareader讀取返回的結(jié)果集
為了讓存儲過程返回結(jié)果集，必須定義一個游標(biāo)變量作為輸出參數(shù)。這和sql server中有著很大的不同！并且還要用到oracle中“包”（package）的概念，似乎有點繁瑣，但熟悉后也會覺得很方便。
關(guān)于“包”的概念，有很多內(nèi)容可以參考，在此就不贅述了。首先，我創(chuàng)建了一個名為testpackage的包，包頭是這么定義的：
create or replace package testpackage is
type mycursor is ref cursor; -- 定義游標(biāo)變量
procedure getrecords(ret_cursor out mycursor); -- 定義過程，用游標(biāo)變量作為返回參數(shù)
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;
已經(jīng)萬事具備了，讓我們前臺調(diào)用試試：
string connectionstring = "data source=yxzhang;user id=yxzhang;password=yxzhang";
string querystring = "testpackage.getrecords"; //注意調(diào)用方法
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對象要記得及時關(guān)閉
cn.close(); //datareader對象未關(guān)閉之前，不能關(guān)閉連接
}
catch( oracleexception ex )
{
console.writeline("exception occurred!");
console.writeline("the exception message is:{0}",ex.message.tostring());
}
finally
{
console.writeline("------------------end-------------------");
}
請看結(jié)果：
record 1:
id:100 name:tony age:23
record 2:
id:101 name:jack age:40
------------------end-------------------
小結(jié)：
包是oracle特有的概念，sql server中找不到相匹配的東西。在我看來，包有點像vc++的類，包頭就是.h文件，包體就是.cpp文件。包頭只負(fù)責(zé)定義，包體則負(fù)責(zé)具體實現(xiàn)。如果包返回多個游標(biāo)，則datareader會按照您向參數(shù)集合中添加它們的順序來訪問這些游標(biāo)，而不是按照它們在過程中出現(xiàn)的順序來訪問?？墒褂胐atareader的nextresult()方法前進(jìn)到下一個游標(biāo)。
五、用返回的結(jié)果集填充dataset
ado.net舍棄了原來ado中recordset的概念，而使用全新的dataset來替代。dataset可以提供更加強(qiáng)大的功能！有了前面的基礎(chǔ)，利用ado.net從oralce返回dataset也是非常簡單的。主要思想就是用dataadapter接收返回的游標(biāo)，再用dataadapter的fill()方法填充數(shù)據(jù)集。在下面的例子中，我仍用了前一個例子中建好的testpackage包。
下面是前臺調(diào)用主要代碼：
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-------------------");
}
小結(jié)：
程序調(diào)用后的結(jié)果和剛才用datareader調(diào)用的結(jié)果一樣。這里只說明怎樣利用ado.net調(diào)用oracle存儲過程，以及怎樣填充至數(shù)據(jù)集中。至于怎樣操縱dataset，不是本文的討論范圍。有興趣的讀者可以參考msdn以及相關(guān)書籍。
六、用dataadapter更新數(shù)據(jù)庫
通常用dataadapter取回dataset，將會對dataset進(jìn)行一些修改，繼而更新數(shù)據(jù)庫（如果只是為了獲取數(shù)據(jù)，微軟推薦使用datareader代替dataset）。然而，通過存儲過程更新數(shù)據(jù)庫，并不是那么簡單，不能簡單地通過dataadapter的update()方法進(jìn)行更新。必須手動為dataadapter添加insertcommand, deletecommand, updatecommand，因為存儲過程對這些操作的細(xì)節(jié)是不知情的，必須人為給出。
為了達(dá)成這個目標(biāo)，我完善了之前的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;
前臺調(diào)用代碼如下，有點繁瑣，請耐心閱讀：
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); //將更改更新到數(shù)據(jù)庫
newtable.clear(); //清空datatable
da.fill(newtable); //獲取更改后的數(shù)據(jù)
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-------------------");
}
運(yùn)行結(jié)果如下：
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-------------------
小結(jié)：
使用update()方法更新數(shù)據(jù)庫是非常有用的，但是只局限于單個表，對于表連接的情況，還是直接用oraclecommand的excutenonquery()方法去執(zhí)行更新操作比較方便。
七、本文總結(jié)：
在.net中利用ado.net調(diào)用oralce9i中的存儲過程，函數(shù)，包等，可以提高效率，也能完成一些復(fù)雜的業(yè)務(wù)邏輯。然而調(diào)用oracle存儲過程和sql server中有著很大不同，最需要明確的一個思想就是：在oracle的過程中，所有返回值必須以輸出參數(shù)的形式出現(xiàn)。在oracle中，要返回一個結(jié)果集，就必須把ref cursor作為返回參數(shù)，因此就要用到包。包類似于c++中的類，封裝了一些全局變量、常量和函數(shù)，在函數(shù)中又可以有自己的私有變量。通過返回給應(yīng)用程序的游標(biāo)，應(yīng)用程序就可以用datareader或者dataadapter接收它，進(jìn)而做一些處理。還還可以用dataadapter的update()方法更新數(shù)據(jù)庫，當(dāng)然它需要你給出更新數(shù)據(jù)庫的一些操作（為其定義處理過程）。
由于筆者是邊學(xué)邊寫，以上示例不免有些不妥之處，請多多指教！
