Oracle RefCursor 的使用

1, Create Procedure & Package

     create or replace package pkg_workstation is
       type refcur is Ref Cursor;
       procedure query(p_name varchar2,p_person varchar2,return_tb out refcur);
       procedure add(p_name varchar2,p_person varchar2,return_status out int);
     end pkg_workstation;

    create or replace package body pkg_workstation is
    procedure query (p_name varchar2,p_person varchar2,return_tb out refcur) is
    v_sql varchar2(1000);
    begin
      v_sql := 'select * from workstation where 1=1';
      if(p_name is not null) then
        v_sql := v_sql || ' and name='''||p_name||'''';
      end if;
     
      if(p_person is not null) then
        v_sql := v_sql || ' and person='''||p_person||'''';
      end if;
       
      open return_tb for v_sql;
    end;
  
    procedure Add (p_name varchar2,p_person varchar2,return_status out int) is
    v_count number;
    begin
     return_status := 0;
     select count(*) into v_count from workstation where person=p_person;
     if v_count>0 then
     return_status := -1;
     return;
     end if;
    
     insert into workstation (name,person) values (p_name,p_person);
     commit;
     return_status := 1;
    
     Exception
     when others
     then rollback;
    
  end;

2, Be invoked in VS.Net IDE

    public static OracleDataAdapter GetAdapter(string ProcedureName,OracleParameter[] Params)
   {
       using (OracleConnection conn = new OracleConnection(constr))
      {
          OracleCommand cmd = new OracleCommand(ProcedureName,conn);
          cmd.CommandType = CommandType.StoredProcedure;
         foreach(OracleParameter param in Params)
        {
           cmd.Parameters.Add(param);
        }
       OracleDataAdapter ada = new OracleDataAdapter(cmd);
      conn.Open();
      cmd.ExecuteNonQuery();
      cmd.Dispose();
      conn.Close();
      return ada;
    }
  }

public static DataTable query(string name,string person)
  {
     DataTable dt = new DataTable();
     object obj=null;
     OracleParameter[] pas = new OracleParameter[]
     {
        new OracleParameter("p_name",OracleDbType.NVarchar2,name,ParameterDirection.Input),
        new OracleParameter("p_person",OracleDbType.NVarchar2,person,ParameterDirection.Input),
        new OracleParameter("return_dt",OracleDbType.RefCursor,obj,ParameterDirection.Output)
     };
    OracleConnection conn = new OracleConnection(OracleHelper.ConnString);

    OracleDataAdapter ada = OracleHelper.GetAdapter("pkg_workstation.query",pas,conn);
    ada.Fill(dt,(OracleRefCursor)ada.SelectCommand.Parameters["return_dt"].Value);
    conn.Close();
    conn.Dispose();
    return dt;
  }