DotNet操作Oracle存储过程备忘
发布日期：2021-05-14 04:36:44
浏览次数：18
分类：博客文章
本文共 19223 字，大约阅读时间需要 64 分钟。
简介
本文主要介绍如何利用Microsoft.net中提供的oracle支持以及System.Data.OracleClient命名空间下的方法来调用Oracle的存储过程。我尽量用一些经常用到的例子来讲解。
执行存储过程
让我们先从定义开始。首先，存储过程是一个能够处理一个或者多个动作的模块。而函数也是一个模块，但是他有返回值。这点不像存储过程。函数只是在执行的时候才会被调用，比如说表达式中的一个元素或者是默认的变量。
下面的第一个例子展示的是如何通过输入参数和输出参数来调用oracle的存储过程的。下面所有的示例中，我们将采用oracle默认的数据库ORCL，图例1中的代码向我们展示了如何创建一个名称为count_emp_by_dept的带有输入部门数字的参数，同时带有输出部门里面员工人数的参数的存储过程：
View Code
create or replace procedure count_emp_by_dept(pin_deptno number , pout_count out number ) is begin select count ( * ) into pout_count from scott.emp where deptno = pin_deptno; end count_emp_by_dept;
图例1：存储过程count_emp_by_dept
现在，让我们来创建一个控制台程序，并且添加对System.Data.OracleClient.dll的引用。图例2就是代码。代码中首先做的就是添加命名空间System.Data.OracleClient的引用。然后就是必须设置参数，最后利用OracleCommand对象的ExecuteNonQuery方法调用存储过程即可。
View Code
Using System; using System.Collections.Generic; using System.Text; using System.Data.OracleClient; using System.Data; namespace CallingOracleStoredProc { class Program { static void Main( string [] args) { using (OracleConnection objConn = new OracleConnection( " Data Source=ORCL; User ID=scott; Password=tiger " )) { OracleCommand objCmd = new OracleCommand(); objCmd.Connection = objConn; objCmd.CommandText = " count_emp_by_dept " ; objCmd.CommandType = CommandType.StoredProcedure; objCmd.Parameters.Add( " pin_deptno " , OracleType.Number).Value = 20 ; objCmd.Parameters.Add( " pout_count " , OracleType.Number).Direction = ParameterDirection.Output; try { objConn.Open(); objCmd.ExecuteNonQuery(); System.Console.WriteLine( " Number of employees in department 20 is {0} " ,objCmd.Parameters[ " pout_count " ].Value); } catch (Exception ex) { System.Console.WriteLine( " Exception: {0} " ,ex.ToString()); } objConn.Close(); } } } } 图例2：调用存储过程代码
调用函数
由于函数和存储过程的区别不大，除了会返回一个值之外，所以我们需要设置一个返回参数。看例子：
图例3的代码展示了如何创建一个get_count_emp_by_dept的函数，它接收部门编号的参数，返回部门人数。这和前面的存储过程非常类似。
View Code
create or replace function get_count_emp_by_dept(pin_deptno number ) return number is var_count number ; begin select count ( * ) into var_count from scott.emp where deptno = pin_deptno; return var_count; end get_count_emp_by_dept;
图例3：创建oracle函数
图例4就是调用函数的代码。从图示可以看到，我们需要定义一个返回参数去得到返回值。除此之外，代码部分和调用存储过程的代码几乎相同。
View Code
using System; using System.Collections.Generic; using System.Text; using System.Data.OracleClient; using System.Data; namespace CallingOracleStoredProc { class Program { static void Main( string [] args) { using (OracleConnection objConn = new OracleConnection( " Data Source=ORCL; User ID=scott; Password=tiger " )) { OracleCommand objCmd = new OracleCommand(); objCmd.Connection = objConn; objCmd.CommandText = " get_count_emp_by_dept " ; objCmd.CommandType = CommandType.StoredProcedure; objCmd.Parameters.Add( " pin_deptno " , OracleType.Number).Value = 20 ; objCmd.Parameters.Add( " return_value " , OracleType.Number).Direction = ParameterDirection.ReturnValue; try { objConn.Open(); objCmd.ExecuteNonQuery(); System.Console.WriteLine( " Number of employees in department 20 is {0} " , objCmd.Parameters[ " return_value " ].Value); } catch (Exception ex) { System.Console.WriteLine( " Exception: {0} " ,ex.ToString()); } objConn.Close(); } } } } 图例4：调用函数代码
使用游标
你可以使用REF CURSOR数据类型去处理oracle结果集。为了得到结果集，你必须在存储过程或者是函数中定义一个REF CURSOR输出参数来传递游标到你的程序中。下面就让我们去定义一个利用游标变量的存储过程。
我们先在图例5中定义一个包以及一个存储过程头。
View Code
create or replace package human_resources as type t_cursor is ref cursor ; procedure get_employee(cur_employees out t_cursor); end human_resources;
图例5：human_resources包以及get_employee存储过程
图例6是包体的定义：
View Code
create or replace package body human_resources as procedure get_employee(cur_employees out t_cursor) is begin open cur_employees for select * from emp; end get_employee; end human_resources;
图例6：包体
在图例7的代码中，我们调用了包中的存储过程。调用包中的存储过程可以使用如下的规则[package_name].[procedure_name]。为了得到游标，你需要定义一个游标参数，并且制定参数类型为输出才行，最后调用OracleCommand对象的ExecuteReader方法即可。
View Code
Using System; using System.Collections.Generic; using System.Text; using System.Data.OracleClient; using System.Data; namespace CallingOracleStoredProc { class Program { private static void prvPrintReader(OracleDataReader objReader) { for ( int i = 0 ; i < objReader.FieldCount; i ++ ) { System.Console.Write( " {0}\t " ,objReader.GetName(i)); } System.Console.Write( " \n " ); while (objReader.Read()) { for ( int i = 0 ; i < objReader.FieldCount; i ++ ) { System.Console.Write( " {0}\t " , objReader[i].ToString()); } System.Console.Write( " \n " ); } } static void Main( string [] args) { using (OracleConnection objConn = new OracleConnection( " Data Source=ORCL; User ID=scott; Password=tiger " )) { OracleCommand objCmd = new OracleCommand(); objCmd.Connection = objConn; objCmd.CommandText = " human_resources.get_employee " ; objCmd.CommandType = CommandType.StoredProcedure; objCmd.Parameters.Add( " cur_employees " , OracleType.Cursor).Direction = ParameterDirection.Output; try { objConn.Open(); OracleDataReader objReader = objCmd.ExecuteReader(); prvPrintReader(objReader); } catch (Exception ex) { System.Console.WriteLine( " Exception: {0} " ,ex.ToString()); } objConn.Close(); } ｝ } } 图例7：程序代码
如果存储过程返回多个游标集合的话，DataReader对象可以通过调用NextResult方法来处理。让我们看以下的一个例子。图例8展示了如何创建一个包头：
View Code
create or replace package human_resources as type t_cursor is ref cursor ; procedure get_employee_department(cur_employees out t_cursor, cur_departments out t_cursor); end human_resources;
图例8：包头
图例9则展示了创建包体的方法：
View Code
create or replace package body human_resources as procedure get_employee_department(cur_employees out t_cursor, cur_departments out t_cursor) is begin open cur_employees for select * from emp; open cur_departments for select * from dept; end get_employee_department; end human_resources;
图例9：包体
让我们通过图例10来看看代码处理方式：
View Code
using System; using System.Collections.Generic; using System.Text; using System.Data.OracleClient; using System.Data; namespace CallingOracleStoredProc { class Program { private static void prvPrintReader(OracleDataReader objReader) { for ( int i = 0 ; i < objReader.FieldCount; i ++ ) { System.Console.Write( " {0}\t " ,objReader.GetName(i)); } System.Console.Write( " \n " ); while (objReader.Read()) { for ( int i = 0 ; i < objReader.FieldCount; i ++ ) { System.Console.Write( " {0}\t " , objReader[i].ToString()); } System.Console.Write( " \n " ); } } static void Main( string [] args) { using (OracleConnection objConn = new OracleConnection( " Data Source=ORCL; User ID=scott; Password=tiger " )) { OracleCommand objCmd = new OracleCommand(); objCmd.Connection = objConn; objCmd.CommandText = " human_resources.get_employee_department " ; objCmd.CommandType = CommandType.StoredProcedure; objCmd.Parameters.Add( " cur_employees " , OracleType.Cursor).Direction = ParameterDirection.Output; objCmd.Parameters.Add( " cur_departments " , OracleType.Cursor).Direction = ParameterDirection.Output; try { objConn.Open(); OracleDataReader objReader = objCmd.ExecuteReader(); prvPrintReader(objReader); objReader.NextResult(); prvPrintReader(objReader); } catch (Exception ex) { System.Console.WriteLine( " Exception: {0} " ,ex.ToString()); } objConn.Close(); } } } } 图例10：程序代码
DataSet和DataAdapter的使用方法
最后的一个例子向我们展示了如何利用DataAdapter对象填充以及更新一个DataSet对象。首先需要做的就是创建四个操作零食表的增删查该的存储过程。图例11展示了创建的包头：
View Code
create or replace package human_resources as type t_cursor is ref cursor ; procedure select_employee(cur_employees out t_cursor); procedure insert_employee(p_empno number , p_ename varchar2 , p_job varchar2 , p_mgr number , p_hiredate date, p_sal number , p_comm number , p_deptno number ); procedure update_employee(p_empno number , p_ename varchar2 , p_job varchar2 , p_mgr number , p_hiredate date, p_sal number , p_comm number , p_deptno number ); procedure delete_employee(p_empno number ); end human_resources;
图例11：包头
图例12是包体：
View Code
create or replace package body human_resources as procedure select_employee(cur_employees out t_cursor) is begin open cur_employees for select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp; end select_employee; procedure insert_employee(p_empno number , p_ename varchar2 , p_job varchar2 , p_mgr number , p_hiredate date, p_sal number , p_comm number , p_deptno number ) is begin update emp set ename = p_ename, job = p_job, mgr = p_mgr, hiredate = p_hiredate, sal = p_sal, comm = p_comm, deptno = p_deptno where empno = p_empno; end insert_employee; procedure update_employee(p_empno number , p_ename varchar2 , p_job varchar2 , p_mgr number , p_hiredate date, p_sal number , p_comm number , p_deptno number ) is begin insert into emp values (p_empno,p_ename,p_job,p_mgr,p_hiredate,p_sal,p_comm,p_deptno); end update_employee; procedure delete_employee(p_empno number ) is begin delete from emp where empno = p_empno; end delete_employee; end human_resources;
图例12：包体
最终，让我们看看图例13中，对上面的存储过程进行调用的代码。从上图可以看到，如果要填充数据表，我们需要通过OracleCommand来定义增删查该操作并且将其关联到DataAdapter对象。下面例子的逻辑就是先填充数据表，然后打印出雇员数量，最后添加一行新的雇员数据进去。
View Code
using System; using System.Collections.Generic; using System.Text; using System.Data.OracleClient; using System.Data; namespace CallingOracleStoredProc { class Program { static void Main( string [] args) { using (OracleConnection objConn = new OracleConnection( " Data Source=ORCL; User ID=scott; Password=tiger " )) { OracleDataAdapter objAdapter = new OracleDataAdapter(); OracleCommand objSelectCmd = new OracleCommand(); objSelectCmd.Connection = objConn; objSelectCmd.CommandText = " human_resources.select_employee " ; objSelectCmd.CommandType = CommandType.StoredProcedure; objSelectCmd.Parameters.Add( " cur_employees " , OracleType.Cursor).Direction = ParameterDirection.Output; objAdapter.SelectCommand = objSelectCmd; OracleCommand objInsertCmd = new OracleCommand(); objInsertCmd.Connection = objConn; objInsertCmd.CommandText = " human_resources.insert_employee " ; objInsertCmd.CommandType = CommandType.StoredProcedure; objInsertCmd.Parameters.Add( " p_empno " , OracleType.Number, 4 , " empno " ); objInsertCmd.Parameters.Add( " p_ename " , OracleType.VarChar, 10 , " ename " ); objInsertCmd.Parameters.Add( " p_job " , OracleType.VarChar, 9 , " job " ); objInsertCmd.Parameters.Add( " p_mgr " , OracleType.Number, 4 , " mgr " ); objInsertCmd.Parameters.Add( " p_hiredate " , OracleType.DateTime, 12 , " hiredate " ); objInsertCmd.Parameters.Add( " p_sal " , OracleType.Number, 7 , " sal " ); objInsertCmd.Parameters.Add( " p_comm " , OracleType.Number, 7 , " comm " ); objInsertCmd.Parameters.Add( " p_deptno " , OracleType.Number, 7 , " deptno " ); objAdapter.InsertCommand = objInsertCmd; OracleCommand objUpdateCmd = new OracleCommand(); objUpdateCmd.Connection = objConn; objUpdateCmd.CommandText = " human_resources.update_employee " ; objUpdateCmd.CommandType = CommandType.StoredProcedure; objUpdateCmd.Parameters.Add( " p_empno " , OracleType.Number, 4 , " empno " ); objUpdateCmd.Parameters.Add( " p_ename " , OracleType.VarChar, 10 , " ename " ); objUpdateCmd.Parameters.Add( " p_job " , OracleType.VarChar, 9 , " job " ); objUpdateCmd.Parameters.Add( " p_mgr " , OracleType.Number, 4 , " mgr " ); objUpdateCmd.Parameters.Add( " p_hiredate " , OracleType.DateTime, 10 , " hiredate " ); objUpdateCmd.Parameters.Add( " p_sal " , OracleType.Number, 7 , " sal " ); objUpdateCmd.Parameters.Add( " p_comm " , OracleType.Number, 7 , " comm " ); objUpdateCmd.Parameters.Add( " p_deptno " , OracleType.Number, 7 , " deptno " ); objAdapter.UpdateCommand = objUpdateCmd; OracleCommand objDeleteCmd = new OracleCommand(); objDeleteCmd.Connection = objConn; objDeleteCmd.CommandText = " human_resources.delete_employee " ; objDeleteCmd.CommandType = CommandType.StoredProcedure; objDeleteCmd.Parameters.Add( " p_empno " , OracleType.Number, 4 , " empno " ); objAdapter.DeleteCommand = objDeleteCmd; try { DataTable dtEmp = new DataTable(); objAdapter.Fill(dtEmp); System.Console.WriteLine( " Employee count = {0} " , dtEmp.Rows.Count ); dtEmp.Rows.Add( 7935 , " John " , " Manager " , 7782 , DateTime.Now, 1300 , 0 , 10 ); objAdapter.Update(dtEmp); } catch (Exception ex) { System.Console.WriteLine( " Exception: {0} " ,ex.ToString()); } objConn.Close(); } } } }
图例12：程序代码
总结
本文中，我想大家阐述了如何利用Microsoft.net来操作oracle的存储过程。上面的例子几乎涵盖了。Net引用oracle存储过程的所有基本的操作。谢谢。
发表评论
最新留言
哈哈，博客排版真的漂亮呢~
[***.90.31.176]2025年04月10日 06时34分15秒
关于作者
喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！
-- 愿君每日到此一游！
推荐文章
Flask--简介
2019-03-11
16 python基础-恺撒密码
2019-03-11
Frame--Api框架
2019-03-11
Boostrap技能点整理之【网格系统】
2019-03-11
新闻发布项目——业务逻辑层（UserService）
2019-03-11
hibernate正向生成数据库表以及配置——hibernate.cfg.xml
2019-03-11
javaWeb服务详解（含源代码，测试通过，注释） ——Emp的Dao层
2019-03-11
java实现人脸识别源码【含测试效果图】——Dao层（IUserDao）
2019-03-11
使用ueditor实现多图片上传案例——前台数据层（Index.jsp）
2019-03-11
解决Chrome播放视频闪屏黑屏无法播放
2019-03-11
Git简单理解与使用
2019-03-11
echarts 基本图表开发小结
2019-03-11
二分查找.基于有序数组的查找方法.704
2019-03-11
制作JS验证码（简易）
2019-03-11
adb通过USB或wifi连接手机
2019-03-11
包装类
2019-03-11
JDK9-15新特性
2019-03-11
集合继承结构
2019-03-11
白红宇的个人博客 - 记录点点滴滴的事 - 您是第 461321981 位访客