存储过程和调用问题

[handsome0916]

数据库是oracle,目的是返回一个数据集.

CREATE OR REPLACE PACKAGE PKG_JCCTEST1
AS
type rc_class is ref cursor;
procedure GetTableTt(p1 number,p2 number ,
ResultCursor out rc_class);

end PKG_JCCTEST1;

CREATE OR REPLACE PACKAGE BODY PKG_JCCTEST1
AS
procedure GetTableTt(p1 number,p2 number ,
ResultCursor out rc_class)
IS
BEGIN
open ResultCursor for
select p1+p2 as "sum", p1-p2 as "sub" from T_TYPE;
END ;


这段脚本报错，应该怎么搞。


假如我要用C#调用，获取返回这个数据集，怎么实现？

[yudi010]

DAO类调用
你的sql有问题

[ljhcy99]

select p1+p2 as [sum], p1-p2 as sub from T_TYPE;

假如我要用C#调用，获取返回这个数据集，怎么实现？

using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand command = new OracleCommand("PKG_JCCTEST1");
command.CommandType = CommandType.StoredProcedure;
command.Connection = connection;
try
{
connection.Open();
OracleDataReader read =command.ExecuteReader();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}

[hecker728]

数据库访问类，可以执行存储过程，参考下

namespace CommLB.wmj.AllDataProdivers.Command
{
    /// <summary>
    /// OracleDataProvider 的摘要说明
    /// </summary>
    internal class OracleDataProvider : CommLB.wmj.AllDataProdivers.CommandOfInterface.IDataProvider
    {
        private System.Data.OracleClient.OracleConnection oracleConnection;
        private System.Data.OracleClient.OracleCommand oracleCommand;
        private string connectionString;
        public OracleDataProvider()
            : this(null)
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }

        public OracleDataProvider(string connectionString)
        {
            if (connectionString == null || connectionString.Trim() == string.Empty)
            {
                System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader();
                this.connectionString = (string)(configurationAppSettings.GetValue("oracleConnectionString", typeof(string)));
            }
            else
            {
                this.connectionString = connectionString;
            }
        }

