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 = "";
}