As a mostly Java developer, I find the explicit, manual transaction management in PL/SQL a bit low level and difficult to maintain. In Java EE (and Spring), transaction propagation is a concept where scopes (mainly methods) can be declaratively annotated with a propagation type, which governs the transaction behaviour of such a scope. PL/SQL supports one of these propagation types through PRAGMA AUTONOMOUS_TRANSACTION, which is really useful e.g. for logging things in a separate transaction, regardless of the success / failure of the "main" transaction.
What I'm suggesting here is a new PRAGMA NESTED_TRANSACTION, which could be used in blocks and procedures / functions as such:
CREATE TABLE t (i INT PRIMARY KEY);
/
DECLARE
v NUMBER;
BEGIN
-- Implicit transaction begin
INSERT INTO t VALUES (1);
-- Implicit savepoint
DECLARE
PRAGMA nested_transaction;
BEGIN
INSERT INTO t VALUES (2);
INSERT INTO t VALUES (1); -- This fails with dup_val_on_index
END;
-- Since the scope (BEGIN .. END) was exited with an exception,
-- there is an implicit ROLLBACK to the implicit savepoint
EXCEPTION
WHEN OTHERS THEN
SELECT count(*) INTO v FROM t;
dbms_output.put_line(v); -- This prints 1, because value 2 was rolled back implicitly
END;
/
Essentially, such a new PRAGMA could just be syntax sugar for the following, more explicit and more verbose logic:
DECLARE
v NUMBER;
BEGIN
-- Implicit transaction begin
INSERT INTO t VALUES (1);
-- Explicit savepoint
SAVEPOINT generated_savepoint_1;
BEGIN
INSERT INTO t VALUES (2);
INSERT INTO t VALUES (1); -- This fails with dup_val_on_index
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO generated_savepoint_1;
RAISE;
END;
EXCEPTION
WHEN OTHERS THEN
SELECT count(*) INTO v FROM t;
dbms_output.put_line(v); -- This prints 1, because value 2 was rolled back explicitly
END;
/
While this doesn't look too impressive for anonymous blocks, it is a really really powerful tool when writing stored procedures / functions, where such procedures / functions can guarantee to the caller that their work is either committed atomically or rolled back completely. The main benefit is maintainability. If in the above explicit example, a new exception block would be added, programmers no longer have to remember to check for transaction / rollback semantics, introducing subtle bugs. Also, no one would accidentally roll back the entire transaction because they reference the wrong savepoint or no saveponit at all.
Obviously, such scopes with nested transaction propagation can be further nested, creating more savepoints. E.g.
DECLARE
v NUMBER;
BEGIN
-- Implicit transaction begin
INSERT INTO t VALUES (1);
-- Implicit savepoint
DECLARE
PRAGMA nested_transaction;
BEGIN
INSERT INTO t VALUES (2);
-- Another implicit savepoint here
DECLARE
PRAGMA nested_transaction
BEGIN
INSERT INTO t VALUES (3);
INSERT INTO t VALUES (1); -- This fails with dup_val_on_index
EXCEPTION
WHEN OTHERS THEN NULL; -- Exception is not propagated, but ROLLBACK to inner SAVEPOINT is still done
END;
END;
-- Since the scope (BEGIN .. END) was no longer exited with an exception (it was swallowed)
-- there is no longer any implicit ROLLBACK to the outer SAVEPOINT
SELECT count(*) INTO v FROM t;
dbms_output.put_line(v); -- This prints 2, because value 3 was rolled back implicitly
END;
/
Side-effects:
As is done in Java EE as well, being "inside" such a scope where there exists an implicit transaction propagation context, explicit calls to COMMIT, ROLLBACK, SAVEPOINT are forbidden. They are allowed only in the outer-most scope, where no propagation context exists. Java EE knows more sophisticated transaction propagation types, too, which might be out of scope for PL/SQL. For details, see:
https://docs.oracle.com/javaee/7/tutorial/transactions003.htm