        /// <summary>
        /// 执行Sql数组语句查询，并将查询返回的结果作为一个数据集返回
        /// </summary>
        /// <param name="sql">Select 语句数组</param>
        /// <param name="tableName">TableName</param>
        /// <returns>数据集 DataSet</returns>
        public DataSet RetriveDataSet(string[] sqls, params string[] tableNames)
        {
            if (sqls == null || sqls.Length == 0)
            {
                throw new Exception("抱歉，SQL 语句为空...");
            }
            int sqlLength;
            sqlLength = sqls.Length;
            using (oracleConnection = this.GetOracleConnection())
            {
                if (oracleConnection == null)
                    return null;
                DataSet ds = new DataSet();
                int tableNameLength = tableNames.Length;
                for (int i = 0; i < sqlLength; i++)
                {
                    using (OracleDataAdapter da = new OracleDataAdapter(sqls[i], oracleConnection))
                    {
                        try
                        {
                            if (i < tableNameLength)
                                da.Fill(ds, tableNames[i]);
                            else
                                da.Fill(ds, "table" + i);
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                    }
                }
                return ds;
            }
        }

        public DataSet RetriveDataSet(string[,] sql_tableNames)
        {
            if (sql_tableNames.GetLength(0) != 2)
            {
                throw new Exception("抱歉，SQL语句-表名 参数必须为二维数组...");
            }
            int length = sql_tableNames.GetLength(1);
            if (length <= 0)
            {
                throw new Exception("抱歉，SQL语句-表名 为空...");
            }

            string[] sqls = new string[length];
            string[] tableNames = new string[length];
            for (int i = 0; i < length; i++)
            {
                sqls[i] = sql_tableNames[i, 0];
                tableNames[i] = sql_tableNames[i, 1];
            }

            return RetriveDataSet(sqls, tableNames);
        }

        public bool ExecuteTransaction(string[] sqls, object[][] myParams)
        {
            throw new Exception("抱歉，未实现...");
        }

        /// <summary>
        ///  执行Sql数组语句查询，并将查询返回的结果作为一个数据读取器返回
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>OracleDataReader</returns>
        public OracleDataReader RetriveDataReader(string sql)
        {
            if (sql == null || sql == string.Empty)
            {
                throw new Exception("抱歉，SQL 语句为空...");
            }
            using (oracleConnection = this.GetOracleConnection())
            {
                if (oracleConnection == null)
                    return null;
                using (oracleCommand = new OracleCommand(sql, oracleConnection))
                {
                    try
                    {
                        OracleDataReader oracleDataReader = oracleCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                        return oracleDataReader;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
            }
        }

        /// <summary>
        /// 执行一个查询式的存贮过程,返回得到的数据集
        /// </summary>
        /// <param name="proceName">存贮过程名称</param>
        /// <param name="myParams">所有属性值</param>
        /// <returns></returns>
        public DataSet ExecStoredProcedure(string proceName, object[] myParams)
        {
            if (proceName == null || proceName == string.Empty)
            {
                throw new Exception("抱歉，存贮过程名称为空...");
            }

            using (oracleConnection = this.GetOracleConnection())
            {
                if (oracleConnection == null)
                    return null;

                DataSet ds = new DataSet();

                try
                {
                    if (oracleConnection.State == System.Data.ConnectionState.Closed)
                        oracleConnection.Open();
                    oracleCommand = oracleConnection.CreateCommand();
                    oracleCommand.CommandType = CommandType.StoredProcedure;
                    oracleCommand.CommandText = proceName;
                    if (myParams != null)
                    {
                        for (int i = 0; i < myParams.Length; i++)
                            oracleCommand.Parameters.Add((OracleParameter)myParams[i]);
                    }
                    using (OracleDataAdapter da = new OracleDataAdapter(oracleCommand))
                    {
                        int returnValue = da.Fill(ds);
                        if (returnValue < 0)
                            throw new Exception("存储过程执行错误:" + (returnValue >= -14 ?
                                ((StoreProcReturn)returnValue).ToString() : "ErrCode:" + returnValue));
                        return ds;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 执行一个非查询式的存贮过程
        /// </summary>
        /// <param name="proceName">存贮过程名称</param>
        /// <param name="myParams">所有属性值</param>
        /// <returns>存储过程return值</returns>
        public int ExecNonQueryStoredProcedure(string proceName, ref object[] myParams)
        {
            if (proceName == null || proceName == string.Empty)
            {
                throw new Exception("抱歉，存贮过程名称为空...");
            }

            using (oracleConnection = this.GetOracleConnection())
            {
                if (oracleConnection == null)
                    throw new Exception("抱歉，数据库连接没有初始化...");

                try
                {
                    if (oracleConnection.State == System.Data.ConnectionState.Closed)
                        oracleConnection.Open();
                    oracleCommand = oracleConnection.CreateCommand();
                    oracleCommand.CommandType = CommandType.StoredProcedure;
                    oracleCommand.CommandText = proceName;
                    if (myParams != null)
                    {
                        for (int i = 0; i < myParams.Length; i++)
                            oracleCommand.Parameters.Add((OracleParameter)myParams[i]);
                    }
                    int returnValue = oracleCommand.ExecuteNonQuery();
                    if (returnValue < 0)
                        throw new Exception("存储过程执行错误:" + (returnValue >= -14 ?
                            ((StoreProcReturn)returnValue).ToString() : "ErrCode:" + returnValue));
                    return returnValue;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        public void Dispose()
        {
            this.connectionString = null;
            if (this.oracleCommand != null)
                this.oracleCommand.Dispose();
            if (this.oracleConnection != null)
                this.oracleConnection.Dispose();
        }
    }
}