在 MariaDB 条件下回滚 [英] Rollback under condition in MariaDB

问题描述

我有一个交易，它在循环中减少了带有金额的变量，如果带有金额的变量低于0，则金额应该返回到交易前的值.在这种情况下，如何在 MariaDB 中适当地使用回滚?

I have a transaction which reduces the variable with amount of money in loop, if the variable with money is below 0, the money amount should return to the value before transaction. How can I appropriately use rollback in MariaDB in this case?

---编辑

我有类似的东西，但它不起作用，请查看 if(budget<0) 中的行，因为如果钱低于 0 和一些，但不是全部，进行了迭代并保存到临时表中，该表显示了它们

I have something like that, and it doesn't work, check out the lines in if(budget<0) because if the money is below 0 and some, but not all of them, iterations were made and saved to the temp table, the table shows them

BEGIN
    DECLARE temppesel text;
    DECLARE tempsalary int;
    DECLARE budget int DEFAULT cash;
    DECLARE done bool DEFAULT false;
    DECLARE occ CURSOR FOR (SELECT pesel, pensja FROM pracownicy where zawod=occupation);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

    START TRANSACTION;
        DROP TABLE IF EXISTS temp;
        CREATE TABLE temp ( Result text );
        OPEN occ;
            occ : LOOP
                FETCH occ INTO temppesel, tempsalary;
                SET budget = budget - tempsalary;

                IF(done) THEN
                    LEAVE occ;
                END IF;

                IF(budget<0) THEN
                    ROLLBACK;
                    LEAVE occ;                      
                END IF;

                INSERT INTO temp VALUES (concat('********',substr(temppesel,9,3), ', wyplacono')); 
            END LOOP;
        CLOSE occ;

        SELECT * FROM temp;
        DROP TABLE temp;
    COMMIT;
END

推荐答案

我相信 CREATE TABLE 语句导致事务被提交.这里是导致隐式提交.

I believe that the CREATE TABLE statements are causing the transaction to be committed. Here is a list of commands that cause an implicit COMMIT.

如上述链接所述，您可以将 START TRANSACTION 语句移到 DROP 和 CREATE 命令之后，或者使用 CREATE TEMPORARY TABLE 语法创建临时表:

As the aforementioned link describes, you can either move the START TRANSACTION statement after the DROP and CREATE commands or use the CREATE TEMPORARY TABLE syntax to create a temporary table:

CREATE TEMPORARY TABLE temp ( Result text );

这篇关于在 MariaDB 条件下回滚的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！