JAVA通过MyBatis调用MySql存储过程和函数
1. 引言
无论是采用SPRING MVC框架开发WEB画面,还是开发需按一定时间间隔执行的批处理,都可能要调用数据库的存储过程或函数。其间调用参数设置不正会浪费大量调试时间初学者甚至放弃使用存储过程。本文记录了通过MyBatis调用MySql存储过程和函数的具体参数设置内容，供参考。 2．MySql存储过程例
/*全公司员工下一年度带薪休假一发赋予处理*/
CREATE DEFINER=`DBuser`@`%` PROCEDURE `paid_vacation_compute `(
OUT p_返回值 INT(11) , INOUT p_员工号 CHAR(3) , p_操作者ID VARCHAR(3)) PROC_START: BEGIN
/* 变量声明 */
DECLARE done INT; #异常退出控制变量 DECLARE empNo CHAR(3); #员工号 DECLARE dateHire date; #分公司就职日 DECLARE workYears INT; #集团内工作年数
DECLARE lastYearRemainDays FLOAT; #昨年残日数（允许以小时为单位休假） DECLARE nowYearleaveDays FLOAT; #今年休暇日数（允许以小时为单位休假） DECLARE elapseYear INT; #入集团经过年度数
/* 游标声明 */ #上年带薪休假数据
DECLARE staffPaidVacationDaysCur CURSOR FOR SELECT a.EMP_NO, #员工号
a.DATE_HIRE, #入职日期
a.WORK_YEARS, #工作年限
b.REMAIN_DAYS # 上年带薪休假应休但未休残日数 FROM T_EMPLOYEE AS a,
T_PAID_VACATION AS b WHERE a. EMP_NO = b. EMP_NO
/* 程序退出规定声明 */
DECLARE CONTINUE HANDLER FOR NOT FOUND SET SET done = 1;
DECLARE EXIT HANDLER FOR SQLWARNING SET done = 2;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
SET done = 3;
ROLLBACK; #SQL异常回滚
END;
/* １．输入参数妥当性检查 */
IF (p_操作者 IS NULL OR p_操作者 = '') THEN
SET p_返回值 = 9;
LEAVE PROC_START; END IF; SET done = 0; START TRANSACTION; loop_label: LOOP
# 读入一条记录, 代入到各个变量中 FETCH staffPaidVacationDaysCur INTO empNo, #员工号
dateHire, #就职日
workYears, #集团内工作年数 lastYearRemainDays #昨年残日数 ;
IF done = 1 THEN
LEAVE loop_label;
END IF;
/*根据集团内工作年限+分公司就职年限计算下一年度带薪天数*/
SET elapseYear = YEAR（current_timestamp）- Year(dateHire)
+ workYears;
IF elapseYear = 0 THEN
SET nowYearLeaveDays = 10; SET nowYearLeaveDays = 11; SET nowYearLeaveDays = 12;
ELSEIF elapseYear = 1 THEN ELSEIF elapseYear = 2 THEN ELSEIF elapseYear = 3 THEN
SET nowYearLeaveDays = 14; SET nowYearLeaveDays = 16; SET nowYearLeaveDays = 18; SET nowYearLeaveDays = 20;
ELSEIF elapseYear = 4 THEN ELSEIF elapseYear = 5 THEN ELSEIF elapseYear >= 6 THEN END IF; SET done = 0; SET p_员工号 = empNo; UPDATE T_PAID_VACATION SET
LAST_YEAR_REMAIN_DAYS = lastYearRemainDays, THIS_YEAR_BASE_DAYS = nowYearLeaveDays, UPDATE_DATETIME = current_timestamp, UPDATE_USER_ID = 'SYS',
UPDATE_TERMINAL_ID = 'MANUAL'
WHERE EMP_NO = CONVERT(empNo USING binary); IF done = 3 THEN SET p_返回值 = 6; LEAVE PROC_START; END IF;
END LOOP; COMMIT;
END
3．MySql函数例
CREATE DEFINER=`DBuser`@`%` FUNCTION ` paid_vacation_compute `(
p_员工号 CHAR(3) , p_操作者ID VARCHAR(3)) ) RETURNS int(11) BEGIN
/* 变量声明 */
DECLARE done INT; #异常退出控制变量 DECLARE empNo CHAR(3); #员工号 DECLARE dateHire date; #分公司就职日 DECLARE workYears INT; #集团内工作年数
DECLARE lastYearRemainDays FLOAT; #昨年残日数（允许以小时为单位休假） DECLARE nowYearleaveDays FLOAT; #今年休暇日数（允许以小时为单位休假） DECLARE elapseYear INT; #入集团经过年度数
/* 游标声明 */ #上年带薪休假数据
DECLARE staffPaidVacationDaysCur CURSOR FOR SELECT a.EMP_NO, #员工号
a.DATE_HIRE, #入职日期
a.WORK_YEARS, #工作年限
b.REMAIN_DAYS # 上年带薪休假应休但未休残日数 FROM T_EMPLOYEE AS a,
T_PAID_VACATION AS b WHERE a. EMP_NO = b. EMP_NO
/* 程序退出规定声明 */
DECLARE CONTINUE HANDLER FOR NOT FOUND SET SET done = 1; DECLARE EXIT HANDLER FOR SQLWARNING SET done = 2;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
SET done = 3;
ROLLBACK; #SQL异常回滚
END;
/* １．输入参数妥当性检查 */
IF (p_操作者 IS NULL OR p_操作者 = '') THEN
RETURN 9;
END IF; SET done = 0; START TRANSACTION; loop_label: LOOP
# 读入一条记录, 代入到各个变量中 FETCH staffPaidVacationDaysCur INTO empNo, #员工号
dateHire, #就职日
workYears, #集团内工作年数 lastYearRemainDays #昨年残日数 ;
IF done = 1 THEN
LEAVE loop_label;
END IF;
/*根据集团内工作年限+分公司就职年限计算下一年度带薪天数*/
SET elapseYear = YEAR（current_timestamp）- Year(dateHire)
+ workYears;
IF elapseYear = 0 THEN
SET nowYearLeaveDays = 10; SET nowYearLeaveDays = 11; SET nowYearLeaveDays = 12; SET nowYearLeaveDays = 14; SET nowYearLeaveDays = 16; SET nowYearLeaveDays = 18; SET nowYearLeaveDays = 20;
ELSEIF elapseYear = 1 THEN ELSEIF elapseYear = 2 THEN ELSEIF elapseYear = 3 THEN ELSEIF elapseYear = 4 THEN ELSEIF elapseYear = 5 THEN ELSEIF elapseYear >= 6 THEN END IF;