dao层
// 调用存储过程 void callProcedureGrantEarnings(@Param("params") Map<String,Object> params);
Xml
<select id="callProcedureGrantEarnings" statementType="CALLABLE"> {call earnings_proceduce(#{params.result,mode=OUT,jdbcType=VARCHAR})} </select>
得到的结果就在map中的result中。
存储过程
CREATE DEFINER=`root`@`::1` PROCEDURE `earnings_proceduce`(out result varchar(100)) label:BEGIN #收益记录的分配 # 基本参数的定义 # 总金额 DECLARE _total_money BIGINT DEFAULT 0; # 发放配置占比 DECLARE _deduct BIGINT; # 待发放金额 DECLARE _stay_out BIGINT DEFAULT 0; # 用户最多保存数量 DECLARE _num BIGINT DEFAULT 0; # 查询通宝币总额 DECLARE _tb_num BIGINT DEFAULT 0; # 实际发放金额 DECLARE _amount BIGINT DEFAULT 0; # 定时发放时间分钟 DECLARE _time_mi BIGINT DEFAULT 0; # 收入统计的id DECLARE _newid BIGINT; # 判断是否遍历全部记录的标记 DECLARE done int default 0; # 标识事务错误 DECLARE err INT DEFAULT 0; DECLARE i_id BIGINT; DECLARE i_num BIGINT; # 使用游标将数据存储到数据库中,并进行实际发放金额的统计 DECLARE cur CURSOR FOR select c.id,sum(d.numbers) from ( select a.id from sys_user a LEFT JOIN earnings_record b ON a.id = b.user_id and b.status = '0' GROUP BY a.id having count(a.id) <= ( select deduct from earnings_manage where type = 4 )) c join zxt_detail d on c.id = d.creator group by c.id; # 出现错误,设置为1,只要发生异常就回滚 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err=1; # 将结束标志绑定到游标 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; set result='0'; # 查询总金额 select IFNULL(sum(order_money),0) into _total_money from other_order where ISNULL(issue_id); # 如果为0 就退出存储过程 if _total_money = 0 THEN set result='查询总金额为0,不进行发放'; LEAVE label; end if; # 查询基本配置 select deduct into _deduct from earnings_manage where type = 0; # 计算待发放金额 set _stay_out=ROUND(_total_money * _deduct /100); # 如果为0 就退出存储过程 if _stay_out = 0 THEN set result='待发放金额金额为0,不进行发放'; LEAVE label; end if; # 查询通宝总额 select IFNULL(sum(numbers),0) into _tb_num from zxt_detail; # 如果为0 就退出存储过程 if _tb_num = 0 THEN set result='通宝总金额为0,不进行发放'; LEAVE label; end if; # 定时发放的时间 select deduct * 60 into _time_mi from earnings_manage where type = 3; # 开启事务 start TRANSACTION; # 打开游标 open cur; # 开始循环 read_loop: LOOP # 提取游标的数据 FETCH cur INTO i_id,i_num; # 声明结束的时候 IF done = 1 THEN LEAVE read_loop; END IF; # 事务的处理 # 获取新的id set _newid = REPLACE(unix_timestamp(current_timestamp(3)),'.',''); set i_num = FLOOR( _stay_out * i_num / _tb_num); # 添加个人收益 IF i_num != 0 THEN set _amount = _amount+i_num; INSERT INTO `earnings_record` (`creator`, `user_id`, `status`, `amount`, `create_date`) VALUES ('10000',i_id,'0',i_num, NOW()); end if; end LOOP read_loop; # 添加总收益 INSERT INTO `earnings_issue` (`id`, `stay_out`, `amount`, `other_id`, `updater`, `update_date`, `creator`, `create_date`, `deduct`, `earnings_sum`, `time_out`) VALUES (_newid, _stay_out, _amount, NULL, '10000', NOW(), '10000', NOW(),_total_money - _stay_out, _total_money, _time_mi); # 给订单表绑定任务 update other_order set issue_id = _newid where ISNULL(issue_id); # 如果事务发生错误,就进行回滚 IF err=1 THEN # 如果发生回滚就表示发生发生错误 set result='发生了回滚,不进行发放'; ROLLBACK; ELSE commit; end if; #关闭游标 CLOSE cur; END