I have several inserts in my stored procedure. But if any block has an error, all made inserts should be reverted. This means all the operations inside the stored procedure should be atomar. Concretely in my example below, when block 2 failed, than all the inserts from block 1 should be reverted. How can I do it?
DECLARE C1 CURSOR FOR S1;
--Block 1
PREPARE S1 FROM
'WITH TEMP AS
(
SELECT *
FROM TABLEE
WHERE ID = 2
),
TEMP_1 AS
(
SELECT COUNT(1) AS ID
FROM NEW TABLE
(
INSERT INTO TABLE_A (Col_1, Col_2)
SELECT Col_1, ''' || V || '''
FROM TABLE_A
JOIN TABLEE ON ID = Col_1
)
),
SELECT 1
FROM SYSIBM.SYSDUMMY1';
OPEN C1;
CLOSE C1;
--Block 2
PREPARE S1 FROM
'WITH TEMP AS
(
SELECT *
FROM TABLEEX
WHERE ID = 2
),
TEMP_1 AS
(
SELECT COUNT(1) AS ID
FROM NEW TABLE
(
INSERT INTO TABLE_AA (Col_1, Col_2)
SELECT Col_1, ''' || V || '''
FROM TABLE_AA
JOIN TABLEE ON ID = Col_1
)
),
SELECT 1
FROM SYSIBM.SYSDUMMY1';
OPEN C1;
CLOSE C1;
Example.
--#SET TERMINATOR @
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEST_SP (I INT) WITH REPLACE ON COMMIT PRESERVE ROWS@
COMMIT@
INSERT INTO SESSION.TEST_SP (I) VALUES 1@
BEGIN
--/*
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
--ROLLBACK TO SAVEPOINT SP1;
END;
--*/
--SAVEPOINT SP1 ON ROLLBACK RETAIN CURSORS;
INSERT INTO SESSION.TEST_SP (I) VALUES 2;
SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT = 'Error!';
END@
COMMIT@
SELECT * FROM SESSION.TEST_SP@
If you run this script as is with autocommit switched off, you get 2 rows in the session table. The same is when the exception handler definition is commented out entirely.
But if you uncomment the 2 commented out lines (with SAVEPOINT), you get 1 row in this table (inserted by the 1-st standalone INSERT) - all the changes made by the BEGIN END block will be rolled back by the ROLLBACK TO SAVEPOINT statement.
Note, that you could achieve the same with an UNDO handler (see handler-declaration), but it's allowed in the inlined compound sql only (BEGIN ATOMIC ... END), and you can't use it since you need dynamic sql (PREPARE).