如何在MYSQL中实现回滚事务和错误信息

问题描述

我有示例程序.

CREATE PROCEDURE `sample_procedure `
(
IN IDIn bigint(20),
IN MainIDIn bigint(20),
IN NameIn  varchar(20)
)
 READS SQL DATA
    DETERMINISTIC
BEGIN
INSERT INTO tbl_DEPT
(
ID,
Name)
Select 1,'Mohan';

IF NOT EXISTS (SELECT ID FROM tbl_emp  te  WHERE te.ID = IDIn) THEN
INSERT INTO tbl_emp
(
MainID,
Name)
VALUES (MainIDIn,
        NameIn);
ELSE
IF  EXISTS (SELECT ID FROM tbl_emp  te  WHERE te.ID = IDIn) THEN
  UPDATE tbl_emp
  set
    MainID =MainIDIn,
    name = NameIn
  WHERE te.ID= IDIn;
  END IF;
  END IF;
END

调用 sample_procedure(1,2,Sampl123)

我只是将一些不相关的数据发送到程序中，以便程序失败.但是我们需要如何实现回滚意味着它应该在没有将记录插入 tbl_DEPT 的情况下进入起始状态.

I'm just sending some irrelevant Data into the Procedure so that procedure gets failed . But how we need to implement roll back means it should come to the starting state with out Inserting Records into the tbl_DEPT also.

在 T-SQL 中，我们将有

IN T-SQL we will have

BEGIN
    SET NOCOUNT ON
    BEGIN TRANSACTION
    BEGIN TRY
        SET @OUT = "success";
        COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
        set @out = 'not success';
        ROLLBACK TRANSACTION
    END CATCH
END

这种 TRY CATCH 块并捕获错误

this kind of TRY CATCH blocks and to capture Error

 "ERROR_NUMBER() AS ErrorNumber,
    ERROR_SEVERITY() AS ErrorSeverity"

以同样的方式在 MYSQL 中，我正在寻找 TRY CATCH 和 ROLL BACK Mechanism .

In the same way in MYSQL I'm looking for TRY CATCH and ROLL BACK Mechanism .

如果过程失败，它应该回滚并且不加载任何表.

IF Procedure fails it should ROLL BACK and Not to load in any of the table .

任何人都可以在 MYSQL 中推荐我.

Can any one Suggest me in MYSQL.

推荐答案

Mysql 不使用 try/catch.如果使用处理程序，例如将终止 SP 执行的 EXIT 处理程序:

Mysql does not use try/catch. If uses handlers, e.g. the EXIT handler that will terminate the execution of the SP:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    -- other logic
END;

还有其他类型的处理程序.

There are other type of handlers as well.

这篇关于如何在MYSQL中实现回滚事务和错误信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！