添加语句的存储过程

DDL

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    createDate DATE NOT NULL,
    userName VARCHAR(255) NOT NULL,
    phone VARCHAR(15),
    age INT,
    sex ENUM('M', 'F', 'O') NOT NULL,  -- 假设'M'代表男，'F'代表女，'O'代表其他
    introduce TEXT
);

DELIMITER //

CREATE PROCEDURE InsertStudent(
    IN p_userName VARCHAR(255),
    IN p_age INT,
    IN p_introduce TEXT
)
BEGIN
    DECLARE v_random_date DATE;
    DECLARE v_random_phone VARCHAR(15);
    DECLARE v_random_sex ENUM('M', 'F', 'O');
    DECLARE v_username_prefix VARCHAR(1);

    -- 验证输入参数不为空
    IF p_userName IS NULL OR p_age IS NULL OR p_introduce IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '输入的数据不允许为空';
    END IF;

    -- 验证userName不以张、王、李、赵开头
    SET v_username_prefix = LEFT(p_userName, 1);
    IF v_username_prefix IN ('张', '王', '李', '赵') THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'userName的名称不允许以张、王、李、赵开头';
    END IF;

    -- 生成随机日期
    SET v_random_date = DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND() * 3650) DAY); -- 随机过去10年内的日期

    -- 生成随机电话号码
    SET v_random_phone = CONCAT(
        FLOOR(RAND() * (999999999999999 - 100000000000000 + 1)) + 100000000000000
    );

    -- 生成随机性别
    SET v_random_sex = ELT(1 + FLOOR(RAND() * 3), 'M', 'F', 'O');

    -- 插入数据
    INSERT INTO student (createDate, userName, phone, age, sex, introduce)
    VALUES (v_random_date, p_userName, v_random_phone, p_age, v_random_sex, p_introduce);
END //

DELIMITER ;

CALL InsertStudent('不是张王李赵开头', 20, '这是一个介绍');