C # calls Oracle stored procedures return result sets and functions

Oracle segment:

First, the package and package body are established in Oracle, where functions and stored procedures are defined to return the result set.

1: Establish package:

CREATE OR REPLACE package SCOTT.pk_wt
is
type mytype is ref cursor;
procedure p_wt( mycs out mytype );
function f_get( str in var
char2 )
return var
char2;
end;/

Description: In fact, package is just a statement. Here we define a stored procedure that returns a compilation and a function that returns a string. 2: Establish package body:

CREATE OR REPLACE package BODY SCOTT.pk_wt
is
procedure p_wt( mycs out mytype )
is
begin
open mycs for select * from test;
end p_wt;
function f_get( str var
char2 )
return var
char2
is
str_temp var
char2( 100 ) := 'good luck!';
begin
str_temp := str_temp || str;
return str_temp;
end f_get;
end pk_wt;/

Description: Here the establishment of package body is the specific description and use, will be adopted in what way to achieve. C # section: In C #, the code is divided into two parts, one using the function and the other part using the result set.

Define a connection and get it from the Webconfig:

private OracleConnection orcn=new OracleConnection( System.Configuration.ConfigurationSettings.AppSettings["scott"] );

C # calls Oracle functions:

OracleCommand cmd=new OracleCommand( "pk_wt.f_get",orcn );
cmd.CommandType=CommandType.StoredProcedure;
OracleParameter p1=new OracleParameter( "str",OracleType.VarChar,10 );
p1.Direction=System.Data.ParameterDirection.Input;
p1.Value=
this.TextBox1.Text;
OracleParameter p2=new OracleParameter( "result",OracleType.VarChar,100 );
p2.Direction=System.Data.ParameterDirection.ReturnValue;
cmd.Parameters.Add( p1 );
cmd.Parameters.Add( p2 );
orcn.Open( );
cmd.ExecuteNonQuery( );
orcn.Close( );
this.Button_function.Text=p2.Value.ToString( );

Result is a system-defined function return variable, notably, the return type of the function's arguments is specified, and the command type needs to be specified, in addition to the normal stored procedure. C # calls Oracle to return the result set:

OracleCommand cmd=new OracleCommand( "pk_wt.p_wt",orcn );
cmd.CommandType=CommandType.StoredProcedure;
OracleParameter p1=new OracleParameter( "mycs",OracleType.Cursor );
p1.Direction=System.Data.ParameterDirection.Output;
cmd.Parameters.Add( p1 );
OracleDataAdapter da=new OracleDataAdapter( cmd );
DataSet ds=new DataSet( );
da.Fill( ds,"test" );
this.DataGrid1.DataSource=ds;
this.DataGrid1.DataBind( );

There is nothing to say about this class. Just the data type defined is the cursor, the type is output, and nothing else.

Personal Summary:

Oracle's package and package body will be our only way to return to the compilation and use of functions, which must be studied well.