实例:
例子
DELIMITER $
DROP PROCEDURE IF EXISTS `insert_corp`$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `mop`.`insert_corp`(IN loop_time BIGINT)
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE corp_parameter BIGINT;
-- DECLARE duplicate_key int default 0;
-- DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key=1;
SET autocommit=0; -- 关闭事务自动提交
WHILE i<=loop_time DO
SET corp_parameter=CONCAT(20220000000+i); -- 因为表corp与corp_order表中的eccode与ecname不可重复,用循环变量生成eccode和ecname的值
INSERT INTO corp (eccode,ecname,create_date) VALUES (corp_parameter,corp_parameter,NOW()); --向表中插入数据的sql语句
INSERT INTO corp_order (ecname,eccode,order_date,) VALUES (corp_parameter,corp_parameter,NOW());
/*
if duplicate_key=1 then
SELECT concat ('failed to insert ','corp') as "Result";
else
SELECT concat ('created success','corp') as "Result";
end if;
*/
IF MOD(i,10000)=0 THEN --每一万条提交一次事务
COMMIT;
END IF;
SET i=i+1;
END WHILE;
END$
DELIMITER ;
call insert_corp(100000); ---调用存储过程