问题描述
我有示例程序.
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中实现回滚事务和错误信息的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!