mysql存储过程批量插入数据

DROP TABLE IF EXISTS TeachersInfo;

 

CREATE TABLE TeachersInfo
(
id INT NOT NULL AUTO_INCREMENT,
teacherCode VARCHAR(20) NULL,
teacherName VARCHAR(30) NULL,
roleID VARCHAR(20) NULL,
SUBJECT INT NULL,
CONSTRAINT PK_TEACHERSINFO PRIMARY KEY clustered (id)
);

COMMENT ON TABLE TeachersInfo IS
'教师表';

 

#创建存储过程导入数据

DELIMITER $$

CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `basedatabase`.`teachersinfoproc`(IN s INT)
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
DECLARE i INT;
DECLARE tcode VARCHAR(50);
SET i = 0;
WHILE i < s DO
SET tcode = 'tcode' + i;
INSERT INTO `teachersinfo` (
`teacherCode`,
`teacherName`,
`roleID`,
`subject`
)
VALUES
(
tcode,
'teacherName',
'roleID',
'subject'
) ;

SET i = i+1;
END WHILE;

END$$

DELIMITER ;