DotNet操作Oracle存储过程备忘

最新推荐文章于 2024-08-18 10:54:05 发布

weixin_30580943

最新推荐文章于 2024-08-18 10:54:05 发布

阅读量53

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/scy251147/archive/2011/02/13/1953136.html

版权

简介

本文主要介绍如何利用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

图例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存储过程的所有基本的操作。谢谢。

转载于:https://www.cnblogs.com/scy251147/archive/2011/02/13/1953136.html

weixin_30580943

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
DotNet操作Oracle存储过程备忘

简介本文主要介绍如何利用Microsoft.net中提供的oracle支持以及System.Data.OracleClient命名空间下的方法来调用Oracle的存储过程。我尽量用一些经常用到的例子来讲解。执行存储过程让我们先从定义开始。首先，存储过程是一个能够处理一个或者多个动作的模块。而函数也是一个模块，但是他有返回值。这点不像存储过程。函数只是在执行的时候才会被调用，比...
复制链接

扫一扫