Oracle stored procedure and. net call

4. AccessOracleProcess/function (1)

Stored procedures are often used when SQL Server is used as a program,OracleYou can also use the process or function.OracleThe process does not seem to have a return value, and the function that has a return value (this is somewhat like BASIC, and the function/process is very detailed. SQL Server Stored Procedures can return values ).

. NETAccessOracleThe procedure/function method is similar to SQL Server, for example:

OracleParameter [] parameters = {
New OracleParameter ("ReturnValue", OracleType. Int32, 0, ParameterDirection. ReturnValue, true, 0, 0 ,"",
DataRowVersion. Default, Convert. DBNull)
New OracleParameter ("parameter 1", OracleType. NVarChar, 10 ),
New OracleParameter ("parameter 2", OracleType. DateTime ),
New OracleParameter ("parameter 3", OracleType. Number, 1)
};

Parameters [1]. Value = "test ";
Parameters [2]. Value = DateTime. Now;
Parameters [3]. Value = 1; // It can also be new OracleNumber (1 );

OracleConnection connection = new OracleConnection (ConnectionString );
OracleCommand command = new OracleCommand ("function/process name", connection );
Command. CommandType = CommandType. StoredProcedure;

Foreach (OracleParameter parameter in parameters)
Command. Parameters. Add (parameter );

Connection. Open ();
Command. ExecuteNonQuery ();
Int returnValue = parameters [0]. Value; // receives the function return Value.
Connection. Close ();

For how to set the DbType of Parameter, see the document of System. Data. OracleClient. OracleType enumeration. For example:OracleThe value of the Number type parameter in the database can be used. NETDecimal or System. Data. OracleClient. OracleNumber type.. NETInt or OracleNumber type. And so on.

In the preceding example, we can see that the function return value is specified using the parameter "ReturnValue", which is the parameter of ParameterDirection. ReturnValue.

5. AccessOracleProcess/function (2)

Processes/functions that do not return record sets (without SELECT output) are called in a similar way as SQL Server. However, if you want to return the record set through the process/functionOracleIs a little more troublesome.

In SQL Server, the following stored procedure:

Create procedure GetCategoryBooks
(
@ CategoryID int
)
AS
SELECT * FROM Books
WHERE CategoryID = @ CategoryID
GO

InOracle, Follow these steps:

(1) createPackage, Contains a cursor type: (only one operation is required in a database)

Create or replace package Test
AS
TYPE Test_CURSOR is ref cursor;
END Test;

(2) process:

Create or replace procedure GetCategoryBooks
(
P_CURSOROutTest. Test_CURSOR, -- here is the abovePackageType, output parameter
P_CatogoryID INTEGER
)
AS
BEGIN
OPEN p_CURSOR
SELECT * FROM Books
WHERE CategoryID = p_CatogoryID;
END GetCategoryBooks;

(3). NETProgram:

OracleParameters parameters = {
New OracleParameter ("p_CURSOR", OracleType. CURSOR, 2000, ParameterDirection. Output, true, 0, 0 ,"",
DataRowVersion. Default, Convert. DBNull ),
New OracleParameter ("p_CatogoryID", OracleType. Int32)
};

Parameters [1]. Value = 22;

OracleConnection connection = new OracleConnection (ConnectionString );
OracleCommand command = new OracleCommand ("GetCategoryBooks", connection );
Command. CommandType = CommandType. StoredProcedure;

Foreach (OracleParameter parameter in parameters)
Command. Parameters. Add (parameter );

Connection. Open ();
OracleDataReader dr = command. ExecuteReader ();

While (dr. Read ())
{
// Your specific operation. I don't need to teach this?
}
Connection. Close ();

In addition, if DataReader is used to obtain a record set, the program cannot access the data of output parameters and returned values before DataReader is disabled.

Okay. First, all in all.. NETAccessOracleThere are still many differences between SQL Server and SQL Server.