MySQL学习笔记——存储过程

MySQL存储过程是一个事先编译好并存储在数据库中的一组 SQL 语句集合，类似于程序中的子程序或函数。

存储过程可以接受参数、执行一系列 SQL 语句，并返回结果。存储过程可以大大简化复杂的查询和数据处理操作，并提高数据库性能和安全性。

假设有3张表，歌曲表song，歌手表singer，歌曲和歌手的关联表song_singer

# 歌曲
create table `default`.song(
    id int PRIMARY KEY auto_increment,
    name varchar(256) UNIQUE
) engine=InnoDB default charset=utf8;

# 歌手
create table `default`.singer(
    id int PRIMARY KEY auto_increment,
    name varchar(256) UNIQUE
) engine=InnoDB default charset=utf8;

# 关联表
create table `default`.song_singer(
    id int PRIMARY KEY auto_increment,
    song_id int,
    singer_id int,
    UNIQUE KEY unique_index_name (song_id, singer_id)
) engine=InnoDB default charset=utf8;

现在要写一个存储过程，同时添加歌曲，歌手，歌曲和歌手的关联表数据

CREATE PROCEDURE add_song_and_singer(
    IN song_name VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_general_ci,
    IN singer_name VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_general_ci
)
BEGIN

    DECLARE result_code INTEGER DEFAULT 0; -- 定义返回结果并赋初值0
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result_code=1; -- 在执行过程中出任何异常设置result_code为1
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET result_code = 2; -- 如果表中没有下一条数据则置为2

    START TRANSACTION;

    # 写入歌曲
    INSERT INTO song (name) VALUES (song_name);
    SET @new_song_id = LAST_INSERT_ID();

    # 写入歌手
    INSERT INTO singer (name) VALUES (singer_name);
    SET @new_singer_id = LAST_INSERT_ID();

    # 写入关联表
    INSERT INTO song_singer (song_id, singer_id) VALUES (@new_song_id, @new_singer_id);

    -- 可以根据不同的业务逻辑错误返回不同的result_code，这里只定义了1和0
    IF result_code = 1 THEN
        # 回滚
        ROLLBACK;
        select 'transaction roll back';
    ELSE
        # 提交事务
        COMMIT;
    END IF;

END;

调用存储过程

call add_song('发如雪', '周杰伦');

如果过程中任何SQL失败，则输出

transaction roll back

参考：MySQL 存储过程

MySQL存储过程中实现回滚