AUTOCOMMIT ON blocks

Each statement within a stored procedure BEGIN AUTOCOMMIT ON block is executed as a singleton statement with an implied commit immediately after the statement. This behavior is similar to running a series of SQL commands on the command line, not inside a BEGIN/COMMIT transaction block.

You can specify all SQL commands inside an AUTOCOMMIT ON block, including EXECUTE IMMEDIATE, IF statements, FOR statements, and so on. There are no restrictions on the SQL statements. For each statement that is used inside an AUTOCOMMIT ON block, the database changes are automatically committed after each statement is executed.

An example of an AUTOCOMMIT block follows.

CREATE PROCEDURE TEST4()
LANGUAGE NZPLSQL
RETURNS BOOLEAN
BEGIN_PROC
BEGIN AUTOCOMMIT ON
    GROOM TABLE MYTBL;
    RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
    RAISE NOTICE ‘ERROR: %’, SQLERRM;
    RETURN FALSE;
END;

Note: Executing transactional statements such as COMMIT or ROLLBACK inside an AUTOCOMMIT ON block has no effect because those commands commit or roll back only their own statement. All previous statements are already committed.

There is an implied COMMIT at the beginning of the AUTOCOMMIT ON block before any statement in the block is executed. Any exception that is raised by a command inside an AUTOCOMMIT ON block calls the exception block, in the same manner as a normal NZPLSQL statement block.

The behavior of statements in an exception block attached to an AUTOCOMMIT ON block is based on the AUTOCOMMIT ON/OFF status of the parent block. If an AUTOCOMMIT ON sub-block occurs within an AUTOCOMMIT ON block, the statements in the exception block are executed as singletons unless you issue a BEGIN within the exception block to change the behavior.

The NZPLSQL language allows statement blocks to be nested within each other. This behavior is supported by AUTOCOMMIT ON blocks as well, so it is possible to nest AUTOCOMMIT ON|OFF blocks within each other. The syntax BEGIN AUTOCOMMIT OFF is the default and is identical to BEGIN without an AUTOCOMMIT clause.

BEGIN
    Statement1;
    BEGIN AUTOCOMMIT ON
         /* an implicit commit occurs here */
        Statement2;
         /* an implicit commit occurs here */
        Statement3;
         /* an implicit commit occurs here */
    END
END;

When an AUTOCOMMIT ON block is nested inside of an AUTOCOMMIT OFF block, an implicit commit occurs before the first statement is executed inside of the AUTOCOMMIT ON block and then an implicit commit occurs after each statement inside of the AUTOCOMMIT ON block.

When an AUTOCOMMIT OFF block is nested inside of an AUTOCOMMIT ON block, the statements within the AUTOCOMMIT OFF block are executed as a multi statement transaction that is immediately committed at the end of the block.

BEGIN AUTOCOMMIT ON
    BEGIN AUTOCOMMIT OFF
        Statement1;
        Statement2;
    END;
     /* an implicit commit occurs here */
END;

Exception blocks for an AUTOCOMMIT ON block are executed if any of the statements within the AUTOCOMMIT ON block fails. Unlike a normal exception block, there is no need to issue a ROLLBACK inside an AUTOCOMMIT ON exception block. The statement that failed is automatically rolled back and a new transaction is immediately started before processing any statements in the exception block.