Oracle creates a stored procedure and returns a result set (with C # calling code)

The most common use of stored procedures is to query a data table and return a result set.

In SQL SERVER, this type of operation is easiest and can be done with a simple select * from XX. But this is not supported in Oracle, so how do we implement the same functionality as SQL Server? And look at the following code:

Create or Replace proceduresp_getdept (Rep_typeinch varchar2, SELinch varchar2, result out sys_refcursor) asseqvarchar2( +); Infovarchar2( +);begin    ifRep_type= '1'  Then          OpenResult for Select *  fromHelp ; End IF; ifRep_type= '2'  Then        SelectSeq,info intoSeq,info fromHelpwhereRowNum=1; End if;End;

As you can see in the code, Oracle returns a structure set in table format through the cursor Sys_refcursor implementation. Note that the definition method result out Sys_refcursor is somewhat similar to the out parameter type in C #.

Sys_refcursor and cursor comparison:

Sys_refcursor cannot be manipulated with Open,close, Fetch. Can be used as a parameter.

Cursor can be used open,close, fetch operation. cannot be used as a parameter.

Here's the code for C # calling the previous stored procedure:

OracleConnection con =NewOracleConnection ("Password=manager; User id=system;data source= (description= (address= (protocol=tcp) (host=127.0.0.1)) (port=1521 ( (SERVICE_NAME=ORCL) ));"); OracleCommand cmd=NewOracleCommand ("sp_getdept", con); Cmd.commandtype=CommandType.StoredProcedure; OracleParameter P0=NewOracleParameter ("Rep_type", Oracletype.varchar); P0. Direction=ParameterDirection.Input; Cmd.            Parameters.Add (P0); Cmd. parameters["Rep_type"]. Value ="2"; OracleParameter P1=NewOracleParameter ("result", OracleType.Cursor); P1. Direction=System.Data.ParameterDirection.Output; Cmd.            Parameters.Add (p1); OracleParameter P2=NewOracleParameter ("sel", Oracletype.varchar); P0. Direction=ParameterDirection.Input; Cmd.            Parameters.Add (p2); Cmd. parameters["sel"]. Value ="1"; OracleDataAdapter da=NewOracleDataAdapter (CMD); DataSet DS=NewDataSet (); Da.            Fill (DS); Console.WriteLine (ds. tables[0]. rows[5][0].            ToString ()); Console.ReadLine ();

Hope to be of help to you. ^_^.

Oracle creates a stored procedure and returns a result set (with C # calling code)