mysql存储过程跨库事务_mysql数据库存储过程之事务

实例：

例子

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); ---调用存储过程