17)Use SAVEPOINT commands

 

SQL> select *
2 from department;
DNAME DNO MGRSSN MGRSTARTD
-------------------- ---------- ---------- ---------
Transport 2 1235 13-NOV-93
Packing 4 1236 17-DEC-95
Marketing 3 1237 14-FEB-96
Product 5 1238 30-JAN-97
Accounts 1 1234 30-OCT-98
sales 6 1239 04-MAR-92
6 rows selected.
SQL> savepoint a;
Savepoint created.
SQL> insert into department
2 values('bank','7','1240','05-may-1998');
1 row created.
SQL> select *
2 from department;
DNAME DNO MGRSSN MGRSTARTD
-------------------- ---------- ---------- ---------
Transport 2 1235 13-NOV-93
Packing 4 1236 17-DEC-95
Marketing 3 1237 14-FEB-96
Product 5 1238 30-JAN-97
Accounts 1 1234 30-OCT-98

sales 6 1239 04-MAR-92
bank 7 1240 05-MAY-98
7 rows selected.
SQL> savepoint b;
Savepoint created.
SQL> delete from department
2 where dname='bank';
1 row deleted.
SQL> select *
2 from department;
DNAME DNO MGRSSN MGRSTARTD
-------------------- ---------- ---------- ---------
Transport 2 1235 13-NOV-93
Packing 4 1236 17-DEC-95
Marketing 3 1237 14-FEB-96
Product 5 1238 30-JAN-97
Accounts 1 1234 30-OCT-98
sales 6 1239 04-MAR-92
6 rows selected.
SQL> roll back b;
Rollback complete.
SQL> select *
2 from department;
DNAME DNO MGRSSN MGRSTARTD

-------------------- ---------- ---------- ---------
Transport 2 1235 13-NOV-93
Packing 4 1236 17-DEC-95
Marketing 3 1237 14-FEB-96
Product 5 1238 30-JAN-97
Accounts 1 1234 30-OCT-98
sales 6 1239 04-MAR-92
6 rows selected.
SQL> roll back a;
Rollback complete.

SQL> select *
2 from department;
DNAME DNO MGRSSN MGRSTARTD
-------------------- ---------- ---------- ---------
Transport 2 1235 13-NOV-93
Packing 4 1236 17-DEC-95
Marketing 3 1237 14-FEB-96
Product 5 1238 30-JAN-97
Accounts 1 1234 30-OCT-98
sales 6 1239 04-MAR-92
6 rows selected.