The COMMIT statement makes all database updates made during the current transaction permanent, and ends the current transaction.
COMMIT [ WORK ];
The COMMIT statement may be used within anonymous blocks, stored procedures, or functions. Within an SPL program, it may appear in the executable section and the exception section.
In the following example, the third INSERT statement in the anonymous block results in an error. The effect of the first two INSERT statements is retained as shown by the first SELECT statement. Even after a ROLLBACK statement is issued, the two rows remain in the table as shown by the second SELECT statement. This verifies that the two rows were indeed committed.
\set AUTOCOMMIT off
SET polar_comp_stmt_level_tx TO on;
BEGIN
INSERT INTO dept VALUES (50, 'FINANCE', 'DALLAS');
INSERT INTO dept VALUES (60, 'MARKETING', 'CHICAGO');
COMMIT;
INSERT INTO dept VALUES (70, 'HUMAN RESOURCES', 'CHICAGO');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END;
SQLERRM: value too long for type character varying(14)
SQLCODE: 22001
SELECT * FROM dept;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
50 | FINANCE | DALLAS
60 | MARKETING | CHICAGO
(6 rows)
ROLLBACK;
SELECT * FROM dept;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
50 | FINANCE | DALLAS
60 | MARKETING | CHICAGO
(6 rows)