/*
一、
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;