一个简单的例子，在存储过程中执行插入数据，任意一条报错后自动回滚。
假设有两张表test1,test2:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| CREATE TABLE `test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` int(11) DEFAULT NULL,
`name` varchar(255) NOT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;
CREATE TABLE `test2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` int(11) DEFAULT NULL,
`address` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;
|
创建存储过程：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
| DROP PROCEDURE IF EXISTS intertTest12;
CREATE PROCEDURE intertTest12
(
userId INT(11),
name varchar(255),
age INT(11),
address VARCHAR(255)
)
BEGIN
DECLARE result_code INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result_code=1;
START TRANSACTION;
INSERT INTO test1 (userId,name, age) VALUES (userId, name,age);
INSERT INTO test2 (userId,address) VALUES (userId, address);
IF result_code = 1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
select result_code;
END ;
|
调用存储过程：
1
| > CALL intertTest12(656465,'小明',7,'南京市')
|