一、
我的是用包做的简单的应用存储过程返回结果集
create or replace package text_fhz is
type type_cur is ref cursor; --定义游标变量用于返回记录集
procedure TESTA(deptcode IN VARCHAR2,
v_cur out type_cur--定义输出的变量
);
end text_fhz;
create or replace package body text_fhz is
procedure TESTA(deptcode IN VARCHAR2,
v_cur out type_cur) AS
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;
protected void Button6_Click(object sender, EventArgs e)
{
//C#調用Package中的Procedure
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 = "";
}
二
不用 包 直接用存储过程返回数据集这里我们应用了sys_refcursor 这个返回游标sys_refcursor是oracle9i以后系统定义的一个refcursor,主要用在过程中返回结果集。
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;
同包一样的操作不过就是标红的地方有所不一样。
protected void Button6_Click(object sender, EventArgs e)
{
//C#調用Package中的Procedure
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 = "";
}