E企盈营销工具技术服务商 热线:4006-838-530

mybatis调用oracle存储过程批量更新数据

E企盈直播平台营销卖货系统

最近做了一个批量更新数据的小程序,采用的是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”);

赞(0) 打赏
未经允许不得转载:E企盈小程序开发-热线:4006-838-530 » mybatis调用oracle存储过程批量更新数据
分享到: 更多 (0)
E企盈小程序直播营销卖货系统
E企盈直播平台营销卖货系统

评论 抢沙发

E企盈小程序开发

联系我们联系我们

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