.Net中调用Oracle存储过程

项目.Net MVC5的，在这里面调用了Oracle的存储过程，在这做个记录：

1、Oracle中的存储过程

create or replace procedure PROC_HISTASKBYUSERID(curruserid in varchar2,queryuserid in varchar2,typeNum in integer, startNum in integer,endNum in integer,datestart in date,dateend in date,return_cur1 out sys_refcursor)
is
begin
  --可以使用if..then或者elsif..then或者else
  if (typeNum = '109') then
    open return_cur1 for select * from(
    --你的查询数据代码
 );
  else
    open return_cur1 for select * from(
    --你的查询数据代码    
 );
  end if;
  
 end;

上面的代码是返回游标的一种存储过程，in 参数代表传入的数据，比如 curruserid in varchar2 就是程序传入的一个字符串类型的数据：curruserid；在上面的代码中，返回数据使用“open return_cur1 for...”，其中的return_cur1也是定义的游标类型，比如上面定义为：

return_cur1 out sys_refcursor

在存储过程里面完全可以使用if elsif else判断语句。

2、.Net调用存储过程

下面是一些存储过程操作的方法：

 #region 存储过程操作

        /// <summary>
        /// 执行存储过程 返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>OracleDataReader</returns>
        public static OracleDataReader RunProcedure( string storedProcName, IDataParameter[] parameters)
        {
            OracleConnection connection = new OracleConnection(connectionString);
            OracleDataReader returnReader;
            connection.Open();
            OracleCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            return returnReader;
        }


        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                OracleDataAdapter sqlDA = new OracleDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }


        /// <summary>
        /// 构建 OracleCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>OracleCommand</returns>
        private static OracleCommand BuildQueryCommand(OracleConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            OracleCommand command = new OracleCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (OracleParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return command;
        }

        /// <summary>
        /// 执行存储过程，返回影响的行数		
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public static int RunProcedure( string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                int result;
                connection.Open();
                OracleCommand command = BuildIntCommand(connection, storedProcName, parameters);
                rowsAffected = command.ExecuteNonQuery();
                result = (int)command.Parameters["ReturnValue"].Value;
                //Connection.Close();
                return result;
            }
        }

        /// <summary>
        /// 创建 OracleCommand 对象实例(用来返回一个整数值)	
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>OracleCommand 对象实例</returns>
        private static OracleCommand BuildIntCommand(OracleConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            OracleCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.Parameters.Add(new OracleParameter("ReturnValue",
                OracleDbType.Int32, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }
        /// <summary>
        ///  执行存储过程，不返回任何值
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="parameters"></param>
        public static void RunProcedureNoReturn(string storedProcName, IDataParameter[] parameters)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                connection.Open();
                OracleCommand command = BuildQueryCommand(connection, storedProcName, parameters);
                command.ExecuteNonQuery();
                connection.Close();
            }
        }
        #endregion

上面的方法只是对存储过程的一些底层操作，具体的代码还得我们自己写，比如下面使用上面的一个方法RunProcedure()来执行本文中提到的这个oracle存储过程。代码如下：

public DataTable PROC_queryDatalist( int startNum,int endNum,string userid, string curruserid,int typeNum,DateTime d1,DateTime d2)
        {
            IDataParameter[] ip = new IDataParameter[8];
            ip[0] = new OracleParameter("@curruserid", curruserid);
            ip[1] = new OracleParameter("@queryuserid", userid);
            ip[2] = new OracleParameter("@typeNum", typeNum);
            ip[3] = new OracleParameter("@startNum", startNum);
            ip[4] = new OracleParameter("@endNum", endNum);
            ip[5] = new OracleParameter("@datestart", d1);
            ip[6] = new OracleParameter("@dateend", d2);
            ip[7] = new OracleParameter("@return_cur1", OracleDbType.RefCursor, ParameterDirection.Output);
            DataSet ds = DBHelperOra.RunProcedure("PROC_HISTASKBYUSERID", ip, "DataTable");
            return ds.Tables[0];
        }

DBHelperOra.RunProcedure("PROC_HISTASKBYUSERID", ip, "DataTable");返回的是一个DataSet，只是在这里把它转成了DataTable而已，在上面的这个代码当中，定义了一个

IDataParameter[] ip = new IDataParameter[8];

这里面的数据是传入存储过程的

ip[0] = new OracleParameter("@curruserid", curruserid);

里卖你的“@curruserid”与你在存储过程中定义的变量是一一对应的，在你的存储过程中定义了几个变量，你这就应该传入几个值，名字一定相同额，对于定义的返回类型像上面的代码中那么定义：

 ip[7] = new OracleParameter("@return_cur1", OracleDbType.RefCursor, ParameterDirection.Output);

有几个就定义几个。

还有在调用的时候，存储过程的名字得和oracle存储过程的名字一致，例如下面的代码：

 DataSet ds = DBHelperOra.RunProcedure("PROC_HISTASKBYUSERID", ip, "DataTable");

“PROC_HISTASKBYUSERID”就是你再数据库中定义的存储过程的名字，ip就是你上面的定义参数数组，至于后面"DataTable"只是返回的DataSet的一个名字而已，这里你随意。。。