oracle new 赋值问题,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");