在存储过程中使用mysql事务

I want to do sth like below .There is one procedure which contains two other procedures. The first one inserts some date into my 'User' table and the second do the same to some other tables. The problem is , when I call this SP with a wrong 'CountryID' which leads the process to an error as expected, the first SP (InsertUsername) commits! obviously what I want is having a roll back after this error .

我想做下面的事情。有一个程序包含另外两个程序。第一个在我的'User'表中插入一些日期,第二个在其他表中执行相同的操作。问题是,当我使用错误的“CountryID”调用此SP时,会导致进程出现预期的错误,第一个SP(InsertUsername)提交!显然我想要的是在此错误后回滚。

BEGIN

START TRANSACTION ;

开始交易;

SET @Username = _Username;

CALL InsertUsername ( @Username , @UserID );

CALL InsertUsername(@Username,@ UserID);

CALL InsertAddress ( @UserID , _CountryID , _AdderssText , _PostalCode );

CALL InsertAddress(@ UserID,_CountryID,_AdderssText,_PostalCode);

COMMIT ;

END

2 个解决方案

#1

I recently found that transactions didn't work as expected until we defined exit handlers in the stored procedures.

我最近发现,在我们在存储过程中定义退出处理程序之前,事务没有按预期工作。

We created a fairly crude one-liner for use as a default:

我们创建了一个相当粗略的单行使用作为默认值:

declare exit handler for SQLWARNING, SQLEXCEPTION begin show warnings limit 5; rollback; end;

#2

like simon.evans said I finally found the correct answer and it worked for me so I want to add more details :

像simon.evans说我终于找到了正确答案,它对我有用,所以我想添加更多细节:

CREATE PROCEDURE `add`(IN serial_number VARCHAR(20), IN tarnsaction_type INT
, OUT errcode VARCHAR(3))
BEGIN

DECLARE      my_balance NUMERIC(15,3);
DECLARE EXIT HANDLER FOR SQLEXCEPTION 
  BEGIN

  ROLLBACK;
  SET errcode = '058';
  END;

 CALL procedure2(p_account_id,v_account_balance);
 #do what ever you want to do here

END$$

If you have a question ask me because I had the same problem and finally I found this answer :)

如果你有问题问我,因为我有同样的问题,最后我找到了这个答案:)

#1