/* 一、 1、编写存储过程实现转账过程 2、提示: (1)涉及t_user、t_account表,新建t_transfer_log表 (2)转账金额小于100,不收手续费,在100-1000范围收取0.1%的手续费,在1000-5000收取0.3%手续费,在5000-10000收取0.5%,大于10000收取1%手续费,封顶50块。 (3)记录转账日志 */
# 新建t_transfer_log表 create table t_transfer_log( transfer_id int(11) primary key auto_increment comment '转账编号', sender_id int(11) not null comment '转出人编号', receiver_id int(11) not null comment '收款人编号', amount double(11,2) not null default 0.00 comment '转账金额', fee double(11,2) not null default 0.00 comment '手续费', status char(1) not null default '0' comment '转账状态:0-开始转账;1-转账中;2-转账成功;3-转账失败', transfer_date datetime not null default current_timestamp comment '转账时间', update_date datetime not null default current_timestamp on update current_timestamp comment '更新时间' )engine=innodb default charset=utf8 comment '转账记录表';
# 编写存储过程实现转账过程 CREATE PROCEDURE proc_transfer (IN in_sender_id INT, IN in_receiver_id INT, in in_amount DOUBLE, OUT out_msg VARCHAR (20)) begin
# 转出人账余额 declare v_sender_balance double default 0.00;
# 手续费 declare v_fee double default 0.00;
# 转账记录id declare v_transfer_id int;
# 语句执行标志 declare done int default 0;
# 语句执行错误修改执行标志 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
# 获取转账记录的id select max(transfer_id) into v_transfer_id from t_transfer_log where sender_id = in_sender_id;
# 查询转出人账户余额 select balance into v_sender_balance from t_account where user_id = in_sender_id;
# 计算转账金额 if in_amount <= 100 THEN set v_fee = 0; elseif in_amount > 100 and in_amount <= 1000 then set v_fee = in_amount * 0.001; elseif in_amount > 1000 and in_amount <= 5000 then set v_fee = in_amount * 0.003; elseif in_amount > 5000 and in_amount <= 10000 then set v_fee = in_amount * 0.005; else set v_fee = in_amount * 0.01; end if;
# 判断手续费是否到达50封顶 if v_fee > 50 then set v_fee = 50; end if;
# 创建转账日志 insert into t_transfer_log(sender_id, receiver_id, amount, fee) values (in_sender_id, in_receiver_id, in_amount, v_fee);
# 开启事务 start transaction;
# 判断账户余额是否大于或者等于转账金额+手续费 if v_sender_balance >= in_amount + v_fee THEN
# 修改转账记录的状态为转账中 update t_transfer_log set status = '1' where transfer_id = v_transfer_id;
# 转出人余额减区转账金额和手续费 update t_account set balance = balance - in_amount - v_fee where user_id = in_sender_id; # 收款人余额减区转账金额 update t_account set balance = balance + in_amount where user_id = in_receiver_id;
# 修改转账记录的状态为转账失败 update t_transfer_log set status = '2' where transfer_id = v_transfer_id; # 提示转账结果 set out_msg = '转账成功!';
else # 提示转账结果 set out_msg = '余额不足,转账失败!'; # 修改转账记录的状态为转账失败 update t_transfer_log set status = '3' where transfer_id = v_transfer_id;
end if;
if done = 1 then # 回滚事务 rollback; # 修改转账记录的状态为转账失败 update t_transfer_log set status = '3' where transfer_id = v_transfer_id; # 提示转账结果 set out_msg = '系统异常,转账失败!'; else # 提交事务 commit; end if;
end;
# 测试 set @msg = ''; call proc_transfer(1, 2, 10000, @msg); select @msg; |
|