存储过程例子:
CREATE PROCEDURE `hbSchedule`(IN date VARCHAR(100))
BEGIN
INSERT INTO `RPT_SCHEDULE_BK` (`SCHEDULE`, `MATERIAL`, `REGION`, `DEVICE_TYPE`, `AMOUNT`, `DAY`, `WEEK`, `MONTH`, `TIME`, `CLICKS`)
SELECT k.`SCHEDULE`,k.MATERIAL,k.REGION,k.DEVICE_TYPE,sum(AMOUNT) AS AMOUNT,k.`DAY`,k.`WEEK`,k.`MONTH`,k.TIME,SUM(CLICKS) AS CLICKS from RPT_SCHEDULE k where k.`DAY`=date group BY `SCHEDULE`,`MATERIAL`,REGION,DEVICE_TYPE,`DAY`;
DELETE from RPT_SCHEDULE where DAY= date;INSERT INTO RPT_SCHEDULE SELECT * from RPT_SCHEDULE_BK;
TRUNCATE table RPT_SCHEDULE_BK;
END
在存储过程中使用参数时,除了写上必要的属性名外,还必须指定参数的 mode
(模式),可选值为 IN、OUT、INOUT 三种,入参使用 IN,出参使用 OUT,输入输出参数使用 INOUT。
mybatis的xml实现:
<!-- 定义存储过程 -->
<select id="hbSchedule" statementType="CALLABLE" >
call hbSchedule(
#{date,mode=IN,jdbcType=VARCHAR}
)
</select>
public void hbSchedule(@Param("date")String date);
mysql调用存储过程:call hbSchedule("20170606")