c#调用oracle存储过程返回数据集

2008-12-20 10:59:57|  分类： net|字号 订阅

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");
        }