Transaction Control Statements

      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>
Advertisements