32.C#调用Oracle的存储过程和函数

一、引言        

        在前面的文章中我们分别讲解了Oracle中存储过程和存储函数创建的方法，本文主要介绍在C#中调用之前创建的存储过程和函数的方法。

二、C#调用带参的存储过程

        首先，假设我们有以下数据表emp:

      并创建了存储过程insert_emp:

create or replace 
procedure insert_emp(v_empno emp.empno%type,v_ename emp.ename%type,v_job emp.job%type,v_mgr emp.mgr%type,v_sal emp.sal%type,v_deptno emp.deptno%type)
as 
 v_count number;
begin
  select count(*) into v_count from emp where empno=v_empno;
  if v_count>0 then
   raise_application_error(-20789,'增加失败，该部门已经存在');
  else
   insert into emp values(v_empno,v_ename,v_job,v_mgr,sysdate,v_sal,null,v_deptno);
  end if;
  commit;
exception
  when others then
      dbms_output.put_line('sqlerrm='||sqlerrm);
      rollback;
end;

        我们的目标是在C#程序中调用该存储过程插入一条人员信息,我们创建一个OracleManager的类，用它来管理数据库的连接和SQL语句的执行等等，

 public class OracleManager
    {
        static private OracleConnection conn;
        private OracleManager()
        {
            string connStr = "User Id=scott;Password=123456;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.5.201)(PORT=1523)))(CONNECT_DATA=(SERVICE_NAME=ORCL)))";
            conn = new OracleConnection(connStr);
        }


        /// <summary>
        /// 单例模式
        /// </summary>
        static public OracleManager Instance = new OracleManager();


        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void OpenDb()
        {
            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
            }
            catch (OracleException ex)
            {
                //MessageBox.Show(ex.Message);
            }
        }


        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void CloseDb()
        {
            try
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
            catch (System.Exception ex)
            {
                //MessageBox.Show(ex.Message);
            }
        }
}

        在上面的代码中，我们创建了一个OracleManager的类，并对其进行了单例化，在构造函数中定义了数据库连接信息，然后分别定义了打开和关闭数据库连接的方法OpenDb和CloseDb，接下来我们定义一个函数Callproc来调用数据库的存储过程：

public void CallProc(int empno, string ename, string job, int mgr, int sal, int deptno)
        {
            try
            {
                OracleCommand orc = conn.CreateCommand();
                orc.CommandType = CommandType.StoredProcedure;
                orc.CommandText = "INSERT_EMP";
                orc.Parameters.Add("v_empno", OracleDbType.Int32).Direction = ParameterDirection.Input;
                orc.Parameters["v_empno"].Value = empno;
                orc.Parameters.Add("v_ename", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
                orc.Parameters["v_ename"].Value = ename;
                orc.Parameters.Add("v_job", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
                orc.Parameters["v_job"].Value = job;
                orc.Parameters.Add("v_mgr", OracleDbType.Int32).Direction = ParameterDirection.Input;
                orc.Parameters["v_mgr"].Value = mgr;
                orc.Parameters.Add("v_sal", OracleDbType.Int32).Direction = ParameterDirection.Input;
                orc.Parameters["v_sal"].Value = sal;
                orc.Parameters.Add("v_deptno", OracleDbType.Int32).Direction = ParameterDirection.Input;
                orc.Parameters["v_deptno"].Value = deptno;
                orc.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
            	
            }
        }

        在CallProc中我们使用数据库连接对象conn创建了一个OracleCommand对象orc，该对象用来进行调用存储过程的相关设置：比如在其CommandText字段中设置过程名，在其Parameters字段中添加存储过程所需的参数等等。我们调用该方法：

            OracleManager.Instance.OpenDb();
            OracleManager.Instance.CallProc(7600, "Hyman", "SalesMan", 7698, 3000, 30);
            OracleManager.Instance.CloseDb();

        在emp表中成功插入了该条数据：

三、C#调用存储函数

        C#中调用存储函数和调用存储过程类似，如下:

 public void CallFunc(int empno)
        {
            try
            {
                OracleParameter[] parameters = {
                   new OracleParameter("ReturnValue", OracleDbType.Int32, 0, ParameterDirection.ReturnValue, true, 0, 0, "",DataRowVersion.Default, Convert.DBNull ),
                   new OracleParameter("v_empno", OracleDbType.Int32,1)
                };
                parameters[1].Value = empno;
                OracleCommand orc = conn.CreateCommand();
                orc.CommandType = CommandType.StoredProcedure;
                orc.CommandText = "GET_INCOME";
                foreach (OracleParameter param in parameters)
                    orc.Parameters.Add(param);
                orc.ExecuteNonQuery();
                string returnValue = parameters[0].Value.ToString();
            }
            catch (System.Exception ex)
            {

            }
        }

        可以看到，两者唯一的不同点在于传参时，调用存储函数的参数列表第一个参数要存放调用的函数的返回值，其他的过程基本类似。另外需要特别注意的是每次调用的过程或者函数的名称必须要大写，否则会提示无法找到存储过程的错误。。。。