C # calls Oracle stored procedures return result sets and functions

oracle| Stored Procedures | 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 VARCHAR2)
return VARCHAR2;
End
/
Description: In fact, package is just a statement. Here we define a stored procedure to return 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 varchar2)
return VARCHAR2
Is
Str_temp varchar2 (MB): = ' 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 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.