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

2016-12-30 09:54:42来源:oschina作者:深圳大道人点击

create or replace procedure EMPLOYEEMOVE_PROCE
(
QUERYYEAR in varchar2,
QUERYMONTH in varchar2,
CUSTOMERID in varchar2,
CUSTOMERSERVICE in varchar2,
p_GetDataout GetDataSet.GETCUR
)
AS
STRSQLvarchar2(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 客服专员,客户编码,客户名称,所属员工总数,所属员工累计离职数,所属员工在职数,当月新进员工数,当月离职员工数,差数
FROMt6
WHERE1=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");

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台