MyBatis调用存储过程

  只需要三板斧就可以了,直接看例子:

  1、声明接口:

public interface HelloWorldMapper
{
    void saveWorldProcedure();
}

 

  2、在HelloWorld.xml定义存储过程:

复制代码
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wlf.dao.HelloWorldMapper">

    <!-- 调用存储过程 -->
    <update id="saveWorldProcedure"  statementType="CALLABLE">
      {call PROC_HELLO_WORLD_UPDATE}
     </update>
</mapper>
复制代码

 

  3、在Oracle中创建存储过程:

复制代码
CREATE OR REPLACE procedure PROC_HELLO_WORLD_UPDATE
is
  cursor BOX_CUR
  is --声明显式游标
    select T.BOXID,
      T.COUNTSTARTTIME,
      T.COUNTENDTIME
    from T_adapte_BOXINFO T
    where T.HASCOUNTTIME = 1;
  type BOX_CUR_ROW is table of BOX_CUR%ROWTYPE; --定义游标变量,该变量的类型为基于游标BOX_CUR的记录

  cs_adaptestat SYS_REFCURSOR;
  type tp_HELLO_WORLD is table of T_ADAPTER_HELLO_WORLD%ROWTYPE;
  va_HELLO_WORLD tp_HELLO_WORLD;
  BOX_ID number(11,0);
  START_TIME  date;
  END_TIME    date;
begin
  --For 循环
  for BOX_CUR_ROW in BOX_CUR
  LOOP
    BOX_ID := BOX_CUR_ROW.BOXID;
    START_TIME  := BOX_CUR_ROW.COUNTSTARTTIME;
    select TRUNC(BOX_CUR_ROW.COUNTENDTIME+1)-1/(24*3600)
    into END_TIME
    from DUAL;

    open cs_adaptestat for select T1.ADAPTERMSISDN,COUNT(*) as TOTALOPENNUMBER,max(OPENTIME) LASTOPENTIME,BOXID
    from T_ADAPTEE_RECORD T1
    where (T1.OPENTIME <= END_TIME
    and T1.OPENTIME    >= START_TIME
    and T1.OPENSTATUS      = 1
    and T1.ADAPTEETYPE    = 0
    and T1.BOXID     = BOX_ID)
    group by BOXID,T1.ADAPTERMSISDN;

    fetch cs_adaptestat bulk collect into va_HELLO_WORLD limit 500;

    forall i in 1..va_HELLO_WORLD.count
    merge into T_ADAPTER_HELLO_WORLD T5
    using (select * from dual)
    on (ADAPTERMSISDN = va_HELLO_WORLD(i).ADAPTERMSISDN AND BOXID=va_HELLO_WORLD(i).BOXID)
     when matched then
        update
        set TOTALOPENNUMBER      =va_HELLO_WORLD(i).TOTALOPENNUMBER,
          LASTOPENTIME           =va_HELLO_WORLD(i).LASTOPENTIME
        where T5.TOTALOPENNUMBER!=va_HELLO_WORLD(i).TOTALOPENNUMBER
      when not matched then
        insert
          (
            ADAPTERMSISDN,
            TOTALOPENNUMBER,
            LASTOPENTIME,
            BOXID
          )
          values
          (
            va_HELLO_WORLD(i).ADAPTERMSISDN,
            va_HELLO_WORLD(i).TOTALOPENNUMBER,
            va_HELLO_WORLD(i).LASTOPENTIME,
            va_HELLO_WORLD(i).BOXID
          );
        commit;
  end LOOP;
end;
复制代码

 

 

 

posted on   不想下火车的人  阅读(1248)  评论(0编辑  收藏  举报

努力加载评论中...

导航

点击右上角即可分享
微信分享提示