存储过程--SQL

存储过程

DELIMITER $$

CREATE PROCEDURE InsertUserWithRole(
    IN p_username VARCHAR(50),
    IN p_password VARCHAR(255),
    IN p_gender ENUM('男', '女'),
    IN p_email VARCHAR(100),
    IN p_role_name VARCHAR(50)
)
BEGIN
    DECLARE user_id_var INT;
    DECLARE role_id_var INT;

    -- 检查角色是否存在，如果不存在则插入
    SELECT role_id INTO role_id_var FROM Roles WHERE role_name = p_role_name;
    IF role_id_var IS NULL THEN
        INSERT INTO Roles (role_name) VALUES (p_role_name);
        SET role_id_var = LAST_INSERT_ID();
    END IF;

    -- 插入新用户
    INSERT INTO Users (username, password, gender, email)
    VALUES (p_username, p_password, p_gender, p_email);
    SET user_id_var = LAST_INSERT_ID();

    -- 插入用户角色关联
    INSERT INTO UserRoles (user_id, role_id)
    VALUES (user_id_var, role_id_var);

    -- 输出或处理结果（如果需要）
    SELECT CONCAT('User with ID: ', user_id_var, ' has been assigned role: ', p_role_name) AS message;
END $$

DELIMITER ;
CALL InsertUserWithRole('小红', '123', '女', 'xiaohong@example.com', '校长');