Mysql Stored Procedure transaction rollback and mysql Stored Procedure transaction

Mysql Stored Procedure transaction rollback and mysql Stored Procedure transaction

SQL Process Code:

DELIMITER $$CREATE DEFINER=`root`@`localhost` PROCEDURE `test_procedure`()BEGINDECLARE errno int;declare continue HANDLER for sqlexceptionbegin rollback;set errno=1;end;start transaction;set errno=0;insert into test(name) values ('kaka');insert into test(id, name) values(1,'papa');commit;select errno;END

Process description:

1. A record (1, 'baby') already exists in the table ');

2. Call the test stored procedure;

3. This process first declares the error variable and an SQL Exception Handling handler. When this handler is triggered, the transaction will be rolled back and the error is set to 1;

At the beginning of the transaction, set error to 0, insert a data name to 'kaka ', insert another data (1, 'Papa'), and then commit;

However, when the data is inserted for the second time, the primary key id already exists. Here, an exception is reported, triggering our SQL Exception Handling handler. Implement rollback and set error to 1;

View the error value;

4. After calling the stored procedure, check whether the data in the test table is rolled back;

Before calling:

Calling:

After the call:

Database creation SQL:

CREATE DATABASE  IF NOT EXISTS `test`;USE `test`;DROP TABLE IF EXISTS `test`;CREATE TABLE `test` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(45) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

To sum up, we can declare an exception handling handler in the stored procedure to make our transactions roll back.