mybatis存储过程的写法
(注意事项： 在使⽤游标的时候，不能在游标声明之前，使⽤crud)
存储过程⽰例
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 default0;
# 标识事务错误
DECLARE err INT DEFAULT 0;
DECLARE i_id BIGINT;
DECLARE i_num BIGINT;
# 使⽤游标将数据存储到数据库中，并进⾏实际发放⾦额的统计
DECLARE cur CURSOR FOR
lect c.id,sum(d.numbers) from
( lect a.id from sys_ur a LEFT JOIN earnings_record b ON a.id = b.ur_id and b.status = '0' GROUP BY a.id having count(a.id) <= ( lect 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;
死灵法师英文t result='0';
# 查询总⾦额
lect IFNULL(sum(order_money),0) into _total_money from other_order where ISNULL(issue_id);
# 如果为0 就退出存储过程
if _total_money = 0 THEN
canteen怎么读t result='查询总⾦额为0，不进⾏发放';
LEAVE label;
end if;
# 查询基本配置
lect deduct into _deduct from earnings_manage where type = 0;
# 计算待发放⾦额
t _stay_out=ROUND(_total_money * _deduct /100);
# 如果为0 就退出存储过程
if _stay_out = 0 THENwe found love
if _stay_out = 0 THEN
t result='待发放⾦额⾦额为0，不进⾏发放';
LEAVE label;
end if;
# 查询通宝总额
lect IFNULL(sum(numbers),0) into _tb_num from zxt_detail;
# 如果为0 就退出存储过程
if _tb_num = 0 THEN
t result='通宝总⾦额为0，不进⾏发放';
LEAVE label;
end if;
# 定时发放的时间
lect deduct * 60 into _time_mi from earnings_manage where type = 3;
fion
# 开启事务
start TRANSACTION;
# 打开游标
open cur;
# 开始循环
read_loop: LOOP
# 提取游标的数据
FETCH cur INTO i_id,i_num;
# 声明结束的时候
IF done = 1 THEN
LEAVE read_loop;
END IF;
# 事务的处理
# 获取新的id
t _newid = REPLACE(unix_timestamp(current_timestamp(3)),'.','');
t i_num = FLOOR( _stay_out * i_num / _tb_num);
# 添加个⼈收益
IF i_num != 0 THEN
t _amount = _amount+i_num;
INSERT INTO `earnings_record` (`creator`, `ur_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 t issue_id = _newid where ISNULL(issue_id);foot的复数
# 如果事务发⽣错误，就进⾏回滚american dream
IF err=1 THEN
# 如果发⽣回滚就表⽰发⽣发⽣错误
t result='发⽣了回滚，不进⾏发放';
ROLLBACK;2016年12月四级
ELSE
bethereorbesquarecommit;
end if;
#关闭游标
ipbCLOSE cur;
END
