Oracle stored procedure _ jquery in asp.net

Stored procedures are in large database systems. A group of SQL statement sets are compiled and stored in the database to complete specific functions, you can specify the name of a stored procedure and provide parameters (if the stored procedure has parameters) to execute it. The following section describes the oracle stored procedure in asp.net, for more information, see stored procedures and triggers in large database systems. Both stored procedures and triggers are a collection of SQL statements and flow control statements.

ORACLE code

CREATE OR REPLACE PROCEDURE gd_CURSOR(MYCS1 OUT SYS_REFCURSOR,MYCS2 OUT SYS_REFCURSOR,a out varchar)asBEGIN a:='test'; OPEN MYCS1 FOR SELECT 1 from dual; OPEN MYCS2 FOR SELECT 2 from dual;END;

C # code

////// Execute the oracle stored procedure and return multiple result sets //////Stored Procedure name///Number of returned results///Parameters///
 
  
Arbitrary object Array
 Public object [] ExcuteProc_N_Result (string strProcName, int ResultCount, params OracleParameter [] paras) {using (OracleConnection conn = new OracleConnection ("User ID = username; Password = Password; data Source = database; ") {OracleCommand cmd = new OracleCommand (strProcName, conn); if (paras! = Null & paras. length> 0) {for (int j = 0; j <paras. length; j ++) {if (paras [j]. value = null) {paras [j]. value = DBNull. value ;}} cmd. parameters. addRange (paras); cmd. commandType = CommandType. storedProcedure; conn. open (); cmd. executeNonQuery (); int I = 0; // int nOutputParametersCount = 0; object [] objResult = new object [ResultCount]; foreach (OracleParameter p in cmd. parameters) {if (p. direction = ParameterDirection. output | p. direction = ParameterDirection. inputOutput) {if (p. value is OracleDataReader) {OracleDataReader reader = p. value as OracleDataReader; objResult [I ++] = ConvertDataReaderToDataTable (reader);} else {objResult [I ++] = p. value ;}}return objResult ;}}////// Convert DataReader to DataTable //////OleDbDataReaderProtected DataTable ConvertDataReaderToDataTable (OracleDataReader reader) {DataTable objDataTable = new DataTable ("TmpDataTable"); try {int intFieldCount = reader. fieldCount; // obtain the number of columns in the current row. for (int intCounter = 0; intCounter <= intFieldCount-1; intCounter ++) {objDataTable. columns. add (reader. getName (intCounter), reader. getFieldType (intCounter);} // populate datatable objDataTable. beginLoadData () ; // Object [] objValues = new object [intFieldCount-1]; object [] objValues = new object [intFieldCount]; while (reader. read () {reader. getValues (objValues); objDataTable. loadDataRow (objValues, true);} reader. close (); objDataTable. endLoadData (); return objDataTable;} catch (Exception ex) {throw new Exception ("Conversion error! ", Ex );}}

Call Method

OracleParameter[] oracleParameter = new OracleParameter[]{new OracleParameter("MYCS1",OracleType.Cursor),new OracleParameter("MYCS2",OracleType.Cursor),new OracleParameter("a",OracleType.VarChar,200),};oracleParameter[0].Direction = ParameterDirection.Output;oracleParameter[1].Direction = ParameterDirection.Output;oracleParameter[2].Direction = ParameterDirection.Output;object[] xxx = ExcuteProc_N_Result("gd_CURSOR", 3, oracleParameter);

The preceding Code introduces the oracle stored procedure in asp.net.

Next, we will introduce the oracle Stored Procedure (image and text) through the second method ).

Please refer to the following methods and steps

Step 1: configure the database to be connected through the Net Manager provided by ORACLE, such as COST

Step 2: Open the PL/SQL database tool, which is the correct user name and password, and select it. Click OK to enter the user who needs to create the stored procedure.

Step 3: understand the general Stored Procedure format

Create or replace procedure stored procedure name (param1 in type, param2 out type)
As
Variable 1 type (value range );
Variable 2 type (value range );
Begin
Statement Block
Exception -- Exception Handling
When others then
Rollback;
End;

Step 4: Enter the stored procedure to be created on the SQL input interface

Create or replace procedure sp_demo (param1 in varchar2, param2 out varchar2)/** Stored procedure instance */ascnt int; rst varchar2 (100) Begin Select count (*) into cst from Tab_Demo where Col_Value = param1; If (cst> 0) then -- judgment condition param2: = 'matched value'; Else param2: = 'unmatched value '; end if; Exception When others then Rollback; End;

For example

Step 5: test the Stored Procedure

Exec sp_demo ('male ');

END

Notes

You cannot delete another stored procedure in one stored procedure. You can only call another stored procedure.

If you use create or replace procedure, do not create a stored procedure with the same name as an existing stored procedure. As a result, the stored procedure is overwritten.
Stored procedure parameters do not have a value range. in indicates input, and out indicates output.

The above two methods are used to introduce the oracle stored procedure, and we hope to help you.