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

最新推荐文章于 2022-06-05 13:44:23 发布

ohbaby1981

最新推荐文章于 2022-06-05 13:44:23 发布

阅读量207

点赞数

分类专栏： DB 文章标签： oracle 存储 .net dataset exception string

DB 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

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

在 .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)

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

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

ohbaby1981

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
在.NET中调用Oracle9i存储过程经验总结

在.NET中调用Oracle9i存储过程经验总结在.NET中调用Oracle9i存储过程可以用多个数据提供者，比如OralceClient，OleDb。本文将用OralceClient为例。.NET语言用C#。一、调用一个带输入、输出参数的存储过程首先，在Oralce中创建存储过程如下：createorreplaceprocedure GetRecords(name_out outvarchar2,age_in invarchar2) asbeginselectNAMEinto name_out
复制链接

扫一扫