2799 · 创建批量插入数据的存储过程

描述

我们需要向 teachers 表中插入 30000 条测试数据，包含教师姓名 name = ‘teacher’ + 测试 id，（测试 id 从 1 增加到 30000），教师邮箱 email = name + ‘@chapter.com’，教师年龄 age = 26 + (id%20)
请利用 SQl 的存储过程来实现。

输入数据：

teachers 表：
id name email age country

返回结果：
id name email age country
1 ‘teacher1’ ‘teacher1@chapter.com’ 27 ‘’
2 ‘teacher2’ ‘teacher2@chapter.com’ 28 ‘’
3 ‘teacher3’ ‘teacher3@chapter.com’ 29 ‘’
4 ‘teacher4’ ‘teacher4@chapter.com’ 30 ‘’
5 ‘teacher5’ ‘teacher5@chapter.com’ 31 ‘’
6 ‘teacher6’ ‘teacher6@chapter.com’ 32 ‘’
7 ‘teacher7’ ‘teacher7@chapter.com’ 33 ‘’
‘…’ ‘…’ ‘…’ ‘…’ ‘…’

我们需要向 teachers 表中插入 30000 条测试数据，包含教师姓名 name = ‘teacher’ + 测试 id，（测试 id 从 1 增加到 30000），教师邮箱 email = name + ‘@chapter.com’，教师年龄 age = 26 + (id%20)

分析题目要求：

条件一：使用 SQL 存储过程
条件二：向 teachers 表中插入 30000 条测试数据
条件三：教师姓名 name = ‘teacher’ + 测试 id
条件四：教师邮箱 email = name + ‘@chapter.com’
条件五：教师年龄 age = 26 + (id%20)
根据题目要求，大量重复性操作我们会优先使用存储过程来实现，分析题目条件，根据条件推出：

条件一：CREATE PROCEDURE addTeachers()
条件二：WHILE i < 30000 DO
条件三：SET name = CONCAT(‘teacher’, i);
条件四：email = CONCAT(name, ‘@chapter.com’);
条件五：age = 26 + (i%20);

CREATE PROCEDURE AddTeachers()
BEGIN
DECLARE i INT;
DECLARE name VARCHAR(255);
DECLARE email VARCHAR(255);
DECLARE age INT;

SET i = 0;
WHILE i < 30000 DO
    SET i = i + 1;
    SET name = CONCAT('teacher', i);
    SET email = CONCAT(name, '@chapter.com');
    SET age = 26 + (i % 20);
    INSERT INTO teachers(id, name, email, age, country)
    VALUES(i, name, email, age, '');
END WHILE;

END ;