MySQL 事务 ROLLBACK 示例代码

MySQL 事务 ROLLBACK 示例代码

mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc(
    ->   in_from INTEGER,
    ->   in_to   INTEGER,
    ->   in_amount DECIMAL(8,2))
    -> BEGIN
    ->
    ->   DECLARE txn_error INTEGER DEFAULT 0 ;
    ->
    ->   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
    ->     SET txn_error=1;
    ->   END;
    ->
    ->   SAVEPOINT savepoint_tfer;
    ->
    ->   UPDATE employee
    ->      SET salary=in_amount
    ->    WHERE id=in_from;
    ->
    ->   IF txn_error THEN
    ->     ROLLBACK TO savepoint_tfer;
    ->     SELECT 'Transfer aborted ';
    ->   ELSE
    ->     UPDATE employee
    ->        SET salary=in_amount
    ->      WHERE id=in_to;
    ->
    ->      IF txn_error THEN
    ->         ROLLBACK TO savepoint_tfer;
    ->         SELECT 'Transfer aborted ';
    ->      END IF;
    ->   END IF;
    ->
    -> END$$
Query OK, 0 rows affected (0.00 sec)