s 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)

上一页  [1] [2] [3] [4] [5] [6] [7] 下一页

在google里搜索更多在.NET中调用Oracle9i存储过程经验总结

	Web www.51ec.org

上一篇信息学院： Asp.Net(C#)+Sql Server三层架构下数据存取方案(五)…

下一篇信息学院： asp.net 中用户和角色的验证…