Transaction control statements manage changes made by DML statements i.e apply the changes permanently or restore to original. Below are Transaction Control Statements.
COMMIT : Use commit statement at end of your transactions to apply the changes permanently.
SQL> SELECT * FROM STUDENT; SI_NO FULL_NAME BRAN S PERCENTAGE G ---------- ------------------------------ ---- - ---------- - 1111 AAA IT A 97 A 1112 AAB CS B 86 A 1113 AAC ECE A 78 A 1114 AAD EE A 89 A 1115 AAE ME A 74 B SQL> SQL> UPDATE STUDENT SET GRADE='C' WHERE PERCENTAGE BETWEEN 75 AND 80; 1 row updated. SQL> UPDATE STUDENT SET GRADE='B' WHERE PERCENTAGE BETWEEN 80 AND 85; 0 rows updated. SQL> UPDATE STUDENT SET GRADE='C' WHERE PERCENTAGE<75; 1 row updated. SQL> COMMIT; Commit complete. SQL>
ROLLBACK : Use rollback statement to restore all changes since last commit.
SQL> SELECT * FROM STUDENT; SI_NO FULL_NAME BRAN S PERCENTAGE G ---------- ------------------------------ ---- - ---------- - 1111 AAA IT A 97 A 1112 AAB CS B 86 A 1113 AAC ECE A 78 C 1114 AAD EE A 89 A 1115 AAE ME A 74 C SQL> SQL> INSERT INTO STUDENT VALUES(&SI_NO,'&FULL_NAME','&BRANCH','&SECTION',&PERCENTAGE,'&GRADE'); Enter value for si_no: 1116 Enter value for full_name: BBB Enter value for branch: CV Enter value for section: A Enter value for percentage: 76 Enter value for grade: B old 1: INSERT INTO STUDENT VALUES(&SI_NO,'&FULL_NAME','&BRANCH','&SECTION',&PERCENTAGE,'&GRADE') new 1: INSERT INTO STUDENT VALUES(1116,'BBB','CV','A',76,'B') 1 row created. SQL> SQL> SELECT * FROM STUDENT; SI_NO FULL_NAME BRAN S PERCENTAGE G ---------- ------------------------------ ---- - ---------- - 1111 AAA IT A 97 A 1112 AAB CS B 86 A 1113 AAC ECE A 78 C 1114 AAD EE A 89 A 1115 AAE ME A 74 C 1116 BBB CV A 76 B 6 rows selected. SQL> UPDATE STUDENT SET BRANCH='EC' WHERE SI_NO=1113; 1 row updated. SQL> SQL> SQL> SELECT * FROM STUDENT; SI_NO FULL_NAME BRAN S PERCENTAGE G ---------- ------------------------------ ---- - ---------- - 1111 AAA IT A 97 A 1112 AAB CS B 86 A 1113 AAC EC A 78 C 1114 AAD EE A 89 A 1115 AAE ME A 74 C 1116 BBB CV A 76 B 6 rows selected. SQL> ROLLBACK; Rollback complete. SQL> SELECT * FROM STUDENT; SI_NO FULL_NAME BRAN S PERCENTAGE G ---------- ------------------------------ ---- - ---------- - 1111 AAA IT A 97 A 1112 AAB CS B 86 A 1113 AAC ECE A 78 C 1114 AAD EE A 89 A 1115 AAE ME A 74 C SQL>
SAVEPOINT : It is used create a point within a groups of transactions to rollback to particular transaction.
SQL> SELECT * FROM STUDENT; SI_NO FULL_NAME BRAN S PERCENTAGE G ---------- ------------------------------ ---- - ---------- - 1111 AAA IT A 97 A 1112 AAB CS B 86 A 1113 AAC ECE A 78 C 1114 AAD EE A 89 A 1115 AAE ME A 74 C SQL> SQL> DELETE FROM STUDENT WHERE SECTION='B'; 1 row deleted. SQL> SQL> SELECT * FROM STUDENT; SI_NO FULL_NAME BRAN S PERCENTAGE G ---------- ------------------------------ ---- - ---------- - 1111 AAA IT A 97 A 1113 AAC ECE A 78 C 1114 AAD EE A 89 A 1115 AAE ME A 74 C SQL> SQL> SQL> SAVEPOINT delete_section_B; Savepoint created. SQL> INSERT INTO STUDENT VALUES(&SI_NO,'&FULL_NAME','&BRANCH','&SECTION',&PERCENTAGE,'&GRADE'); Enter value for si_no: 1116 Enter value for full_name: BBB Enter value for branch: CS Enter value for section: A Enter value for percentage: 87 Enter value for grade: B old 1: INSERT INTO STUDENT VALUES(&SI_NO,'&FULL_NAME','&BRANCH','&SECTION',&PERCENTAGE,'&GRADE') new 1: INSERT INTO STUDENT VALUES(1116,'BBB','CS','A',87,'B') 1 row created. SQL> SQL> SELECT * FROM STUDENT; SI_NO FULL_NAME BRAN S PERCENTAGE G ---------- ------------------------------ ---- - ---------- - 1111 AAA IT A 97 A 1113 AAC ECE A 78 C 1114 AAD EE A 89 A 1115 AAE ME A 74 C 1116 BBB CS A 87 B SQL> SQL> SAVEPOINT insert_BBB; Savepoint created. SQL> SQL> UPDATE STUDENT SET GRADE='A'; 5 rows updated. SQL> SQL> SELECT * FROM STUDENT; SI_NO FULL_NAME BRAN S PERCENTAGE G ---------- ------------------------------ ---- - ---------- - 1111 AAA IT A 97 A 1113 AAC ECE A 78 A 1114 AAD EE A 89 A 1115 AAE ME A 74 A 1116 BBB CS A 87 A SQL> SQL> SQL> ROLLBACK TO insert_BBB; Rollback complete. SQL> SELECT * FROM STUDENT; SI_NO FULL_NAME BRAN S PERCENTAGE G ---------- ------------------------------ ---- - ---------- - 1111 AAA IT A 97 A 1113 AAC ECE A 78 C 1114 AAD EE A 89 A 1115 AAE ME A 74 C 1116 BBB CS A 87 B SQL> ROLLBACK TO delete_section_B; Rollback complete. SQL> SELECT * FROM STUDENT; SI_NO FULL_NAME BRAN S PERCENTAGE G ---------- ------------------------------ ---- - ---------- - 1111 AAA IT A 97 A 1113 AAC ECE A 78 C 1114 AAD EE A 89 A 1115 AAE ME A 74 C SQL> SQL> ROLLBACK; Rollback complete. SQL> SELECT * FROM STUDENT; SI_NO FULL_NAME BRAN S PERCENTAGE G ---------- ------------------------------ ---- - ---------- - 1111 AAA IT A 97 A 1112 AAB CS B 86 A 1113 AAC ECE A 78 C 1114 AAD EE A 89 A 1115 AAE ME A 74 C SQL>
SET TRANSACTION : It is used to establish the current transaction as read-only or read/write.
Syntax : SET TRANSACTION [ READ WRITE | READ ONLY ];
[oracle@learndba ~]$ sqlplus scott/tiger SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 12 22:56:06 2016 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SET TRANSACTION READ ONLY; Transaction set. SQL> SQL> SQL> INSERT INTO STUDENT VALUES(&SI_NO,'&FULL_NAME','&BRANCH','&SECTION',&PERCENTAGE,'&GRADE'); Enter value for si_no: 1116 Enter value for full_name: aa AAF Enter value for branch: EE Enter value for section: B Enter value for percentage: 67 Enter value for grade: C old 1: INSERT INTO STUDENT VALUES(&SI_NO,'&FULL_NAME','&BRANCH','&SECTION',&PERCENTAGE,'&GRADE') new 1: INSERT INTO STUDENT VALUES(1116,'AAF','EE','B',67,'C') INSERT INTO STUDENT VALUES(1116,'AAF','EE','B',67,'C') * ERROR at line 1: ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction SQL>EXIT Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@learndba ~]$ [oracle@learndba ~]$ [oracle@learndba ~]$ sqlplus scott/tiger SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 12 22:58:47 2016 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SET TRANSACTION READ WRITE; Transaction set. SQL> INSERT INTO STUDENT VALUES(&SI_NO,'&FULL_NAME','&BRANCH','&SECTION',&PERCENTAGE,'&GRADE'); Enter value for si_no: 1116 Enter value for full_name: BBB Enter value for branch: Enter value for section: A Enter value for percentage: 89 Enter value for grade: A old 1: INSERT INTO STUDENT VALUES(&SI_NO,'&FULL_NAME','&BRANCH','&SECTION',&PERCENTAGE,'&GRADE') new 1: INSERT INTO STUDENT VALUES(1116,'BBB','','A',89,'A') 1 row created. SQL>
We can’t change the transaction read write mode in current transaction. We need to exit and set transaction characteristics.
SQL> SET TRANSACTION READ ONLY; SET TRANSACTION READ ONLY * ERROR at line 1: ORA-01453: SET TRANSACTION must be first statement of transaction SQL>
