Using SAVEPOINT With ROLLBACK
DROP TABLE emp_name CASCADE CONSTRAINTS PURGE;
DROP INDEX empname_ix;
CREATE TABLE emp_name
AS
SELECT employee_id,
last_name,
salary
FROM employees;
CREATE UNIQUE INDEX empname_ix ON emp_name (
employee_id);
DECLARE
emp_id employees.employee_id%TYPE;
emp_lastname employees.last_name%TYPE;
emp_salary employees.salary%TYPE;
BEGIN
SELECT employee_id,
last_name,
salary
INTO emp_id,emp_lastname,emp_salary
FROM employees
WHERE employee_id = 120;
UPDATE emp_name
SET salary = salary * 1.1
WHERE employee_id = emp_id;
DELETE FROM emp_name
WHERE employee_id = 130;
SAVEPOINT do_insert;
INSERT INTO emp_name
VALUES (emp_id,
emp_lastname,
emp_salary);
EXCEPTION
WHEN dup_val_on_index THEN
ROLLBACK TO do_insert;
dbms_output.Put_line('Insert has been rolled back');
END;
/
OUTPUT :-
Insert has been rolled back
0 comments:
Post a Comment