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