Oracle: One database connection, returns multiple result sets

Gooliugle Oracle: One database connection, returns multiple result sets (with parameters)!

1. Set up Package specification

Create or Replace package QX_GDJTJ  is -- Author  :  xxx--created:2012-1-1  -- Purpose: Statistical main equipment Defect  TYPE t_cursor is REF CURSOR; PROCEDURE GETGDJQXTJ (    in  varchar,    cur_gdjqxtj1 out T_cursor,    cur_gdjqxtj2 out T_ CURSOR,    cur_gdjqxtj3 out t_cursor); end QX_GDJTJ;

2. Set up the package body

Create or Replace package body QX_GDJTJ isPROCEDURE getgdjqxtj (cur_idinchvarchar, cur_gdjqxtj1 out t_cursor, cur_gdjqxtj2 off T_cursor, cur_gdjqxtj3 out T_cursor) Isbeginopen cur_gdjqxtj1 forSelect(SelectCount0) fromHvm_view_qxwhereVoltage='500kV'and gdjid=cur_id)-(SelectCount0) fromHvm_view_qxwhereVoltage='500kV'and gdjid=cur_id and CLJG like'% Qualifying%'and CLJG not like'% unqualified%') fromDual;open cur_gdjqxtj2 forSelect(SelectCount0) fromHvm_view_qxwhereVoltage='220kV'and gdjid=cur_id)-(SelectCount0) fromHvm_view_qxwhereVoltage='220kV'and gdjid=cur_id and CLJG like'% Qualifying%'and CLJG not like'% unqualified%') fromDual;open cur_gdjqxtj3 forSelect(SelectCount0) fromHvm_view_qxwhereVoltage='110kV'and gdjid=cur_id)-(SelectCount0) fromHvm_view_qxwhereVoltage='110kV'and gdjid=cur_id and CLJG like'% Qualifying%'and CLJG not like'% unqualified%') fromdual;end getgdjqxtj;end qx_gdjtj;

3. C # call, return result set

 Public StaticDataSet GetDataSet (stringGdjid,stringProname,string[] cursors) {OracleConnection Conn=Getconn (); DataSet DS=NewDataSet (); Try{oraclecommand cmd=NewOracleCommand (); Cmd. Connection=Conn; Cmd.commandtext=Proname; Cmd.commandtype=CommandType.StoredProcedure; Cmd. Parameters.Add ("cur_id", Oracletype.varchar). Value =Gdjid;  for(inti =0; I < cursors. Length; i++) {cmd. Parameters.Add (Cursors[i], oracletype.cursor). Direction=ParameterDirection.Output; } OracleDataAdapter da=NewOracleDataAdapter (CMD); Da.    Fill (DS); }    Catch(System.Data.OracleClient.OracleException ex) {Throw NewException (ex.    Message); }    finally{conn.close (); }    returnds;}

Oracle: One database connection, returns multiple result sets