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 ;