Use the Oracle stored procedure in C # To return the result set

Problem:

A stored procedure defined in MSSQLServer can directly return a dataset, for example:

    ..

In the Oracle database, the definition is as follows:How can this problem be solved?

 

Method:

In Oracle, you can use Cursor to operate a dataset, but the Cursor error is directly used in the output parameters of stored procedures. In this case, the Cursor should be a key word defining the Cursor, not a type, therefore, you must first define a package and declare the global custom cursor type in the package, for example:

   package pkg_products type cursor_pdt  ref  pkg_products;

This package is similar to the interface in OO. the file header in C ++ is called the package body and has the same name. For example:

   package body pkg_products      proc_GetAllProducts(cur_set out cursor_pdt)                   cur_set   id, name, producttype, price,picture, isout, mark, adddate ;

After the definition is successful and the compilation is successful, you can first test it in Oracle, for example:

pdtrow products              cur_set              cur_set           DBMS_OUTPUT.PUT_LINE (pdtrow.id;

If the above PL/SQL script can get the result, it indicates that the implementation of the defined package and package body is successful. You can use it.. NET, Java, or other programs access the defined stored procedure. For example, the script for accessing the stored procedure using C # is as follows:

             OracleConnection conn =  OracleConnection(                          OracleCommand cmd =  OracleCommand(                          cmd.CommandType =                          OracleParameter cur_set =                   OracleParameter(                          cur_set.Direction =                                                       cmd.ExecuteReader(CommandBehavior.CloseConnection);

Note: add the reference assembly to the project: System. data. oracleClient and using are also required in the Code. If an SQL statement is used as a string in C #, try not to wrap it or end it with a semicolon. The code used to access the database is not encapsulated. DataSet and DataTable are returned. The sample code is as follows: