• 周六. 8月 20th, 2022

5G编程聚合网

5G时代下一个聚合的编程学习网

热门标签

mysql 存储过程——案例

admin

11月 28, 2021

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

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

创建账户流水表语句:
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相同,如图:

发表回复

您的电子邮箱地址不会被公开。