C # invoke Oracle stored procedure sample code

--Build a table
CREATE TABLE Sale_report (
Sale_date date not NULL,
Sale_item VARCHAR (2) Not NULL,
Sale_money DECIMAL (10,2) is not NULL,
PRIMARY KEY (sale_date, Sale_item)
);

--Test data
DECLARE
V_begin_day DATE;
V_end_day DATE;
BEGIN
V_begin_day: = to_date (' 2009-01-01 ', ' yyyy-mm-dd ');
V_end_day: = to_date (' 2010-01-01 ', ' yyyy-mm-dd ');

While V_begin_day < V_end_day LOOP
INSERT into Sale_report VALUES (v_begin_day, ' A ', 1);
INSERT into Sale_report VALUES (v_begin_day, ' B ', 2);
INSERT into Sale_report VALUES (v_begin_day, ' C ', 3);
V_begin_day: = V_begin_day + 1;
END LOOP;
END;
/

--Test function
CREATE OR REPLACE FUNCTION helloworldfunc
RETURN VARCHAR2
As
BEGIN
RETURN ' Hello world! ';
END;
/

--Test stored procedure
CREATE OR REPLACE PROCEDURE HelloWorld2 (
P_user_name in VARCHAR2,
P_out_val out VARCHAR2,
P_inout_val in Out VARCHAR2
) as
BEGIN
Dbms_output.put_line (' Hello ' | | p_user_name | | p_inout_val | | ‘!‘);
P_out_val: = ' A ';
P_inout_val: = ' B ';
END HelloWorld2;
/

--Test returns the function of the result set
Create or replace package Pkg_helloworld as
--Defining the REF CURSOR type
Type myrctype is REF CURSOR;
--Function declaration
function Gethelloworld return myrctype;
End Pkg_helloworld;
/

CREATE OR REPLACE Package Body Pkg_helloworld as
function Gethelloworld return Myrctype
Is
Return_cursor Myrctype;
BEGIN
OPEN return_cursor for ' SELECT ' ' Hello ' as A, ' world ' as B from dual ';
return return_cursor;
END Gethelloworld;
End Pkg_helloworld;
/

These are database tables, test data, stored procedure scripts

The following is a C # sample code

Using System;
Using System.Collections.Generic;
Using System.Linq;
Using System.Text;
Using System.Data;
Using System.Data.OracleClient;

Namespace A0170_oracle.sample
{

   //<summary>
   //For an example of an Oracle database stored procedure and function.
   ///
   ///NOTE: The table and data used in this sample, please refer to the Schema.sql file under Project.
   ///
   //</summary>
    class Calloraclefuncproc
    {
       //<summary>
       //Oracle database connection string.
       //</summary>
         Private Const String connstring =
            @ " Data source= (description= (address= (protocol=tcp) (host=192.168.1.210) (port=1521)) (Connect_data= (SERVICE_NAME= (ORCL))); User id=test; Password=test123 ";

public void Testcallfuncproc ()
{
Establish a database connection.
OracleConnection conn = new OracleConnection (connstring);

Open the connection.
Conn. Open ();

Call the Oracle function.
CALLFUNC (conn);

Functions that call Oracle to return a result set
Callfuncwithtable (conn);

Call a stored procedure
CallProcedure (conn);

Close the database connection.
Conn. Close ();
}

<summary>
Test calls to the Oracle function.
</summary>
private void Callfunc (OracleConnection conn)
{
Create a Command.
OracleCommand Testcommand = conn. CreateCommand ();

Defines the SQL statement that needs to be run.
Testcommand.commandtext = "Select Helloworldfunc () from dual";

Run the SQL command, and the results are stored in reader.
OracleDataReader Testreader = Testcommand.executereader ();

           //processing of each data retrieved.
            while (Testreader.read ())
             {
                //The retrieved data is output to the screen.
                Console.WriteLine ("Call function: {0}; return: {1} ",
                     Testcommand.commandtext, testreader[0]
                    );
           }

Close reader.
Testreader.close ();
}

<summary>
A Test call to Oracle returns a function of the result set.
</summary>
private void Callfuncwithtable (OracleConnection conn)
{
Create a Command.
OracleCommand Testcommand = conn. CreateCommand ();

Defines the SQL statement that needs to be run.
Testcommand.commandtext = "Pkg_helloworld.gethelloworld";

Well defined, the type of this run is the stored procedure.
Testcommand.commandtype = CommandType.StoredProcedure;

OK, I have this parameter, which is the cursor + return value.
OracleParameter para = new OracleParameter ("C", OracleType.Cursor);
Para. Direction = ParameterDirection.ReturnValue;
TESTCOMMAND.PARAMETERS.ADD (para);

           //Run the SQL command, and the results are stored in reader.
            OracleDataReader Testreader = Testcommand.executereader ();

           //processing of each data retrieved.
            while (Testreader.read ())
             {
                //The retrieved data is output to the screen.
                Console.WriteLine ("Call function: {0}; return: {1}-{2} ',
                     Testcommand.commandtext, testreader[0], testreader[1]
                    );
           }

Close reader.
Testreader.close ();
}

<summary>
Test run the stored procedure.
</summary>
<param name= "Conn" ></param>
private void CallProcedure (OracleConnection conn)
{
Create a Command.
OracleCommand Testcommand = conn. CreateCommand ();

Defines the SQL statement that needs to be run.
Testcommand.commandtext = "HelloWorld2";

Well defined, the type of this run is the stored procedure.
Testcommand.commandtype = CommandType.StoredProcedure;

Defines the number of parameters to query.
The first number of parameters is the input.
TESTCOMMAND.PARAMETERS.ADD (New OracleParameter ("P_user_name", "Heihei"));

The

           //2nd parameter is the output.
            OracleParameter para2 = new OracleParameter ( "P_out_val", Oracletype.varchar, 10);
            Para2. Direction = ParameterDirection.Output;
            testCommand.Parameters.Add (PARA2);

The 3rd parameter is both input and output.
OracleParameter para3 = new OracleParameter ("P_inout_val", Oracletype.varchar, 20);
Para3. Direction = Parameterdirection.inputoutput;
Para3. Value = "HAHA";
TESTCOMMAND.PARAMETERS.ADD (PARA3);

The ExecuteNonQuery method, which indicates that this operation is not a query operation. No result collection will be returned.
The returned data will be the number of records that are affected.
int insertrowcount = Testcommand.executenonquery ();

After the stored procedure has finished running, the output data is obtained.
String PA2 = testcommand.parameters["P_out_val"]. Value.tostring ();
String pa3 = testcommand.parameters["P_inout_val"]. Value.tostring ();

Console.WriteLine ("After calling {0} stored procedure, p_out_val={1}; P_inout_val={2} ", Testcommand.commandtext, PA2, PA3);

}

}
}