MYSQL

DDL

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

DML

INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-01-01', '张三', '12345678901', 20, '男', '我是张三，喜欢读书。'),
('2023-02-01', '李四', '23456789012', 21, '男', '我是李四，喜欢运动。'),
('2023-03-01', '王五', '34567890123', 22, '女', '我是王五，喜欢音乐。'),
('2023-04-01', '赵六', '45678901234', 23, '女', '我是赵六，喜欢画画。'),
('2023-05-01', '孙七', '56789012345', 24, '男', '我是孙七，喜欢旅行。'),
('2023-06-01', '周八', '67890123456', 25, '女', '我是周八，喜欢摄影。'),
('2023-07-01', '吴九', '78901234567', 26, '男', '我是吴九，喜欢编程。');

DQL

DELIMITER //

CREATE PROCEDURE InsertStudent(
    IN p_userName VARCHAR(255),
    IN p_introduce TEXT
)
BEGIN
    -- 声明变量以检查userName是否以指定字符开头
    DECLARE v_invalid_start CHAR(1) DEFAULT NULL;

    -- 检查userName是否以张、王、李、赵开头
    IF LEFT(p_userName, 1) = '张' OR LEFT(p_userName, 1) = '王'
        OR LEFT(p_userName, 1) = '李' OR LEFT(p_userName, 1) = '赵' THEN
        -- 如果是以这些字符开头，则设置标志
        SET v_invalid_start = 'Y';
    END IF;

    -- 如果userName不是以指定字符开头，则插入数据
    IF v_invalid_start IS NULL THEN
        INSERT INTO student (createDate, userName, phone, age, sex, introduce)
        VALUES (NOW(), p_userName, '123456789012345', 20, 'M', p_introduce);
    ELSE
        -- 如果userName无效，可以记录日志或返回错误消息
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'UserName cannot start with 张, 王, 李, or 赵.';
    END IF;
END //

DELIMITER ;
CALL InsertStudent('刘晓明', 'This is Liu Xiaoming.');