mysql - 回滚后返回空表

Question

我正在编写一个过程，如果没有任何回滚，我希望它返回表，如果至少有 1 次回滚，则返回空表/什么也不返回。

DELIMITER $$
CREATE PROCEDURE payment(IN amount int, IN profession varchar(50))
BEGIN    
DECLARE done BOOL DEFAULT FALSE;
DECLARE salary INT;
DECLARE pes VARCHAR(11);
DECLARE summary INT DEFAULT 0;

DECLARE employee_cursor CURSOR FOR (SELECT RIGHT(PESEL,3), pensja FROM Pracownicy WHERE zawod=profession);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

DROP TEMPORARY TABLE IF EXISTS tmp1;
CREATE TEMPORARY TABLE tmp1(pesel char(11), status varchar(20) DEFAULT 'wyplacono');

SET AUTOCOMMIT =0;
START TRANSACTION;
    OPEN employee_cursor;
    readLoop : LOOP
        FETCH employee_cursor INTO pes,salary;
        IF done THEN
            LEAVE readLoop;
        END IF;

        SET summary = summary + salary;

        IF( summary > amount ) THEN
            ROLLBACK;
        END IF;

        INSERT INTO tmp1(pesel) VALUES(CONCAT('********',pes));

    END LOOP;
    CLOSE employee_cursor;
    COMMIT;
    SELECT * from tmp1;    
END $$
DELIMITER ;

到目前为止，当它不回滚时它工作得很好，但是

INSERT INTO tmp1(pesel) VALUES(CONCAT('********',pes));

似乎忽略了事务:/

Answer 1

将回滚测试移到循环之外。