Transaction commits and rollbacks in stored procedures

The behavior of a stored procedure depends on whether it is called as a singleton SQL statement or within a SQL transaction block.

When a stored procedure is called as a singleton SQL statement, the changes made by the stored procedure are committed when the procedure exits or rolled back if the procedure aborts.

CALL MYPROCEDURE(123);

If the procedure issues a COMMIT or ROLLBACK statement within the procedure body, all changes made before the transaction statement are committed or rolled back, and the system implicitly starts a new transaction. All statements after the transaction command continue to run as a single multi-statement command, not as individual commands inside of a transaction block. When the stored procedure exits, any uncommitted changes are committed or rolled back.

A sample procedure called test3 follows.

CREATE PROCEDURE TEST3()
LANGUAGE NZPLSQL
RETURNS INT AS
BEGIN_PROC
BEGIN
    statement1;
    COMMIT;
    statement2;
    statement3;
    RETURN 1234;
END;
END_PROC;

Assume that you run the test3 procedure as a singleton SQL statement such as EXECUTE test3(). The procedure executes statement1 and commits that change. The procedure then runs statement2 and statement3, returns the 1234 value, and exits. If the statements complete without error, the statement2 and statement3 changes are committed when the procedure exits.

Assume that you run the procedure inside a BEGIN transaction block such as the following example.

BEGIN;
execute test3();
ROLLBACK;

When you call the test3 procedure within a transaction block, the procedure executes statement1 and commits it, then executes statement2 and statement3, but those statements are not yet committed. The procedure returns control to the transaction block, which then calls a ROLLBACK command. In this case, the changes made by statement2 and statement3 are rolled back, but statement1 was already committed by the COMMIT statement inside the stored procedure body. If the outside transaction block called a COMMIT instead of a ROLLBACK, statement2 and statement3 would both be committed by transaction block COMMIT command.

Remember that if a BEGIN statement starts a transaction block before calling a stored procedure, the stored procedure always returns with an active transaction block, and you must issue a COMMIT or ROLLBACK statement to complete or abort that transaction. Even in the case where an exception causes the stored procedure to abort, you must still use COMMIT or ROLLBACK after the procedure exits.