MySQL 通过存储过程批量插入数据

-- 建立 emp 表
CREATE TABLE emp(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, *编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", *名字*/
job VARCHAR(9) NOT NULL DEFAULT "", *工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, *上级编号*/
hiredate DATE NOT NULL, *入职时间*/
sal DECIMAL(7,2) NOT NULL, *薪水*/
comm DECIMAL(7,2) NOT NULL, *红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 *部门编号*/
)ENGINE=INNODB DEFAULT CHARSET=GBK;

delimiter $$$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
while i < n DO
SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END while;
RETURN return_str;
END $$$

-- 用于随机产生部门编号

delimiter $$$
CREATE FUNCTION rand_num()
RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(100+RAND()*10);
RETURN i;
END $$$

delimiter $$$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
# set autocommit = 0 把 autocommit 设置成 0
SET autocommit = 0;
repeat
SET i = i + 1;
INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES ((START + i),rand_string(6),
'SALESMAN', 0001, CURDATE(), 2000, 400, rand_num());
until i = max_num
END repeat;
# 循环之后在提交
COMMIT;
END $$$

-- 执行存储过程，往 dept 表添加随机数据

delimiter $$$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
repeat
SET i = i + 1;
INSERT INTO dept(deptno,dname,ioc) VALUES ((START + i),rand_string(10),rand_string(8));
until i = max_num
END repeat;
COMMIT;
END $$$

-- 往 dept 表中插入数据
call insert_dept(100,10);