The ROLLBACK statement ends the current transaction and undoes any changes made during that transaction.
The following code inserts information about an employee into three different tables.
If an INSERT statement tries to store a duplicate employee number, PL/SQL raises the predefined exception DUP_VAL_ON_INDEX.
To ensure that changes to all three tables are undone, the exception handler runs a ROLLBACK.
SQL> SQL> drop table emp; Table dropped.-- from w ww .ja v a 2 s. c om SQL> CREATE TABLE emp( 2 empid NUMBER(6), 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(25), 5 email VARCHAR2(25), 6 phone_number VARCHAR2(20), 7 hire_date DATE, 8 job_id VARCHAR2(10), 9 salary NUMBER(8,2), 10 commission_pct NUMBER(2,2), 11 manager_id NUMBER(6), 12 department_id NUMBER(4)) ; SQL> SQL> INSERT INTO emp VALUES( 100, 'Steven', 'King', 'SKING', '123.123.4567', TO_DATE('17-JUN-1987', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 10); SQL> INSERT INTO emp VALUES( 200, 'Joe', 'Lee', 'abc', '123.123.9999', TO_DATE('17-JUN-1980', 'dd-MON-yyyy'), 'TESTER', 25000, NULL, NULL, 20); SQL> SQL> DROP TABLE emp_name; Table dropped. SQL> CREATE TABLE emp_name AS 2 SELECT empid, last_name 3 FROM emp; SQL> SQL> CREATE UNIQUE INDEX empname_ix 2 ON emp_name (empid); Index created. SQL> SQL> DROP TABLE emp_sal; Table dropped. SQL> CREATE TABLE emp_sal AS 2 SELECT empid, salary 3 FROM emp; SQL> SQL> CREATE UNIQUE INDEX empsal_ix 2 ON emp_sal (empid); Index created. SQL> SQL> DROP TABLE emp_job; Table dropped. SQL> CREATE TABLE emp_job AS 2 SELECT empid, job_id 3 FROM emp; SQL> SQL> CREATE UNIQUE INDEX empjobid_ix 2 ON emp_job (empid); Index created. SQL> SQL> SQL> DECLARE 2 emp_id NUMBER(6); 3 emp_lastname VARCHAR2(25); 4 emp_salary NUMBER(8,2); 5 emp_jobid VARCHAR2(10); 6 BEGIN 7 SELECT empid, last_name, salary, job_id 8 INTO emp_id, emp_lastname, emp_salary, emp_jobid 9 FROM emp 10 WHERE empid = 120; 11 12 INSERT INTO emp_name (empid, last_name) 13 VALUES (emp_id, emp_lastname); 14 15 INSERT INTO emp_sal (empid, salary) 16 VALUES (emp_id, emp_salary); 17 18 INSERT INTO emp_job (empid, job_id) 19 VALUES (emp_id, emp_jobid); 20 21 EXCEPTION 22 WHEN DUP_VAL_ON_INDEX THEN 23 ROLLBACK; 24 DBMS_OUTPUT.PUT_LINE('Inserts were rolled back'); 25 END; 26 / SQL>