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

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:

create procedure sp_getAllEmployeesasSELECT * FROM [NORTHWND].[dbo].[Employees]

In the Oracle database, the definition is as follows:ErrorHow 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:

/** Create a package named pkg_products **/create or replace package pkg_products is -- Define a public cursor type cursor_pdt -- ref can be passed between programs -- open in a program cursor variable, type cursor_pdt is ref cursor; -- declare a stored procedure. The cursor type parameter is output procedure proc_GetAllProducts (cur_set out cursor_pdt); end 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:

/** Create a package **/create or replace package body pkg_products is -- Implement the Stored procedure proc_GetAllProducts (cur_set out cursor_pdt) as begin that is not implemented in the package -- open the cursor, when defining a cursor, using ref to process the cursor can be postponed to the client open cur_set for select id, name, producttype, price, picture, isout, mark, adddate from products; end;

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

/** Use the stored procedure defined in the procedure Test **/declare -- defines the cur_set pkg_products.cursor_pdt variable of the cursor type; -- defines the row type pdtrow products % rowtype; begin -- execute the Stored Procedure pkg_products.proc_GetAllProducts (cur_set); -- traverse the data LOOP in the cursor -- store the data from the current row to pdtrow FETCH cur_set INTO pdtrow; -- If no data is obtained, end the loop exit when cur_set % NOTFOUND; -- output the obtained data DBMS_OUTPUT.PUT_LINE (pdtrow. id | ',' | pdtrow. name); end loop; CLOSE cur_set; end;

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:

1 // define the connection object 2 OracleConnection conn = new OracleConnection ("Data Source = orcl; Persist Security Info = True; User ID = t52; Unicode = True; Password = t52 "); 3 // Note: package name. stored Procedure name format 4 OracleCommand cmd = new OracleCommand ("pkg_products.proc_GetAllProducts", conn); 5 // set the command type to Stored Procedure 6 cmd. commandType = CommandType. storedProcedure; 7 // define parameters. Note that the parameter name must be consistent with the stored procedure definition and the type is OracleType. cursor 8 OracleParameter cur_set = 9 new OracleParameter ("cur_set", OracleType. cursor); 10 // set the parameter to the output type 11 cur_set.Direction = ParameterDirection. output; 12 // Add parameter 13 cmd. parameters. add (cur_set); 14 // open the connection 15 conn. open (); 16 // run and return OracleDataReader. Release 17 return cmd after use. 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: