MySQL数据库_if判断用法_存储过程添加数据

添加数据的存储过程，要求2个参数，参数为userName与introduce，插入的数据userName不能以张王李兆开头

1.DDL

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    createDate DATE NOT NULL,
    userName VARCHAR(50) NOT NULL,
    phone VARCHAR(15),
    age INT,
    sex CHAR(1),
    introduce TEXT
);

2.DML

INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES
('2023-07-01', '张三', '13800138000', 20, 'M', '我是张三，喜欢篮球。'),
('2023-07-02', '李四', '13900139000', 21, 'F', '我是李四，喜欢音乐。'),
('2023-07-03', '王五', '13700137000', 22, 'M', '我是王五，喜欢旅行。'),
('2023-07-04', '赵六', '13600136000', 23, 'F', '我是赵六，喜欢画画。'),
('2023-07-05', '孙七', '13500135000', 24, 'M', '我是孙七，喜欢摄影。'),
('2023-07-06', '周八', '13400134000', 25, 'F', '我是周八，喜欢跳舞。'),
('2023-07-07', '吴九', '13300133000', 26, 'M', '我是吴九，喜欢游泳。');

3.DQL

DELIMITER //

CREATE PROCEDURE InsertStudent(
    IN p_userName VARCHAR(50),
    IN p_introduce TEXT
)
BEGIN
    -- 检查userName是否以"张"、"王"、"李"或"兆"开头
    IF LEFT(p_userName, 1) NOT IN ('张', '王', '李', '兆') THEN
        -- 插入数据到student表
        INSERT INTO student(createDate, userName, introduce)
        VALUES(CURDATE(), p_userName, p_introduce);
    ELSE
        -- 如果userName不合法，则抛出异常
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Invalid userName: username cannot start with 张, 王, 李, or 兆';
    END IF;
END //

DELIMITER ;
-- 调用数据
CALL InsertStudent('your_username', 'your_introduction');
-- 查询
SELECT * FROM student WHERE userName='your_username';