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;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步