Oracle存储过程返回select * from table结果

来源：互联网发布：复杂网络的建模编辑：程序博客网时间：2024/06/08 06:44

1.首先建立一个包

create or replace package LogOperation is  type listLog is ref cursor;  procedure PCenterExamine_sel(listCenterExamine out listlog,testlist out listLog,numpage in decimal);end;

2.建立包中的主体

create or replace package body LogOperation is  procedure PCenterExamine_sel  (    listCenterExamine out listlog,    testlist out listlog,    numpage in decimal  )   as  begin    open listCenterExamine for select * from Log_CenterExamine;    open testlist for select * from Log_CenterExamine;  end;end;

3.在程序中调用存储过程的值

        public static DataSet RunProcedureGetDataSet(string storedProcName, OracleParameter[] parameters)        {            string connectionString ="192.168.1.1/db";            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, "dt");                connection.Close();                return dataSet;            }        }

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

4.有几个out的ref cursor，变量ds中就用几个DataTable。并且输入参数 indecimal也不会受影响，并且可以参加存储过程的运算

                OracleParameter[] paramDic = {                     new OracleParameter("listCenterExamine",OracleType.Cursor),                    new OracleParameter("testlist",OracleType.Cursor),                    new OracleParameter("numpage",OracleType.Int32)};                paramDic[0].Direction = ParameterDirection.Output;                paramDic[1].Direction = ParameterDirection.Output;                paramDic[2].Value = 1;                ds = Model.OracleHelper.RunProcedureGetDataSet("LogOperation.PCenterExamine_sel", paramDic);

0 0