c#调用oracle存储过程

CREATE OR REPLACE PACKAGE pkg_tableType  IS       type Tabletype is ref cursor;       PROCEDURE SP_CPZD        (        CPNO IN VARCHAR2,        STATUS IN VARCHAR2,        t_sql out Tabletype        );  END;  CREATE OR REPLACE package BODY pkg_tableType  is  PROCEDURE SP_CPZD  (  CPNO IN VARCHAR2,  STATUS IN VARCHAR2,  t_sql out Tabletype  )  IS  BEGIN          OPEN t_sql FOR             select *  from ball b where b.no=CPNO  and  b.type =STATUS ;    END SP_CPZD;  END pkg_tableType;  #region 存储过程操作       /// <summary>    /// 执行存储过程    /// </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();         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",      OracleType.Int32,4,ParameterDirection.ReturnValue,      false,0,0,string.Empty,DataRowVersion.Default,null ));     return command;    }      #endregion             public DataSet GetCPResult(string s_aN_CPNO)          {              OracleParameter[] parameter = {                   new OracleParameter("CPNO",OracleType.VarChar,100),                  new OracleParameter("STATUS",OracleType.VarChar,100),                  new OracleParameter("t_sql", OracleType.Cursor)};              parameter[0].Value = s_aN_CPNO;              parameter[1].Value = "3";              parameter[2].Direction = System.Data.ParameterDirection.Output;              return RunProcedure("pkg_tabletype.sp_cpzd", parameter, "ds");          }