. NET using Oracle functions and procedures and output parameters (3) using cursors to return the list

CSDN Tags: oracle,. NET, ado.net, FUNCTION, stored procedure

Use cursors, return list, define class by package

in SQL Server

Do a store that gets the return list directly

CREATE PROCEDURE [dbo]. [Test_get]
As

Testing stored Procedures

Get a return list and return value

in Oracle

But if the Oracle database procedure is generally not able to implement this, then how to deal with it
The recordset is not returned directly in Oracle, and the recordset is returned through parameters as a cursor.
The first step is to create a package this place basically defines the Mycrtype a cursor type

CREATE OR REPLACE PACKAGE pkg_test as TYPE myrctype is REF CURSOR; End Pkg_test;

Step two to create a function

Return number was result number ; The Begin open p_cursor for select Art_no,descr the From article where art_no< Artno; return (result); End Mytest1;

Test results as shown:

As shown in the figure

So when called in C # code, there is no difference from the call return value of SQL Server.

private static OracleCommand BuildIntCommand3 (OracleConnection Connection, String storedprocname, idataparameter[] Parameters) {OracleCommand command = Buildquerycommand (connection, storedprocname, Parameters) command. Parameters.Add ("P_cursor", OracleType.Cursor). Direction = ParameterDirection.Output; Command. Parameters.Add ("ReturnValue", Oracletype.number). Direction = ParameterDirection.ReturnValue; return command; }

Mainly reflected in the addition of 2 return parameters P_cursor cursor Recordset and returnvalue return value

public static void Runprocedure (String storedprocname, idataparameter[] Parameters, DataSet DataSet, string tablename) {u Sing (oracleconnection connection = new OracleConnection (connectionString)) {OracleDataAdapter SqlDA = new Oracledataada Pter (); Sqlda.selectcommand = BuildIntCommand3 (connection, storedprocname, parameters); Sqlda.fill (DataSet, TableName); Connection. Close (); } }

populating datasets with OracleDataAdapter

public DataSet GetNoPermissionList1 (int roleid) {DataSet permissions = new DataSet (); oracleparameter[] Parameters = {New OracleParameter ("Roleid", Oracletype.varchar, 4)}; Parameters[0]. Value = Roleid; Dbhelperora.runprocedure ("Sp_acc_getnopermissionlist", Parameters, permissions, "permissions"); return permissions; }

Call a stored procedure with a parameter

DataTable mytb1= myperm. GetNoPermissionList1 (555). Tables[0]; Gridview1.datasource = MYTB1; Gridview1.databind ();

Foreground page shows the recordset returned