mybatis中存储过程的调用

　　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`@`::` PROCEDURE `earnings_proceduce`(out  result varchar())

label:BEGIN

    #收益记录的分配

    # 基本参数的定义

    # 总金额

    DECLARE _total_money BIGINT DEFAULT ;

    # 发放配置占比

    DECLARE _deduct BIGINT;

    # 待发放金额

    DECLARE _stay_out BIGINT DEFAULT ;

    # 用户最多保存数量

    DECLARE _num BIGINT DEFAULT ;

    # 查询通宝币总额

    DECLARE _tb_num BIGINT DEFAULT ;

    # 实际发放金额

    DECLARE _amount BIGINT DEFAULT ;

    # 定时发放时间分钟

    DECLARE _time_mi BIGINT DEFAULT ;

    # 收入统计的id

    DECLARE _newid BIGINT;

    # 判断是否遍历全部记录的标记

    DECLARE done int default ;

    # 标识事务错误

  DECLARE err INT DEFAULT ;

  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 = '' GROUP BY a.id having count(a.id) <= (

    select deduct from earnings_manage where type =

    )) c join zxt_detail d on c.id = d.creator group by c.id;

    # 出现错误，设置为1，只要发生异常就回滚

  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err=;

    # 将结束标志绑定到游标

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = ;

    set result='';

    # 查询总金额

    select IFNULL(sum(order_money),) into _total_money from other_order where ISNULL(issue_id);

    # 如果为0 就退出存储过程

    if _total_money =  THEN

            set result='查询总金额为0,不进行发放';

            LEAVE label;

    end if;

    # 查询基本配置

    select deduct into _deduct from earnings_manage where type = ;

    # 计算待发放金额

    set _stay_out=ROUND(_total_money * _deduct /);

# 如果为0 就退出存储过程

    if _stay_out =  THEN

            set result='待发放金额金额为0,不进行发放';

            LEAVE label;

    end if;

# 查询通宝总额

    select IFNULL(sum(numbers),) into _tb_num from zxt_detail;

    # 如果为0 就退出存储过程

    if _tb_num =  THEN

            set result='通宝总金额为0,不进行发放';

            LEAVE label;

    end if;

    # 定时发放的时间

    select deduct *  into _time_mi from earnings_manage where type = ;

    # 开启事务

    start TRANSACTION;

    # 打开游标

    open cur;

    # 开始循环

    read_loop: LOOP

    # 提取游标的数据

    FETCH cur INTO i_id,i_num;

    # 声明结束的时候

  IF done =  THEN

     LEAVE read_loop;

  END IF;

    # 事务的处理

    # 获取新的id

    set _newid = REPLACE(unix_timestamp(current_timestamp()),'.','');

    set i_num = FLOOR( _stay_out * i_num / _tb_num);

    # 添加个人收益

    IF i_num !=  THEN

            set _amount = _amount+i_num;

            INSERT INTO `earnings_record` (`creator`, `user_id`, `status`, `amount`, `create_date`) VALUES ('',i_id,'',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, '', NOW(), '', NOW(),_total_money - _stay_out, _total_money, _time_mi);

    # 给订单表绑定任务

    update other_order set issue_id = _newid where ISNULL(issue_id);

    # 如果事务发生错误，就进行回滚

    IF err= THEN

            # 如果发生回滚就表示发生发生错误

            set result='发生了回滚,不进行发放';

            ROLLBACK;

    ELSE

        commit;

    end if;

    #关闭游标

  CLOSE  cur;

END