存储过程:DELIMITER $$ USE `test`$$ DROP PROCEDURE IF EXISTS `pro_hsnzmt`$$ CREATE PROCEDURE `pro_hsnzmt`() BEGIN DECLARE v_STOP INT DEFAULT 1; DECLARE EXIT HANDLER FOR SQLSTATE '02000' /**包含游标not found*/ BEGIN ROLLBACK; END; DECLARE EXIT HANDLER FOR SQLEXCEPTION SET autocommit=0; START TRANSACTION; DROP TABLE IF EXISTS shiwu; CREATE TABLE `shiwu` ( `asd` VARCHAR(5) DEFAULT NULL ) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO shiwu (asd) VALUES ('sda'); INSERT INTO shiwu (asd) VALUES ('sda'); INSERT INTO shiwu (asd) VALUES ('sda'); INSERT INTO shiwu (asd) VALUES ('sdfsdfsdfsdfsdf'); COMMIT; END; END$$DELIMITER ;
DROP PROCEDURE IF EXISTS inserOrUpdate; CREATE PROCEDURE inserOrUpdate() BEGIN #-------------------- #SQLEXCEPTION 对应存储过程执行中所有异常 #@Auth: #@time: #-------------------- #标志是否出错 DECLARE errno TINYINT DEFAULT '0'; #如sql异常,将errno设置为1且后续执行退出 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK;SET errno = 1; END;#开启事务 START TRANSACTION; insert into shiwu (str) values ('1'); insert into shiwu (str) values ('2'); insert into shiwu (str) values ('3'); #update shiwu set str1 = "123" where id = 2; insert into shiwu (str) values ('1234'); #errno为1,事务回滚sql SELECT errno;END; call inserOrUpdate();
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)mysql>
mysql> -- 自动提交功能取消
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> drop table shiwu;
Query OK, 0 rows affected (0.01 sec)mysql> CREATE TABLE `shiwu` (
-> `asd` varchar(5) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)mysql> insert into shiwu (asd) values ('sda');
Query OK, 1 row affected (0.00 sec)mysql> insert into shiwu (asd) values ('sda');
Query OK, 1 row affected (0.00 sec)mysql> insert into shiwu (asd) values ('sda');
Query OK, 1 row affected (0.00 sec)mysql> insert into shiwu (asd) values ('sdfsdfsdfsdfsdf');
ERROR 1406 (22001): Data too long for column 'asd' at row 1
mysql> select * from shiwu;
+------+
| asd |
+------+
| sda |
| sda |
| sda |
+------+
3 rows in set (0.00 sec) -- 有报错,所以就rollback,那么表shiwu将是空表。
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)mysql> select * from shiwu;
Empty set (0.00 sec)mysql>
怎样写成一个存储过程
怎样写成一个存储过程在存储过程里面,写法类似的。
USE `test`$$
DROP PROCEDURE IF EXISTS `pro_hsnzmt`$$
CREATE PROCEDURE `pro_hsnzmt`()
BEGIN
DECLARE v_STOP INT DEFAULT 1;
DECLARE EXIT HANDLER FOR SQLSTATE '02000' /**包含游标not found*/
BEGIN
ROLLBACK;
END;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET autocommit=0;
START TRANSACTION;
DROP TABLE IF EXISTS shiwu;
CREATE TABLE `shiwu` (
`asd` VARCHAR(5) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO shiwu (asd) VALUES ('sda');
INSERT INTO shiwu (asd) VALUES ('sda');
INSERT INTO shiwu (asd) VALUES ('sda');
INSERT INTO shiwu (asd) VALUES ('sdfsdfsdfsdfsdf');
COMMIT;
END;
END$$DELIMITER ;
DROP PROCEDURE IF EXISTS inserOrUpdate;
CREATE PROCEDURE inserOrUpdate()
BEGIN
#--------------------
#SQLEXCEPTION 对应存储过程执行中所有异常
#@Auth:
#@time:
#--------------------
#标志是否出错
DECLARE errno TINYINT DEFAULT '0';
#如sql异常,将errno设置为1且后续执行退出
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK;SET errno = 1; END;#开启事务
START TRANSACTION;
insert into shiwu (str) values ('1');
insert into shiwu (str) values ('2');
insert into shiwu (str) values ('3');
#update shiwu set str1 = "123" where id = 2;
insert into shiwu (str) values ('1234');
#errno为1,事务回滚sql
SELECT errno;END;
call inserOrUpdate();