Mysql 复习笔记- 基础篇8 [数据库事务概述&事务锁实例]

请参考我的语雀的文档地址

下面是我的写的两个个sql脚本
一 测试转账的sql脚本

-- 初始化链接数据库functionsdb数据库
drop database if exists functionsdb;
create database if not exists functionsdb charset 'utf8';
use functionsdb;

-- 初始化表functionsdb数据库的blanks表
drop table if exists blanks;
create table if not exists functionsdb.blanks
(
    id            int auto_increment comment '唯一标识'
        primary key,
    blanks        varchar(255)                null comment '卡号信息',
    blanks_blanks decimal(24, 2) default 0.00 not null comment '余额',
    constraint blanks_blanks_uindex
        unique (blanks)
)
    comment '银行卡';

INSERT INTO functionsdb.blanks (id, blanks, blanks_blanks) VALUES (1, '张三', 20000.00);
INSERT INTO functionsdb.blanks (id, blanks, blanks_blanks) VALUES (2, '李四', 20000.00);

-- 转账的测试存储过程

set @message = '';

drop procedure if exists transfer;
create procedure transfer(in account_out varchar(50),account_in varchar(50),account_transfer decimal(24,2), out message varchar(2000))
begin
    #声明处理器，遇到 sqlwarning ,not found,sqlexception 错误 进行 rollback(回滚)后 exit(退出)
    declare exit handler for sqlwarning ,not found,sqlexception rollback;

    set message='The server is abnormal, please try again later';

    select count(*) into @account_transfer_in_person from blanks where blanks.blanks=account_in;
    select blanks_blanks,count(id) into @account_transfer_out_person_blanks,@account_transfer_out_person from blanks where blanks = account_out;

    if account_out=account_in then
        set message = concat('You cannot make your own transfers');
    elseif @account_transfer_out_person <= 0 then
        set message = concat('Transfer out account',account_out,'does not exist');
    elseif @account_transfer_in_person<=0 then
        set message = concat('Transfer in account',account_in,'does not exist');
    elseif @account_transfer_out_person_blanks<account_transfer then
        set message = concat('Account',account_out,'’s account balance is insufficient and the transfer failed');
    else
        # 开启事务，进行转账操作
        start transaction;
        update blanks set blanks_blanks=blanks_blanks-account_transfer where blanks=account_out;
        update blanks set blanks_blanks=blanks_blanks+account_transfer where blanks=account_in;
        select blanks_blanks into @Balance from blanks where blanks=account_out;
        set message = concat('转账成功,转出金额',account_transfer,'$,当前余额为',@Balance);
        commit;

    end if;
end;

call transfer('张三','李四',2,@message);

select @message;

二 删除活动数据

-- -----------------------------------------------------------------------------------------------------------------
-- 删除活动数据(Delete activity data)
-- @auther Void Bug
-- @version 2021-11-18
-- -----------------------------------------------------------------------------------------------------------------

-- 新建数据库
drop database if exists functionsdb;
create database if not exists functionsdb charset 'utf8';
-- 新建测试表
use functionsdb;
drop database if exists event;
create table if not exists event
(
    id          int auto_increment comment '活动编号'
        primary key,
    title       varchar(128) null comment '活动主题',
    content     text         null comment '活动内容',
    username    varchar(50)  null comment '创建人',
    create_time datetime     not null comment '创建时间'
)
    comment '活动信息记录表' charset 'utf8' engine =InnoDB;

drop database if exists event;
create table if not exists event_commented
(
    id          int auto_increment comment '评论编号'
        primary key,
    event_id    int          not null comment '活动id',
    title       varchar(128) null comment '评论标题',
    comment     text         not null comment '评论内容',
    username    varchar(128) not null comment '评论人',
    create_time datetime     not null comment '评论时间'
)
    comment '评论信息表' charset 'utf8' engine =InnoDB;

-- 插入测试数据
INSERT INTO functionsdb.event (id, title, content, username, create_time) VALUES (1, '吃辣大赛', '比赛吃辣椒', '鑫阳', '2021-11-19 14:21:50');
INSERT INTO functionsdb.event (id, title, content, username, create_time) VALUES (2, '百米赛跑', '1500米赛跑', '志鹏', '2021-11-12 13:23:45');

INSERT INTO functionsdb.event_commented (id, event_id, title, comment, username, create_time) VALUES (1, 1, '参加不了', '从小不吃辣', '少魄', '2021-11-19 14:23:51');
INSERT INTO functionsdb.event_commented (id, event_id, title, comment, username, create_time) VALUES (2, 1, '必得冠军', '四川人', '李四', '2021-08-19 14:24:25');
INSERT INTO functionsdb.event_commented (id, event_id, title, comment, username, create_time) VALUES (3, 2, '必破八秒', '属火箭的', '王五', '2021-08-23 14:25:12');
INSERT INTO functionsdb.event_commented (id, event_id, title, comment, username, create_time) VALUES (4, 2, '眨眼的事情', '火箭升空', '鑫阳', '2021-11-19 14:26:37');

-- 初始化存储过程
drop procedure if exists del_event_commented;

create procedure del_event_commented(event_name varchar(128), out message varchar(1000))
begin
    -- 声明错误处理器
    declare exit handler for sqlwarning ,not found,sqlexception rollback;
    -- 初始化错误类型
    set message='The server is abnormal, please try again later';
    -- 查询活动是否存在和活动id
    select count(*),id into @number,@event_id from event where title=event_name;

    if (@number > 0) then
        -- 活动存在
        delete from event where id=@event_id;
        delete from event_commented where event_id=@event_id;
        set message='successfully deleted';
    else
        -- 活动不存在
        set message='The activity does not exist, please check if the entered information is correct';
    end if;
end;

-- 测试是否正确
call del_event_commented('百米赛跑',@message);
select @message;