Oracle SQL Commands: Part 8

James Tomar
11y
4.8k
0
2

Article

TCC Commands

1. COMMIT Command

The COMMIT Statement is used when we made some changes in the current transaction and want them to be permanent. In other words, to apply our changes permanently we use the COMMIT clause. Until the changes are committed we cannot access the modified data.

Syntax

COMMIT [WORK] [COMMENT 'your comment']

Or

INSERT INTO table_name VALUES (Val1,Val2,.....Valn);
COMMIT;

Example

Assume the following Employee table:

Emp_id	Emp_Name	City
110011	ASD	Delhi
110022	GHJ	Jodhpur
110033	RST	Patiala
110055	ABC	Haridwar

INSERT INTO Employees VALUES (11044, 'Rahul','Jaipur')
COMMIT;

Result

Emp_id	Emp_Name	City
110011	ASD	Delhi
110022	GHJ	Jodhpur
110033	RST	Patiala
110044	Rahul	Jaipur
110055	ABC	Haridwar

2. ROLLBACK Command

In Oracle, in a current transaction, if we made the changes and also commited them, then ROLLBACK is the only command that is used to undo the changes done by us. To undo/rollback your transaction, no privileges are necessary.

Syntax

ROLLBACK [WORK] TO <SAVEPOINT>] identifier;

Or

INSERT INTO table_name VALUES (Val1,Val2,.....Valn);
ROLLBACK;

Example

Assume the following Organization table:

Org_id	Org_Name	City
123	TCS	Delhi
124	HCL	Noida
126	INFOSYS	Mumbai
128	WIPRO	Gurgoan

Query

INSERT INTO Organization VALUES (125, 'TECH. MAHINDRA','Noida');

INSERT INTO Organization VALUES (127, 'COMPRO PVT.LTD.','Delhi');

INSERT INTO Organization VALUES (129, 'NEIT','Mumbai');

COMMIT;

Result

Org_id	Org_Name	City
123	TCS	Delhi
124	HCL	Noida
126	INFOSYS	Mumbai
128	WIPRO	Gurgoan
125	TECH. MAHINDRA	Noida
127	COMPRO PVT. LTD.	Delhi
129	NEIT	Mumbai

Note: Here we inserted 3 rows but we need to insert only 2 rows so now use rollback and the result is:

Query

INSERT INTO Organization VALUES (125, 'TECH. MAHINDRA','Noida');

INSERT INTO Organization VALUES (127, 'COMPRO PVT.LTD.','Delhi');

INSERT INTO Organization VALUES (129, 'NEIT','Mumbai');

COMMIT;

ROLLBACK;

Result

Org_id	Org_Name	City
123	TCS	Delhi
124	HCL	Noida
126	INFOSYS	Mumbai
128	WIPRO	Gurgoan

3. SAVEPOINT Command

SAVEPOINT is used when we don't want to ROLLBACK all the changes during the current transaction, in other words we created savepoints during the changes made so that we can go to the specific SAVEPOINT to ROLLBACK the change.

Syntax

SAVEPOINT <Savepoint_Name>;

Example

Assume the following Order table:

Order_id	Item_Name	Quantity
1	Shampoo	12
2	Hair Oil	8
5	Talc	5
9	Deo Spray	7

Query

INSERT INTO Order VALUES (7, Soap', 10);

SAVEPOINT a;

DELETE FROM Order VALUES (5, 'Talc',5);

SAVEPOINT b;

INSERT INTO Order VALUES (3, 'NEIT', 15);

COMMIT;

Result

Order_id	Item_Name	Quantity
1	Shampoo	12
2	Hair Oil	8
9	Deo Spray	7
7	Soap	10
3	Detergent	15

Note: Here, in the preceding query we inserted 2 rows and deleted 1 row from the table order and also created SAVEPOINTS, but we inserted two rows instead of one. So, to rollback the insertion of the second row we rollback the transaction using the savepoint with rollback in the following way:

Query

INSERT INTO Order VALUES (7, Soap', 10);

SAVEPOINT a;

DELETE FROM Order VALUES (5, 'Talc',5);

SAVEPOINT b;

INSERT INTO Order VALUES (3, 'NEIT', 15);

ROLLBACK TO SAVEPOINT b;

COMMIT;

Result

Order_id	Item_Name	Quantity
1	Shampoo	12
2	Hair Oil	8
9	Deo Spray	7
7	Soap	10

4. SET TRANSACTION Command

As the name indicates, a SET TRANSACTION is a set of one or more transactions/statements that are executed together as a unit so that either all will be executed or none of them will be executed. It is used to establish the current transaction as read only or read write.

Syntax

SET TRANSACTION

{

{ READ { ONLY | WRITE }

| ISOLATION LEVEL

{ SERIALIZABLE | READ COMMITTED }

| USE ROLLBACK SEGMENT rollback_segment

}

[ NAME 'text' ]

| NAME 'text'

};

Since I have described the transaction levels in my article "Transactions in Oracle database", here I am sharing the Read only level in the following example.

Example

To set a transaction to read only you need to use the SET TRANSACTION READ ONLY Command. In DML the transactions will start automatically, thats why a SET TRANSACTION statement is issued before the DML data. Here the default scott schema is used:

SQL> connect HR/fyicenter

SQL> SET TRANSACTION READ ONLY;
Transaction set.

SQL> SELECT * FROM fyi_links;

     ID URL              NOTES          COUNTS CREATED
------- ---------------- ---------- ---------- ---------
    101 FYICENTER.COM                          07-MAY-06
    110 CENTERFYI.COM                          07-MAY-06
    112 oracle.com                                  07-MAY-06
    113 sql.com                                       07-MAY-06

Keep the "HR" SQL*Plus window as-is and open another window to run another instance of SQL*Plus.

>cd (OracleXE home directory)
>.\bin\sqlplus /nolog

SQL> connect SYSTEM/password
Connected.

SQL> DELETE FROM hr.fyi_links where id = 112;
1 row deleted.

SQL> DELETE FROM hr.fyi_links where id = 113;
1 row deleted.

SQL> COMMIT;

Commit complete.

Go back to the "HR" SQL*Plus window.

SQL> SELECT * FROM fyi_links;

     ID URL              NOTES          COUNTS CREATED
------- ---------------- ---------- ---------- ---------
    101 FYICENTER.COM                          07-MAY-06
    110 CENTERFYI.COM                          07-MAY-06
    112 oracle.com                                  07-MAY-06
    113 sql.com                                       07-MAY-06

SQL> COMMIT;
Commit complete.

SQL> SELECT * FROM fyi_links;

     ID URL              NOTES          COUNTS CREATED
------- ---------------- ---------- ---------- ---------
    101 FYICENTER.COM                          07-MAY-06
    110 CENTERFYI.COM                          07-MAY-06

As you can see, two records were deleted from another session after the HR session started the READ ONLY transaction. The deleted records was not impacting any query statements until the transaction was ended with the COMMIT statement.