A simple application Stored Procedure returned result set that oracle uses a package plus a stored procedure and a direct application Stored Procedure

I,

My is a simple application stored procedure that uses a package to return a result set.

Create or replace package text_fhz is
Type type_cur is ref cursor; -- defines the cursor variable used to return the record set
Procedure TESTA (deptcode IN VARCHAR2,
V_cur out type_cur -- defines the output variable

);
End text_fhz;

Create or replace package body text_fhz is
Procedure TESTA (deptcode IN VARCHAR2,
V_cur out type_cur)

V_ SQL varchar2 (4000 );
V_deptcode_n varchar2 (100 );
BEGIN
V_deptcode_n: = deptcode | '% ';
-- V_ SQL: = 'select zcbm, fwzc_id from t_fwzc where deptcode like ''' | deptcode | '% ''';
V_ SQL: = 'select zcbm, fwzc_id from t_fwzc where deptcode like ''' | v_deptcode_n | '''';
Open v_cur for v_ SQL;
End testa;
End text_fhz;

You may have to write the following database calls by yourself.

Protected void Button6_Click (object sender, EventArgs e)
{
// C # use Procedure in the Package
OracleCommand comm = base. Conn. CreateCommand ();
Comm = new OracleCommand ("text_fhz.TESTA", base. Conn );
Comm. CommandType = CommandType. StoredProcedure;
// OracleParameter p1 = new OracleParameter ("str", OracleType. VarChar, 10 );

OracleParameter p1 = new OracleParameter ("deptcode", OracleType. Number );
P1.Direction = ParameterDirection. Input;
P1.Value = "1 ";
OracleParameter p2 = new OracleParameter ("v_cur", OracleType. Cursor );
P2.Direction = ParameterDirection. Output;
Comm. Parameters. Add (p1 );
Comm. Parameters. Add (p2 );

DataTable dt = new DataTable ();
OracleDataAdapter da = new OracleDataAdapter (comm );
Da. Fill (dt );
String xx = "";

}

 

II

The returned cursor sys_refcursor is a refcursor defined by the system after oracle9i. It is mainly used to return the result set in the process.

 

Create or replace procedure text_fhz_SYS_REFCURSOR (deptcode in varchar, v_cur out sys_refcursor)
Is
V_ SQL varchar2 (4000 );
V_deptcode_n varchar2 (100 );
Begin
V_deptcode_n: = deptcode | '% ';
V_ SQL: = 'select * from t_fwzc where deptcode like ''' | v_deptcode_n | '''';
Open v_cur for v_ SQL;
End text_fhz_SYS_REFCURSOR;

 

 

The operations of the same package are different in the red.

Protected void Button6_Click (object sender, EventArgs e)
{
// C # use Procedure in the Package
OracleCommand comm = base. Conn. CreateCommand ();
Comm = new OracleCommand ("text_fhz_SYS_REFCURSOR", base. Conn );
Comm. CommandType = CommandType. StoredProcedure;
// OracleParameter p1 = new OracleParameter ("str", OracleType. VarChar, 10 );

OracleParameter p1 = new OracleParameter ("deptcode", OracleType. Number );
P1.Direction = ParameterDirection. Input;
P1.Value = "1 ";
OracleParameter p2 = new OracleParameter ("v_cur", OracleType. Cursor );
P2.Direction = ParameterDirection. Output;
Comm. Parameters. Add (p1 );
Comm. Parameters. Add (p2 );

DataTable dt = new DataTable ();
OracleDataAdapter da = new OracleDataAdapter (comm );
Da. Fill (dt );
String xx = "";

}