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

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