ASP.NET调用oracle存储过程返回多个游标

利用oracle可以返回多个记录集。

首先创建程序包，再创建程序包主体。

利用存储过程返回多个记录集。

--创建包

create or replace package pack_siit is
 type t_cursor is ref cursor;--定义游标变量
 type t_scan is ref cursor;
 type t_send is ref cursor;
 type t_receive is ref cursor;
 type t_review is ref cursor;
 type t_locked is ref cursor;
 type t_confirm is ref cursor;
 procedure getFlowTrack
           (var_barcode in varchar2,
          cur_FirstTrial out t_cursor,
          cur_Scan out t_scan,
          cur_Send out t_send,
          cur_Receive out t_receive,
          cur_Review out t_review,
          cur_Locked out t_locked,
          cur_Confirm out t_confirm);
   end pack_siit;

--创建包主体

create or replace package body pack_siit is
  procedure getFlowTrack
    (var_barcode in varchar2,
    cur_FirstTrial out t_cursor,
    cur_Scan out t_scan,
    cur_Send out t_send,
    cur_Receive out t_receive,
    cur_Review out t_review,
    cur_Locked out t_locked,
    cur_Confirm out t_confirm)
    as
     begin
    open cur_FirstTrial for
     select  * from a where id=var_barcode;  
     -- return 1
     open cur_Scan for
     select  * from b where id=var_barcode;
     --2
     open cur_Send for
    select  * from c where id=var_barcode;
    --3
    open cur_Receive for
    select  * from d where id=var_barcode;
     --4
    open cur_Review for
    select  * from e where id=var_barcode;
     --5
    open cur_Confirm for
    select  * from f where id=var_barcode;
      --6
    open cur_Locked for
    select  * from g where id=var_barcode;

    end getFlowTrack;
   end pack_siit;

 

ASP.NET里调用

OracleConnection connection = new OracleConnection(connectionString);
                --连接
                connection.Open();
                OracleCommand command = new OracleCommand();
                command.Connection = connection;
                command.CommandText = "PACK_SIIT.getFlowTrack";--存储过程名
                command.CommandType = CommandType.StoredProcedure;--设置执行为存储过程
                command.Parameters.Add("var_barcode", OracleType.VarChar, 50);
                command.Parameters.Add("cur_FirstTrial", OracleType.Cursor).Direction = ParameterDirection.Output;
                command.Parameters.Add("cur_Scan", OracleType.Cursor).Direction = ParameterDirection.Output;
                command.Parameters.Add("cur_Send", OracleType.Cursor).Direction = ParameterDirection.Output;
                command.Parameters.Add("cur_Receive", OracleType.Cursor).Direction = ParameterDirection.Output;
                command.Parameters.Add("cur_Review", OracleType.Cursor).Direction = ParameterDirection.Output;
                command.Parameters.Add("cur_Confirm", OracleType.Cursor).Direction = ParameterDirection.Output;
                command.Parameters.Add("cur_Locked", OracleType.Cursor).Direction = ParameterDirection.Output;
                command.Parameters["var_barcode"].Value = stBarcode;// "DH448400001308120004CB1";
                OracleDataAdapter daReader = new OracleDataAdapter(command);
                DataSet ds = new DataSet();
                daReader.Fill(ds);

 

操作返回的DataSet

对DataSet里的7张表创建新列在赋值

DataSet setDs=null;

 if (ds.Tables[0].Rows.Count > 0)
                {
                    setDs.Tables.Add(ds.Tables[0].Copy());

                    DataColumn taColumn = new DataColumn("IMGTATUS", System.Type.GetType("System.String"));
                    setDs.Tables[0].Columns.Add(taColumn);
                    setDs.Tables[0].Rows[0]["IMGTATUS"] = "新列";
                    if (ds.Tables[1].Rows.Count > 0)
                    {
                        DataColumn taColumn1 = new DataColumn("IMGTATUS", System.Type.GetType("System.String"));
                        taColumn1.DefaultValue = "新列";
                        ds.Tables[1].Columns.Add(taColumn1);
                        setDs.Tables[0].ImportRow(ds.Tables[1].Rows[0]);
                    }
                    if (ds.Tables[2].Rows.Count > 0)
                    {
                        DataColumn taColumn2 = new DataColumn("IMGTATUS", System.Type.GetType("System.String"));
                        //taColumn2.DefaultValue = "新列";
                        ds.Tables[2].Columns.Add(taColumn2);
                        for (int i = 0, length = ds.Tables[2].Rows.Count; i < length; i++)
                        {
                           
                            if (i > 0)
                            {
                                ds.Tables[2].Rows[i]["IMGTATUS"] = "值1";
                            }
                            else
                            {
                                ds.Tables[2].Rows[i]["IMGTATUS"] = "值2";
                            }
                            setDs.Tables[0].ImportRow(ds.Tables[2].Rows[i]);
                        }
                    }

                    ..........等等

        return setDs;

}

得到DataSet后在排序,绑定在GridView上

                    DataView view = ds.Tables[0].DefaultView;
                        view.Sort = "CREATE_TIME ASC";//根据列排序
                        gridView.DataSource = view;
                        gridView.DataBind();