C # How to call the Oracle Stored Procedure

Preparation:

Environment: PL/SQL + Oracle9i + vs2008

Create Table test:

Create Table Test
(
ID number, // number
Name varchar2 (10), // name
Sex varchar2 (2), // gender
Age number, // age
Address varchar2 (200) // address
)

1. Execute the Oracle stored procedure without parameters:

First, write an oracle stored procedure without parameters:

Create or replace procedure proc1
Is
Begin insert into test (ID, name, sex, age) values (1, 'liheng', 'male', 25 );
Commit; // remember to write the transaction commit statement
End;

C # The Call Code is as follows:

Oracleconnection conn = new oracleconnection ("Server = CW; uid = ls005o9; Pwd = aaaaaa ");
Conn. open ();
Oraclecommand cmd = conn. createcommand ();
Cmd. commandtype = commandtype. storedprocedure; // specifies that the stored procedure is executed.
Cmd. commandtext = "proc1 ";
Cmd. executenonquery ();
Conn. Close ();

2. Execute the stored procedure of input type parameters:

First, write a stored procedure for input type parameters:

Create or replace procedure proc2
(
V_id number,
V_name varchar2
)
Is
Begin
Insert into test (ID, name) values (v_id, v_name );
Commit;
End;

C # The Call Code is as follows:

// When passing in parameters, I used two text boxes to accept user input parameters and made a simple verification.

If (string. isnullorempty (this. textbox1.text ))
{
MessageBox. Show ("No. cannot be blank! ");
This. textbox1.focus ();
Return;
}
If (string. isnullorempty (this. textbox2.text ))
{
MessageBox. Show ("name cannot be blank! ");
This. textbox2.focus ();
Return;
}
Oracleconnection conn = new oracleconnection ("Server = CW; uid = ls005o9; Pwd = aaaaaa ");
Conn. open ();
Oraclecommand cmd = conn. createcommand ();
Cmd. commandtype = commandtype. storedprocedure;
Cmd. commandtext = "proc2 ";
Cmd. Parameters. Add ("v_id", oracletype. Number). Direction = parameterdirection. input; // specify that the input parameter is used by the Oracle stored procedure.
Cmd. Parameters ["v_id"]. value = This. textbox1.text. Trim ();
Cmd. Parameters. Add ("v_name", oracletype. nvarchar). Direction = parameterdirection. input; // specifies that the input parameter is used by the Oracle stored procedure.
Cmd. Parameters ["v_name"]. value = This. textbox2.text. Trim ();
Cmd. executenonquery ();
Conn. Close ();

3. Execute the stored procedure of output type parameters:

First, write an output-type stored procedure:

Create or replace procedure proc3
(
Reccount out number
)
Is
Begin
Select count (*) into reccount from test;
End;

The meaning of this stored procedure is the total number of records in the table.

C # The Call Code is as follows:

Oracleconnection conn = new oracleconnection ("Server = CW; uid = ls005o9; Pwd = aaaaaa ");
Conn. open ();
Oraclecommand cmd = conn. createcommand ();
Cmd. commandtype = commandtype. storedprocedure;
Cmd. commandtext = "proc3 ";
Cmd. Parameters. Add ("reccount", oracletype. Number). Direction = parameterdirection. output;
Cmd. executenonquery ();

// Here I use a text box to accept the parameter values returned after the stored procedure is executed.
This. textbox3.text = cmd. Parameters ["reccount"]. value. tostring ();

Summary:

The above code runs successfully and can be extended according to me. The principle is the same and the results are different.