# 歌曲
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('发如雪', '周杰伦');

transaction roll back