最近做了一个批量更新数据的小程序，采用的是mybatis调用oracle存储过程的方式。
1.编写oracle存储过程，
--mydept为传入参数
create or replace procedure diary_refresh(mydept in varchar2) as
--声明变量
popAt Date;
lastAt Date;duration number;
prjOn number;
prjOff number;
labor1On number;
labor1Off number;
labor2On number;
labor2Off number;
--cursor 游标 类似 容器 将查询到的结果存储
cursor cur is select trunc(startsat,'dd') from (select distinct trunc(p.startsat,'dd') as startsat from prj p union select distinct trunc(hf.acceptedat,'dd') from prj hf) order by startsat;
--开始 循环
begin
--防止重名字段插入
delete from hf_diaryy where dept=mydept;commit;
--查询 表prj中最小时间赋给lastAt
select min(startsAt) into lastAt from prj;
--打开 游标
open cur;
loop
-- 循环将 cur中的数据赋给 popAt
fetch cur into popAt;
-- 判断 游标是否结果 并跳出循环
exit when cur %notfound ;
--循环 查询符合业务条件的数据 赋给变量
select popAt-lastAt into duration from dual;select count(*) into prjOn from prj where startsat=lastAt and dept=mydept;
select count(*) into prjOff from prj where acceptedatlastAt and dept=mydept;
select sum(labor1) into labor1On from prj where startsat=lastAt and dept=mydept;
select sum(labor1) into labor1Off from prj where acceptedatlastAt and dept=mydept;
select sum(labor2) into labor2On from prj where startsat=lastAt and dept=mydept;
select sum(labor2) into labor2Off from prj where acceptedatlastAt and dept=mydept;
--将 变量值 insert到指定的数据表中
insert into hf_diaryy(dept,popAt,nextOneAt,duration,wip,labor1,labor2) values(mydept,lastAt,popAt,duration,prjOn-prjOff,labor1On-labor1Off,labor2On-labor2Off);lastAt:=popAt;
--结束 循环
end loop;commit;
end diary_refresh;
2. java中编写相应实体
3. mybatis调用，mapper.xml中
!-- 调用存储过程，批量插入业务数据 --
select id="upDept" parameterType="java.lang.String" statementType="CALLABLE"
![CDATA[{call DIARY_REFRESH(#{mydept,mode=IN,jdbcType=VARCHAR})}]]
/select
--获取session 执行
session.selectOne(mapper.upDept, "123");
