mysql 存储过程 插入记录_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 ;