C # calls a stored procedure method with parameters--oracle "reprint"

1 because the eight reports of the crosstab are written in SQL , each report corresponds to a SQL statement that has more than one row, and cannot define the view because a parameter must be passed So we took advantage of the stored procedure implementation.

First, define the package and then define the package body to generate the dataset with . NET Calls

. NET calls the oracle stored procedure with parameters, returns the dataset

Oracle is a bit more complex, with the following steps:

Write the package definition file first in PL/SQL

CREATE OR REPLACE Package ZHBB as
TYPE t_cursor is REF cursor;
Procedure ZH11 (RQ in Char, io_cursor in Out t_cursor);

Procedure ZH12 (RQ in Char, io_cursor in Out t_cursor);

END ZHBB;

Second, write the definition file of the package body

CREATE OR REPLACE Package BODY ZHBB as

Procedure ZH11 (RQ in Char, io_cursor in off t_cursor) is

V_cursor T_cursor;

BEGIN

OPEN V_cursor for

Select * from AA where NY=RQ;

Io_cursor: = V_cursor;

END ZH11;

Procedure ZH12 (RQ in Char, io_cursor in off t_cursor) is

。。。。。

END ZH12;

. NET call methods:

public static DataTable getdatatable (string packagename,string proname,string connstring,string RQ)

{

connstring=" provider= msdaora.1; User Id=xx;data source=xx; Password=xx; "

Define the new link method, because the original driver "OraOLEDB.Oracle" does not meet the requirements, set to "Msdaora.1"

OleDbConnection raclecon = new OleDbConnection (connstring);

Oraclecon.open ();

OleDbCommand cmd = new OleDbCommand ("{call" + PackageName + ".") + Proname + "(?, {resultset 0, io_cursor})}", Oraclecon);

Cmd. Connection = Oraclecon;

Cmd. Parameters.Add ("RQ", OleDbType.VarChar, 8). Value = "" + RQ + "";

OleDbDataAdapter da = new OleDbDataAdapter (cmd);

DataTable dt = new DataTable ();

DataSet ds = new DataSet ();

Da. Fill (DT);

Oraclecon.close ();

Oraclecon.dispose ();

return DT;

}

}

C # calls a stored procedure method with parameters--oracle "reprint"