The ROLLBACK statement undoes all database updates made during the current transaction, and ends the current transaction.
ROLLBACK [ WORK ];
The ROLLBACK 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 exception section contains a ROLLBACK statement. Even though the first two INSERT statements are executed successfully, the third one results in an exception that causes the rollback of all INSERT statements in the anonymous block.
\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');
INSERT INTO dept VALUES (70, 'HUMAN RESOURCES', 'CHICAGO');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
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
(4 rows)
The following example uses both COMMIT and ROLLBACK. First, the following stored procedure which inserts a new employee is created.
\set AUTOCOMMIT off
SET polar_comp_stmt_level_tx TO on;
CREATE OR REPLACE PROCEDURE emp_insert (
p_empno IN emp.empno%TYPE,
p_ename IN emp.ename%TYPE,
p_job IN emp.job%TYPE,
p_mgr IN emp.mgr%TYPE,
p_hiredate IN emp.hiredate%TYPE,
p_sal IN emp.sal%TYPE,
p_comm IN emp.comm%TYPE,
p_deptno IN emp.deptno%TYPE
)
IS
BEGIN
INSERT INTO emp VALUES (
p_empno,
p_ename,
p_job,
p_mgr,
p_hiredate,
p_sal,
p_comm,
p_deptno);
DBMS_OUTPUT.PUT_LINE('Added employee...') ;
DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
DBMS_OUTPUT.PUT_LINE('Name : ' || p_ename);
DBMS_OUTPUT.PUT_LINE('Job : ' || p_job);
DBMS_OUTPUT.PUT_LINE('Manager : ' || p_mgr);
DBMS_OUTPUT.PUT_LINE('Hire Date : ' || p_hiredate);
DBMS_OUTPUT.PUT_LINE('Salary : ' || p_sal);
DBMS_OUTPUT.PUT_LINE('Commission : ' || p_comm);
DBMS_OUTPUT.PUT_LINE('Dept # : ' || p_deptno);
DBMS_OUTPUT.PUT_LINE('----------------------');
END;
Note that this procedure has no exception section so any error that may occur is propagated up to the calling program.
The following anonymous block is run. Note the use of the COMMIT statement after all calls to the emp_insert procedure and the ROLLBACK statement in the exception section.
BEGIN
emp_insert(9601,'FARRELL','ANALYST',7902,'03-MAR-08',5000,NULL,40);
emp_insert(9602,'TYLER','ANALYST',7900,'25-JAN-08',4800,NULL,40);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('An error occurred - roll back inserts');
ROLLBACK;
END;
Added employee...
Employee # : 9601
Name : FARRELL
Job : ANALYST
Manager : 7902
Hire Date : 03-MAR-08 00:00:00
Salary : 5000
Commission :
Dept # : 40
----------------------
Added employee...
Employee # : 9602
Name : TYLER
Job : ANALYST
Manager : 7900
Hire Date : 25-JAN-08 00:00:00
Salary : 4800
Commission :
Dept # : 40
----------------------
The following SELECT statement shows that employees Farrell and Tyler were added.
SELECT * FROM emp WHERE empno > 9600;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+---------+---------+------+--------------------+---------+------+--------
9601 | FARRELL | ANALYST | 7902 | 03-MAR-08 00:00:00 | 5000.00 | | 40
9602 | TYLER | ANALYST | 7900 | 25-JAN-08 00:00:00 | 4800.00 | | 40
(2 rows)
Execute the following anonymous block:
BEGIN
emp_insert(9603,'HARRISON','SALESMAN',7902,'13-DEC-07',5000,3000,20);
emp_insert(9604,'JARVIS','SALESMAN',7902,'05-MAY-08',4800,4100,11);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('An error occurred - roll back inserts');
ROLLBACK;
END;
Added employee...
Employee # : 9603
Name : HARRISON
Job : SALESMAN
Manager : 7902
Hire Date : 13-DEC-07 00:00:00
Salary : 5000
Commission : 3000
Dept # : 20
----------------------
SQLERRM: insert or update on table "emp" violates foreign key constraint "emp_ref_dept_fk"
An error occurred - roll back inserts
A SELECT statement run against the table yields the following output:
SELECT * FROM emp WHERE empno > 9600;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+---------+---------+------+--------------------+---------+------+--------
9601 | FARRELL | ANALYST | 7902 | 03-MAR-08 00:00:00 | 5000.00 | | 40
9602 | TYLER | ANALYST | 7900 | 25-JAN-08 00:00:00 | 4800.00 | | 40
(2 rows)
The ROLLBACK statement in the exception section undoes the insert of employee Harrison. Note that employees Farrell and Tyler are still in the table as their inserts were made permanent by the COMMIT statement in the first anonymous block.