Скачать или смотреть PL/SQL Savepoint vs Default Exception Handling — Quick Demo Interview Preparation

CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(50),
salary NUMBER
);



BEGIN
FOR i IN 1..10 LOOP
INSERT INTO employees VALUES (i, 'Employee_' || i, 30000 + (i * 100));
END LOOP;
COMMIT;
END;
/



CREATE TABLE payroll (
emp_id NUMBER PRIMARY KEY,
salary NUMBER,
bonus_flag CHAR(1)
);


BEGIN
FOR i IN 1..10 LOOP
INSERT INTO payroll (emp_id, salary, bonus_flag)
VALUES (i, 30000 + (i * 100), 'N');
END LOOP;
COMMIT;
END;
/


begin
execute immediate 'truncate table bonus';
execute immediate 'truncate table employees';
execute immediate 'truncate table payroll';
end;


CREATE TABLE bonus (
emp_id NUMBER,
bonus_amt NUMBER
);


-- ***** WITHOUT SAVEPOINT **********


BEGIN
FOR rec IN (SELECT * FROM employees) LOOP
-- SAVEPOINT before_insert;
BEGIN

-- FIRST DML

INSERT INTO bonus VALUES (rec.emp_id, rec.salary * 0.1);


-- SECOND DML

UPDATE payroll SET bonus_flag = 'Y' WHERE emp_id = rec.emp_id;


-- Simulate error
-- Exception happen in 3rd Steps

IF rec.emp_id = 9 THEN
RAISE_APPLICATION_ERROR(-20001, 'Failed after first DML');
END IF;


-- 4th DML


EXCEPTION
WHEN OTHERS THEN

ROLLBACK; -- Explicitly we mentioned

-- Please note By default oracle rollback only for the statement where exception encountered


--ROLLBACK TO before_insert; -- Undo both insert+update for this emp_id
DBMS_OUTPUT.PUT_LINE('Error for emp_id: ' || rec.emp_id);

END;
END LOOP;


COMMIT;
END;
/





-- ***** DEFAULT BEHAVIOUS ON EXCEPTION **********


BEGIN
FOR rec IN (SELECT * FROM employees) LOOP

BEGIN

-- FIRST DML

INSERT INTO bonus VALUES (rec.emp_id, rec.salary * 0.1);


-- SECOND DML

UPDATE payroll SET bonus_flag = 'Y' WHERE emp_id = rec.emp_id;


-- Simulate error
-- Exception happen in 3rd Steps

IF rec.emp_id = 9 THEN
RAISE_APPLICATION_ERROR(-20001, 'Failed after first DML');
END IF;


-- 4th DML


EXCEPTION
WHEN OTHERS THEN

ROLLBACK; -- Explicitly we mentioned

-- ROLLBACK to before_insert;

-- Please note By default oracle rollback only for the statement where exception encountered


--ROLLBACK TO before_insert; -- Undo both insert+update for this emp_id
DBMS_OUTPUT.PUT_LINE('Error for emp_id: ' || rec.emp_id);

END;
END LOOP;


COMMIT;
END;
/


-- ***** WITH SAVEPOINT **********


BEGIN
FOR rec IN (SELECT * FROM employees) LOOP
SAVEPOINT before_insert;
BEGIN

-- FIRST DML

INSERT INTO bonus VALUES (rec.emp_id, rec.salary * 0.1);


-- SECOND DML

UPDATE payroll SET bonus_flag = 'Y' WHERE emp_id = rec.emp_id;


-- Simulate error
-- Exception happen in 3rd Steps

IF rec.emp_id = 9 THEN
RAISE_APPLICATION_ERROR(-20001, 'Failed after first DML');
END IF;


-- 4th DML


EXCEPTION
WHEN OTHERS THEN

--ROLLBACK; -- Explicitly we mentioned

ROLLBACK to before_insert;

-- Please note By default oracle rollback only for the statement where exception encountered


--ROLLBACK TO before_insert; -- Undo both insert+update for this emp_id
DBMS_OUTPUT.PUT_LINE('Error for emp_id: ' || rec.emp_id);

END;
END LOOP;


COMMIT;
END;
/