mysql find or create 事务_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