TCL Command in SQL (Commit, Rollback, Savepoint)

TCL Commands in SQL

---========================================

-- Commit --

---========================================

select * from XXSD_DEPT

insert into XXSD_DEPT VALUES( 1, 'OPMS1', 'Phase-1')

insert into XXSD_DEPT VALUES( 2, 'OPMS2', 'Phase-2')

insert into XXSD_DEPT VALUES( 3, 'OPMS3', 'Phase-3')

COMMIT;

delete from XXSD_DEPT where DEPTNO = 2

COMMIT;

---========================================

-- Rollback --

---========================================

insert into XXSD_DEPT VALUES( 2, 'OPMS2', 'Phase-2')

insert into XXSD_DEPT VALUES( 4, 'OPMS4', 'Phase-4')

insert into XXSD_DEPT VALUES( 5, 'OPMS5', 'Phase-5')

select * from XXSD_DEPT

ROLLBACK;

delete from XXSD_DEPT where DEPTNO = 2

Understand SAVEPOINT

select * from XXSD_DEPT

---========================================

-- STEP-1 --

---========================================

select * from XXSD_DEPT

insert into XXSD_DEPT VALUES( 1, 'OPMS1', 'Phase-1')

--@ Create Savepoint here for stage-1 @--

SAVEPOINT STAGE1

---========================================

-- STEP-2 --

---========================================

select * from XXSD_DEPT

insert into XXSD_DEPT VALUES( 2, 'OPMS2', 'Phase-2')

--@ Create Savepoint here for stage-2 @--

SAVEPOINT STAGE2

---========================================

-- STEP-3 --

---========================================

select * from XXSD_DEPT

insert into XXSD_DEPT VALUES( 3, 'OPMS3', 'Phase-3')

--@ Create Savepoint here for stage-3 @--

SAVEPOINT STAGE3

---========================================

-- STEP-4 --

---========================================

select * from XXSD_DEPT

insert into XXSD_DEPT VALUES( 4, 'OPMS4', 'Phase-4')

--@ Create Savepoint here for stage-4 @--

SAVEPOINT STAGE4

---========================================

-- STEP-5 --

---========================================

select * from XXSD_DEPT

insert into XXSD_DEPT VALUES( 5, 'OPMS5', 'Phase-5')

--@ Create Savepoint here for stage-5 @--

SAVEPOINT STAGE5

--====================================================

ROLLBACK TO STAGE3

COMMIT;

Select * from xxsd_dept