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

2023-12-17 07:18
文章标签 过程 调用 存储 经验总结 net oracle9i

本文主要是介绍在.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
minvalue100
maxvalue999
start with102
increment by1
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()方法更新数据库，当然它需要你给出更新数据库的一些操作（为其定义处理过程）。

这篇关于在.NET中调用Oracle9i存储过程经验总结的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/503543。

相关文章

使用MongoDB进行数据存储的操作流程

使用MongoDB进行数据存储的操作流程

《使用MongoDB进行数据存储的操作流程》在现代应用开发中,数据存储是一个至关重要的部分,随着数据量的增大和复杂性的增加,传统的关系型数据库有时难以应对高并发和大数据量的处理需求,MongoDB作为... 目录什么是MongoDB？MongoDB的优势使用MongoDB进行数据存储1. 安装MongoDB
阅读更多...
SpringBoot 整合 Grizzly的过程

SpringBoot 整合 Grizzly的过程

《SpringBoot整合Grizzly的过程》Grizzly是一个高性能的、异步的、非阻塞的HTTP服务器框架,它可以与SpringBoot一起提供比传统的Tomcat或Jet... 目录为什么选择 Grizzly？Spring Boot + Grizzly 整合的优势添加依赖自定义 Grizzly 作为
阅读更多...
mysql-8.0.30压缩包版安装和配置MySQL环境过程

mysql-8.0.30压缩包版安装和配置MySQL环境过程

《mysql-8.0.30压缩包版安装和配置MySQL环境过程》该文章介绍了如何在Windows系统中下载、安装和配置MySQL数据库,包括下载地址、解压文件、创建和配置my.ini文件、设置环境变量... 目录压缩包安装配置下载配置环境变量下载和初始化总结压缩包安装配置下载下载地址：https://d
阅读更多...
Idea调用WebService的关键步骤和注意事项

Idea调用WebService的关键步骤和注意事项

《Idea调用WebService的关键步骤和注意事项》：本文主要介绍如何在Idea中调用WebService,包括理解WebService的基本概念、获取WSDL文件、阅读和理解WSDL文件、选... 目录前言一、理解WebService的基本概念二、获取WSDL文件三、阅读和理解WSDL文件四、选择对接
阅读更多...
springboot整合gateway的详细过程

springboot整合gateway的详细过程

《springboot整合gateway的详细过程》本文介绍了如何配置和使用SpringCloudGateway构建一个API网关,通过实例代码介绍了springboot整合gateway的过程,需要... 目录1. 添加依赖2. 配置网关路由3. 启用Eureka客户端（可选）4. 创建主应用类5. 自定
阅读更多...
Java调用Python代码的几种方法小结

Java调用Python代码的几种方法小结

《Java调用Python代码的几种方法小结》Python语言有丰富的系统管理、数据处理、统计类软件包,因此从java应用中调用Python代码的需求很常见、实用,本文介绍几种方法从java调用Pyt... 目录引言Java core使用ProcessBuilder使用Java脚本引擎总结引言python
阅读更多...
最新版IDEA配置 Tomcat的详细过程

最新版IDEA配置 Tomcat的详细过程

《最新版IDEA配置Tomcat的详细过程》本文介绍如何在IDEA中配置Tomcat服务器,并创建Web项目,首先检查Tomcat是否安装完成,然后在IDEA中创建Web项目并添加Web结构,接着,... 目录配置tomcat第一步，先给项目添加Web结构查看端口号配置tomcat    先检查自己的to
阅读更多...
使用JavaScript操作本地存储

使用JavaScript操作本地存储

《使用JavaScript操作本地存储》这篇文章主要为大家详细介绍了JavaScript中操作本地存储的相关知识,文中的示例代码讲解详细,具有一定的借鉴价值,有需要的小伙伴可以参考一下... 目录本地存储：localStorage 和 sessionStorage基本使用方法1. localStorage
阅读更多...
SpringBoot集成SOL链的详细过程

SpringBoot集成SOL链的详细过程

《SpringBoot集成SOL链的详细过程》Solanaj是一个用于与Solana区块链交互的Java库,它为Java开发者提供了一套功能丰富的API,使得在Java环境中可以轻松构建与Solana... 目录一、什么是solanaj?二、Pom依赖三、主要类3.1 RpcClient3.2 Public
阅读更多...
Android数据库Room的实际使用过程总结

Android数据库Room的实际使用过程总结

《Android数据库Room的实际使用过程总结》这篇文章主要给大家介绍了关于Android数据库Room的实际使用过程,详细介绍了如何创建实体类、数据访问对象（DAO）和数据库抽象类,需要的朋友可以... 目录前言一、Room的基本使用1.项目配置2.创建实体类（Entity）3.创建数据访问对象（DAO
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2