存储过程和调用问题

2009-07-14 11:30:16 90 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Xilinx FPGA 10G Ethernet Subsystem example The 10G Ethernet subsystem provides 10 Gb/s Ethernet MAC, Physical Coding Sublayer(PCS) and Physical Medium Attachment (PMA) transmit and receive functionality over anAXI4-Stream interface. 阅读详情

数据库是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#调用，获取返回这个数据集，怎么实现？

...全文

大语言模型在构建UNSPSC 分类数据中的应用 UNSPSC 是联合国标准产品和服务代码。本文研究如何利用大语言模型生成分类系统的分析阅读详情

handsome0916

C# 111,129 成员 642,540 内容

+ 关注 + 加入社区

切换为时间正序

hecker728 2009-07-14

打赏
举报

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





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();

        }

    }

}

ljhcy99 2009-07-14

打赏
举报

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

yudi010 2009-07-14

打赏
举报

DAO类调用
你的sql有问题

存储过程和调用问题

相关推荐