我想在下面的MySQL存储过程中获得一些帮助,当第一个If else语句通过,而第二个出错时,它显示了我在ELSE语句中输入的错误消息,但我想回滚第一个If else语句if第二个错误出来了。我该怎么做?
DELIMITER $$
CREATE PROCEDURE sp_Example()
BEGIN
SET @countOfTable = (select count(*) from Db1.tbl1);
IF @countOfTable > 0
THEN
INSERT INTO db1.tblLog
(
BatchStoreId,
Origin
)
SELECT
(SELECT DISTINCT SomeID FROM db1.tbl1) as SomeId,
(SELECT Origin FROM db1.tblKey WHERE Origin = 'ThisText') as Origin;
ELSE
SELECT 'table is empty!' as ErrorMessage;
End if;
SET @countOpp = (SELECT count(*) FROM db1.tbl2),
@MR = (SELECT DISTINCT SomeID FROM db1.tbl1),
@Opp = (SELECT DISTINCT OtherIDFROM db1.tbl2);
IF @countOpp > 0 and @MR = @Opp
THEN
INSERT INTO db1.tbllog
(
SomeID,
Origin
)
SELECT
(SELECT DISTINCT SomeID FROM db1.tbl2) as SomeID,
(SELECT Origin FROM db1.tblkey WHERE Origin = 'Secondary Text') as Origin;
ELSE
SELECT 'SomeID's Do Not Match in tables db1.tbl1 and db1.tbl2' as ErrorMessage;
End if;
END;
最佳答案
您应该使用START TRANSACTION;
,然后在else语句上使用ROLLBACK
,然后在过程结束时执行commit
。我对它的外观做了一个简单的示例:
DELIMITER $$
CREATE PROCEDURE sp_example()
BEGIN
-- Starting the transaction
START TRANSACTION;
INSERT INTO `test`.`owner` (`name`) VALUES ('Insert Cubias');
IF 1 > 2 THEN
INSERT INTO `test`.`owner` (`name`) VALUES ('McCubo');
ELSE
SELECT 'Rollback transaction' as ErrorMessage;
-- No records will be inserted
ROLLBACK;
End if;
-- commit changes to database
COMMIT;
END;
$$
您应该查看官方文档:https://dev.mysql.com/doc/refman/5.7/en/commit.html以获取更多信息。
干杯!
关于mysql - 如果第二条if-else语句失败,则对第一个If-else语句进行MySQL存储过程回滚,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/49680754/