Oracle stored procedure returns result set

Oracle stored procedure Returns a result set that requires a cursor as a stored procedure parameter

1. Create Pakage

CREATE OR REPLACE Package pkg_query as TYPE cur_query is REF CURSOR; END Pkg_query;

2. Create a stored procedure ()

Create or Replace proceduresp_students (P_nameinch  varchar2,--name (incoming parameter, filter data by name)P_totalrecords out Number,--Total Records (returns the total number of bars in the result set)V_cur out Pkg_query.cur_query--The returned result set        )    isV_sqlVARCHAR2( -) := "';--SQL statementsBEGINV_sql:='SELECT To_number (COUNT (1)) from Tb_students t where T.name=" "||P_name||" " ' ; EXECUTEIMMEDIATE V_sql intop_totalrecords; V_sql:= 'SELECT * from Tb_students t where T.name=" "||P_name||" " ' ; --Dbms_output.put_line (v_sql);   OPENV_cur forV_sql;ENDsp_students;

3.c# Calling stored Procedures

        /// <summary>        ///return result set/// </summary>        /// <param name= "name" >stored procedure passed in parameters for filtering data</param>        /// <param name= "Count" >the stored procedure returns a parameter that returns the total number of filtered data bars</param>        /// <returns></returns>         PublicDataSet Runprocedure (stringName out intcount) {Count=0;
String connectionString = "Data source=orcl; User id= username; password= password;            Unicode=true "; using(OracleConnection connection =NewOracleConnection (connectionString)) {DataSet DataSet=NewDataSet (); Connection.                Open (); OracleDataAdapter SqlDA=NewOracleDataAdapter ("sp_students", connection); Oracleparameter[] Paras={                                NewOracleParameter ("P_name", name),NewOracleParameter ("P_totalrecords", Count),NewOracleParameter ("V_cur", OracleType.Cursor)//the returned cursor                                                         }; paras[1]. Direction =ParameterDirection.Output; paras[2]. Direction =ParameterDirection.Output;                SqlDA.SelectCommand.Parameters.AddRange (paras); SqlDA.SelectCommand.CommandType= CommandType.StoredProcedure;//setting up using Stored proceduresSqlda.fill (DataSet); Connection.                Close (); Count= Convert.ToInt32 (paras[1].                Value); returnDataSet; }        }

4. Test the stored procedure

4.1 Create a table:

--Create TableCreate Tabletb_students (ID NVARCHAR2 ( +)defaultSys_guid () not NULL, NAME NVARCHAR2 ( +), age Number);Commit;--inserting test DataInsert  intotb_students (ID, NAME, age)Values('De3a7d4d5d054992b40761bfa2391f5','Zhang San', -);Insert  intotb_students (ID, NAME, age)Values('137acf3d6c0a4be0b71423a1f7d8452','John Doe', A);Insert  intotb_students (ID, NAME, age)Values('2af70a53bcd348ee9e8ff03916d018f','Harry', -);Insert  intotb_students (ID, NAME, age)Values('2877652AE0A3408A981D8980AE833CF',' -', -);Insert  intotb_students (ID, NAME, age)Values('7b356a73b80c4ed0bfafa1dd96256e0',' -', A);Insert  intotb_students (ID, NAME, age)Values('1ff0a99b47ff4056bbda7e3b4262c61',' -', -);Commit;

4.2 C # Calls:

            NULL ;             int count;             = Runprocedure ("+", out count);             if NULL 0 )            {                = ds. tables[0];            }

Oracle stored procedure returns result set