简介

本文主要介绍如何利用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存储过程的所有基本的操作。谢谢。

posted on 2011-02-13 14:00  程序诗人  阅读(623)  评论(0编辑  收藏  举报