mysql 存储过程——案例

本人手写，已经测试过，正常运行。

实现业务：校验账户流水是否正常，主要操作两张表数据。创建一张临时表进行存储过程中操作。

创建账户流水表语句：

CREATE TABLE `t_account_water` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL COMMENT '收款账号',
  `rec_account_id` bigint(20) NOT NULL COMMENT '账户账号',
  `in_type` varchar(20) NOT NULL COMMENT '进出类型CR("收入"), DR("支出")',
  `trade_no` varchar(50) NOT NULL COMMENT '订单编号',
  `trade_type` varchar(50) NOT NULL COMMENT '业务类型',
  `amount` bigint(20) NOT NULL COMMENT '发生额',
  `begin` bigint(20) DEFAULT NULL COMMENT '期初额',
  `end` bigint(20) DEFAULT NULL COMMENT '期末额',
  `create_time` datetime DEFAULT NULL COMMENT '创建日期',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_u_rec` (`user_id`,`rec_account_id`)
) ENGINE=InnoDB AUTO_INCREMENT=31962 DEFAULT CHARSET=utf8mb4 COMMENT='收支流水实体类'

创建账户表语句：

CREATE TABLE `t_account` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `account_type` varchar(50) NOT NULL COMMENT '账户类型  积分TTP, 金币YEP，群聊币GOLD',
  `user_id` bigint(20) NOT NULL COMMENT '所属账号',
  `amount` bigint(20) NOT NULL COMMENT '余额',
  `freezed` bigint(20) NOT NULL COMMENT '冻结金额',
  `overdraft` varchar(20) NOT NULL COMMENT '是否允许透支',
  `status` varchar(20) NOT NULL COMMENT '状态',
  `create_time` date NOT NULL COMMENT '创建日期',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_u_type` (`user_id`,`account_type`)
) ENGINE=InnoDB AUTO_INCREMENT=547 DEFAULT CHARSET=utf8mb4 COMMENT='账户表'

创建账户流水临时表：--》存储过程主要操作此表

CREATE TABLE t_account_water_temp SELECT tw.*,ta.account_type FROM t_account_water tw LEFT JOIN t_account ta ON ta.id=tw.rec_account_id WHERE 1=2

创建存储过程：（里面有loop的嵌套循环，多级if else判断，局部变量赋值-INTO）

DELIMITER $$ -- 定义结束标识
CREATE PROCEDURE ck_account(IN beginTime DATETIME,IN endTime DATETIME)
BEGIN
    TRUNCATE TABLE t_account_water_temp;-- 每次调用，先清空临时表
    INSERT INTO t_account_water_temp SELECT tw.*,ta.account_type FROM t_account_water AS tw LEFT JOIN t_account ta ON tw.rec_account_id = ta.id 
                        WHERE tw.create_time >= beginTime AND tw.create_time < endTime; -- 插入满足条件的数据
    BEGIN
    -- 定义结束标识
        DECLARE done INT DEFAULT 0;
        DECLARE edone INT DEFAULT 0;
    -- 定义局部变量
        DECLARE a_type VARCHAR(20);
        DECLARE a_user_id BIGINT(20);
        DECLARE a_begin BIGINT;
        DECLARE a_end BIGINT;
        DECLARE a_sum_dr BIGINT;
        DECLARE a_sum_cr BIGINT;
        DECLARE a_last_end BIGINT;
        BEGIN
        DECLARE a_typeList CURSOR FOR SELECT DISTINCT account_type FROM t_account_water_temp;-- 查询出所有账户类型
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;-- 循环结束标识
        OPEN a_typeList;-- 开始游标
            typesLoop:LOOP -- 循环游标开始
                FETCH a_typeList INTO a_type;-- 循环赋值
                IF done = 1 THEN
                    LEAVE typesLoop;-- 跳出游标
                ELSE 
                SET edone = 0;-- 重置标志
                    BEGIN 
                    -- 定义游标
                        DECLARE  a_userIds CURSOR FOR SELECT DISTINCT user_id FROM t_account_water_temp;-- 查出所有id，去重
                        -- 游标结束标识
                        DECLARE CONTINUE HANDLER FOR NOT FOUND SET edone = 1;
                        OPEN a_userIds; -- 开始游标
                            idsLoop:LOOP
                                FETCH a_userIds INTO a_user_id;
                                IF edone = 1 THEN
                                    LEAVE idsLoop;
                                ELSE
                                    -- 查询该账户类型下的，该用户的，begin，end，sumDr，sumCR，last_end
                                    SELECT IFNULL(`begin`,0) INTO a_begin FROM t_account_water_temp WHERE user_id=a_user_id AND account_type=a_type ORDER BY id ASC LIMIT 1;
                                    SELECT IFNULL(`end`,0) INTO a_end FROM t_account_water_temp WHERE user_id=a_user_id AND account_type=a_type ORDER BY id DESC LIMIT 1;    
                                    SELECT IFNULL(SUM(amount),0) INTO a_sum_dr FROM t_account_water_temp WHERE user_id=a_user_id AND account_type=a_type AND in_type='dr';    
                                    SELECT IFNULL(SUM(amount),0) INTO a_sum_cr FROM t_account_water_temp WHERE user_id=a_user_id AND account_type=a_type AND in_type='cr';
                                    SELECT IFNULL((SELECT tw.end AS a_last_end FROM t_account_water AS tw LEFT JOIN t_account AS ta ON tw.rec_account_id = ta.id WHERE tw.user_id=a_user_id AND tw.create_time < beginTime AND ta.account_type=a_type ORDER BY tw.id DESC LIMIT 1),0) INTO a_last_end;
                                    -- INSERT INTO t_mistake(title,content,mistake_type,target,`status`,create_time,remark) VALUES(a_last_end,a_begin,a_end,a_user_id,a_sum_dr,NOW(),a_sum_cr);测试语句
                                    IF (a_begin+a_sum_cr-a_sum_dr != a_end) OR (a_begin != a_last_end) THEN -- 如果数据有问题，则冻结账户，更新数据
                                        UPDATE t_account SET `status`='LOCK' WHERE user_id=a_user_id AND account_type=a_type;
                                        INSERT INTO t_mistake(title,content,mistake_type,target,`status`,create_time,remark) VALUES(a_type,'账户流水异常',4,a_user_id,0,NOW(),'定时处理');
                                    END IF;
                                END IF;                                                                    
                        END LOOP;-- 结束游标
                        CLOSE a_userIds;-- 关闭游标            
                    END;
                END IF;        
            END LOOP;-- 结束游标
            CLOSE a_typeList;-- 关闭游标
            COMMIT;
        END;        
    END;    
END $$

CALL ck_account('2021-06-21 09:32:05','2021-07-01 09:32:05');-- 调用存储过程

DROP PROCEDURE ck_account;-- 删除存储过程

 java代码调用存储过程：以springboot+mybatisPlus为例，与正常调用sql相同，如图：