存储过程
CREATE OR REPLACE PACKAGE ZJUFDC.PAC_FW_ZCZJ AS
procedure FW_ZCZJ( SJ varchar2, --时间
num_Count out number);
END PAC_FW_ZCZJ;
PACKAGE Body
CREATE OR REPLACE PACKAGE BODY ZJUFDC.PAC_FW_ZCZJ AS
procedure FW_ZCZJ( SJ varchar2, --时间
num_Count out number) is
s_SQL varchar2(2000);
s_SQLINSERT varchar2(2000);
S_SQLUp varchar2(2000);
S_SQLSelect varchar2(4000);
S_SQL_INS_BDJL varchar2(2000);
strDEL varchar2(2000);
strINT varchar2(2000);
strNY varchar2(10);
BEGIN
begin
S_SQL_INS_BDJL:='DELETE FROM ZC_ZJJZNY_FW';
execute immediate S_SQL_INS_BDJL;
strNY:=substr(SJ,1,7);
s_SQLINSERT:='insert into ZC_ZJJZNY_FW(NY) values('''||strNY||''')';
execute immediate s_SQLINSERT;
--commit;
strDEL:='delete from ZC_FW_ZCZJ where zjny='''||substr(SJ,1,7)||'''';
execute immediate strDEL;
s_SQL:='insert into ZC_FW_ZCZJ(ZCFLH,ZCBH,ZCMC,QDRQ,SL,JZ,YZ,ZJNY,SYNX,YZJYS,CZL,JCZ,YZJE,LJZJYS,LJZJE,ZMJZ,JZZT) select ZCFLH,ZCBH,ZCMC,QDRQ,SL,JZ,YZ,ZJNY,SYNX,YZJYS,CZL,JCZ,YZJE,LJZJYS,LJZJE,ZMJZ,JZZT from VIEW_ZC_FW_ZCZJ_TEMP';
execute immediate s_SQL;
strDEL:='truncate table ZC_FW_ZCZJHZ';
execute immediate strDEL;
strINT:='insert into ZC_FW_ZCZJHZ select zjny,COUNT(*) AS SL,
sum(jz) AS YZ,
sum(yzje) as BYZJE,
sum(ljzje) as LJZJE,
sum(zmjz) as ZMJZ
from ZC_FW_ZCZJ group by zjny';
execute immediate strINT;
S_SQLSelect:='select count(*) from ASS_FW_ZCXXB' ;
execute immediate S_SQLSelect into num_Count;
commit;
end;
-- 异常处理
EXCEPTION
WHEN OTHERS THEN
num_Count:=0;
ROLLBACK;
RETURN;
end FW_ZCZJ;
END PAC_FW_ZCZJ;
/
XML配置
<!-- 调用存储过程 -->
<select id ="callProduce" parameterType="java.util.Map" resultType="java.util.Map" statementType="CALLABLE">
<!--注明statementType="CALLABLE"表示调用存储过程-->
<!--建在包下的存储过程调用 包名.存储过程名-->
call PAC_FW_ZCZJ.FW_ZCZJ(
#{SJ, jdbcType=VARCHAR, mode=IN},
#{num_Count, mode=OUT, jdbcType= INTEGER}
)
<!--传入传出参数要注明mode=IN/OUT 并要注明jdbcType,返回参数要注明对应的resultMap-->
</select >
Dao层
public Map<String,Integer> callProduce(Map<String, Object> parms);Service层
//返回类型由返回参数类型决定
public Integer callProduce(String ny);
SerciceImpl层
@Override
public Integer callProduce(String ny) {
Map<String, Object> parms = new HashMap<String, Object>();
System.out.println(ny);
parms.put("SJ", ny);
parms.put("num_Count",new Integer(0));
Map<String, Integer> map = zjglMapper.callProduce(parms);
System.out.println(parms.get("num_Count"));
//返回参数只能由传参的params取出
return (Integer) parms.get("num_Count");
}
Controller层
@RequestMapping("/user/fwzc_zjgl_fwzj_qdzj.do")
public @ResponseBody String fwzc_zjgl_fwzj_qdzj(HttpServletRequest request,HttpServletResponse response,Model model) throws UnsupportedEncodingException
{
String NY = null; //前台数据
try {
NY = new String (request.getParameter("NY"));
System.out.println(NY);
} catch (Exception e1) {
e1.printStackTrace();
}
Integer count = zjglService.callProduce(NY);//调用存储过程
System.out.println(count);
if(count > 0){
return "true";
}else{
return "false";
}
}