In this section, we will talk about the exchanges in PL/SQL. A data set exchange is a nuclear unit of work that may comprise of at least one related SQL articulations. It is called nuclear on the grounds that the data set adjustments achieved by the SQL explanations that comprise an exchange can all things considered be either dedicated, i.e., made lasting to the information base or moved back (fixed) from the data set.
An effectively executed SQL articulation and a submitted exchange are not same. Regardless of whether a SQL proclamation is executed effectively, except if the exchange containing the assertion is submitted, it very well may be moved back and all progressions made by the statement(s) can be fixed.
Starting and Ending a Transaction
An exchange has a start and an end. An exchange begins when one of the accompanying occasions happen −
- The principal SQL articulation is performed in the wake of associating with the data set.
- At each new SQL articulation gave after an exchange is finished.
An exchange closes when one of the accompanying occasions happen −
- A COMMIT or a ROLLBACK articulation is given.
- A DDL proclamation, for example, CREATE TABLE explanation, is given; in light of the fact that all things considered a COMMIT is naturally performed.
- A DCL proclamation, for example, a GRANT articulation, is given; in light of the fact that all things considered a COMMIT is naturally performed.
- Client separates from the data set.
- Client exits from SQL*PLUS by giving the EXIT order, a COMMIT is consequently performed.
- SQL*Plus ends unusually, a ROLLBACK is consequently performed.
- A DML explanation comes up short; all things considered a ROLLBACK is naturally performed for fixing that DML articulation.
Committing a Transaction
An exchange is made perpetual by giving the SQL order COMMIT. The overall grammar for the COMMIT order is −
COMMIT;
For instance,
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );
COMMIT;
Rolling Back Transactions
Changes made to the information base without COMMIT could be fixed utilizing the ROLLBACK order.
The overall punctuation for the ROLLBACK order is −
ROLLBACK [TO SAVEPOINT < savepoint_name>];
At the point when an exchange is cut short because of some exceptional circumstance, similar to framework disappointment, the whole exchange since a submit is consequently moved back. In the event that you are not utilizing savepoint, at that point basically utilize the accompanying assertion to rollback all the progressions −
ROLLBACK;
Savepoints
Savepoints are kind of markers that help in parting a long exchange into more modest units by setting a few designated spots. By setting savepoints inside a long exchange, you can move back to a designated spot whenever required. This is finished by giving the SAVEPOINT order.
The overall linguistic structure for the SAVEPOINT order is −
SAVEPOINT < savepoint_name >;
For instance
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Rajnish', 27, 'HP', 9500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (8, 'Riddhi', 21, 'WB', 4500.00 );
SAVEPOINT sav1;
UPDATE CUSTOMERS
SET SALARY = SALARY + 1000;
ROLLBACK TO sav1;
UPDATE CUSTOMERS
SET SALARY = SALARY + 1000
WHERE ID = 7;
UPDATE CUSTOMERS
SET SALARY = SALARY + 1000
WHERE ID = 8;
COMMIT;
ROLLBACK TO sav1 − This assertion moves back all the progressions up direct, where you had checked savepoint sav1.
From that point onward, the new changes that you make will begin.
Automatic Transaction Control
To execute a COMMIT naturally at whatever point an INSERT, UPDATE or DELETE order is executed, you can set the AUTOCOMMIT climate variable as −
SET AUTOCOMMIT ON;
You can kill the auto submit mode utilizing the accompanying order −
SET AUTOCOMMIT OFF;