MYSQL 使用事务

 

直接上代码，ID是唯一标识

CREATE PROCEDURE PRO2()
  BEGIN
      DECLARE t_error INTEGER;
      DECLARE    CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;
 
      START TRANSACTION;
         INSERT INTO temp(ID,tempName) VALUES    ('1','2');
          INSERT INTO temp(ID,tempName)  VALUES    ('1', '3'); 
         IF t_error = 1 THEN
             ROLLBACK;
         ELSE
             COMMIT;
         END IF;
 END

 下面开始填坑

 1、在存储过程中使用变量时，会出现

　　错误代码： 1337

　　Variable or condition declaration after cursor or handler declaration

游标(cursor)必须在声明处理程序之前被声明，并且变量和条件必须在声明游标或处理程序之前被声明。

错误写法

CREATE PROCEDURE PRO2()
  BEGIN
      DECLARE t_error INTEGER;
      DECLARE    CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;
 
       DECLARE t_text INT DEFAULT 0;-- 错误写法

      START TRANSACTION;
         INSERT INTO temp(ID,tempName) VALUES    ('1','2');
          INSERT INTO temp(ID,tempName)  VALUES    ('1', '3'); 
         IF t_error = 1 THEN
             ROLLBACK;
         ELSE
             COMMIT;
         END IF;
 END

 正确写法是需要将定义变量放在游标上

CREATE PROCEDURE PRO2()
  BEGIN

      DECLARE t_text INT DEFAULT 0;-- 正确写法

     DECLARE t_error INTEGER;
      DECLARE    CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;
 
	

      START TRANSACTION;
         INSERT INTO temp(ID,tempName) VALUES    ('1','2');
          INSERT INTO temp(ID,tempName)  VALUES    ('1', '3'); 
         IF t_error = 1 THEN
             ROLLBACK;
         ELSE
             COMMIT;
         END IF;
 END