DotNet操作Oracle存储过程备忘

简介

本文主要介绍如何利用Microsoft.net中提供的oracle支持以及System.Data.OracleClient命名空间下的方法来调用Oracle的存储过程。我尽量用一些经常用到的例子来讲解。

执行存储过程

     让我们先从定义开始。首先，存储过程是一个能够处理一个或者多个动作的模块。而函数也是一个模块，但是他有返回值。这点不像存储过程。函数只是在执行的时候才会被调用，比如说表达式中的一个元素或者是默认的变量。

     下面的第一个例子展示的是如何通过输入参数和输出参数来调用oracle的存储过程的。下面所有的示例中，我们将采用oracle默认的数据库ORCL，图例1中的代码向我们展示了如何创建一个名称为count_emp_by_dept的带有输入部门数字的参数，同时带有输出部门里面员工人数的参数的存储过程：

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方法调用存储过程即可。

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的函数，它接收部门编号的参数，返回部门人数。这和前面的存储过程非常类似。

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就是调用函数的代码。从图示可以看到，我们需要定义一个返回参数去得到返回值。除此之外，代码部分和调用存储过程的代码几乎相同。

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中定义一个包以及一个存储过程头。

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是包体的定义：

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方法即可。

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展示了如何创建一个包头：

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则展示了创建包体的方法：

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来看看代码处理方式：

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展示了创建的包头：

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是包体：

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对象。下面例子的逻辑就是先填充数据表，然后打印出雇员数量，最后添加一行新的雇员数据进去。

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