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)