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