MYSQL 存储过程的事务

MYSQL 存储过程事务

CREATE TABLE `tbl01` (
  `id` INT(11) NOT NULL,
  `name` VARCHAR(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

DELIMITER $$

DROP PROCEDURE IF EXISTS tran_test $$
CREATE PROCEDURE tran_test(
    id1 INT, decs1 VARCHAR(10), 
    id2 INT, decs2 VARCHAR(10), 
    OUT res VARCHAR(10))
BEGIN
  DECLARE t_error INTEGER DEFAULT 0;  
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1 ;        
  START TRANSACTION;  
  UPDATE tbl01 SET `name` = decs1 WHERE id = id1;
  INSERT INTO tbl01 (id, `name`) VALUES (id2, decs2);
  IF t_error=1 THEN  
    SET res = 'REEOR' ;
    ROLLBACK; 
  ELSE  
    SET res = 'SUCCESS' ; 
    COMMIT; 
  END IF;   

END$$

调用:

CALL tran_test(1, 'aa', 12, 'll', @res);
SELECT @res;