Oracle中存储过程中赋值和传参及程序调用返回DS

create or replace procedure EMPLOYEEMOVE_PROCE
(
    QUERYYEAR   in varchar2,
    QUERYMONTH in varchar2,
    CUSTOMERID in varchar2,
    CUSTOMERSERVICE in varchar2,
    p_GetData  out GetDataSet.GETCUR
)
AS
  STRSQL  varchar2(10000);
  STRWHERE varchar2(200);
begin

 IF (CUSTOMERID IS NOT NULL)  THEN
     STRWHERE := ' AND 客户编码='|| CUSTOMERID;
 END IF;   
 IF (CUSTOMERSERVICE IS NOT NULL) THEN 
     STRWHERE := STRWHERE || ' AND 客服专员='|| CUSTOMERSERVICE;
  END IF;     
    
        
  STRSQL :=
    'SELECT 客服专员,客户编码,客户名称,所属员工总数,所属员工累计离职数,所属员工在职数,当月新进员工数,当月离职员工数,差数
    FROM  t6
    WHERE  1=1 ';
    open p_GetData for (STRSQL || STRWHERE);
end;

--包体

CREATE OR REPLACE PACKAGE GetDataSet IS
    TYPE GETCUR IS REF CURSOR;
END GetDataSet;

 

CREATE OR REPLACE PACKAGE GetDataSet IS
    TYPE GETCUR IS REF CURSOR;
END GetDataSet;

	   DataSet ds = new DataSet();

            OracleParameter[] param = new OracleParameter[] 
                    { 
                        new OracleParameter("QUERYYEAR", OracleType.VarChar),
                        new OracleParameter("QUERYMONTH", OracleType.VarChar), 
                        new OracleParameter("CUSTOMERID", OracleType.VarChar), 
                        new OracleParameter("CUSTOMERSERVICE", OracleType.VarChar), 
                        new OracleParameter("p_GetData", OracleType.Cursor) 
                    };
            param[0].Value = QueryYear;
            param[1].Value = QueryMonth;
            param[2].Value = EmCompanyValue;
            param[3].Value = EmployID;

            param[0].Direction = ParameterDirection.Input;
            param[1].Direction = ParameterDirection.Input;
            param[2].Direction = ParameterDirection.Input;
            param[3].Direction = ParameterDirection.Input;
            param[4].Direction = ParameterDirection.Output;

            ds = Maticsoft.DBUtility.DbHelperOra.RunProcedure("EMPLOYEEMOVE_PROCE", param, "queryTable");

转载于:https://www.cnblogs.com/smartsmile/archive/2012/08/23/6234455.html