In this blog, we’ll discuss the transactions in PL/SQL. A database transaction is an atomic unit of labour which will contains one or more related SQL statements. it’s called atomic because the database modifications caused by the SQL statements that constitute a transaction can collectively be either committed, i.e., made permanent to the database or rolled back (undone) from the database.
A successfully executed SQL statement and a committed transaction aren’t same. albeit an SQL statement is executed successfully, unless the transaction containing the statement is committed, it is often rolled back and every one changes made by the statement(s) are often undone.
Starting and Ending a Transaction
A transaction features a beginning and an end. A transaction starts when one among the subsequent events happen −
• The first SQL statement is performed after connecting to the database.
• At each new SQL statement issued after a transaction is completed.
A transaction ends when one among the subsequent events happen −
• A COMMIT or a ROLLBACK statement is issued.
• A DDL statement, like CREATE TABLE statement, is issued; because therein case a COMMIT is automatically performed.
• A DCL statement, like a GRANT statement, is issued; because therein case a COMMIT is automatically performed.
• User disconnects from the database.
• User exits from SQL*PLUS by issuing the EXIT command, a COMMIT is automatically performed.
• SQL*Plus terminates abnormally, a ROLLBACK is automatically performed.
• A DML statement fails; therein case a ROLLBACK is automatically performed for undoing that DML statement.
Committing a Transaction
A transaction is formed permanent by issuing the SQL command COMMIT. the overall syntax for the COMMIT command is −
COMMIT;
For example,

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 database without COMMIT might be undone using the ROLLBACK command.
The general syntax for the ROLLBACK command is −
ROLLBACK [TO SAVEPOINT < savepoint_name>];
When a transaction is aborted thanks to some unprecedented situation, like system failure, the whole transaction since a commit is automatically rolled back. If you’re not using savepoint, then simply use the subsequent statement to rollback all the changes −
ROLLBACK;
Savepoints
Savepoints are kind of markers that help in splitting an extended transaction into smaller units by setting some checkpoints. By setting savepoints within an extended transaction, you’ll roll back to a checkpoint if required. this is often done by issuing the SAVEPOINT command.
The general syntax for the SAVEPOINT command is −
SAVEPOINT < savepoint_name >;
For example

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 statement rolls back all the changes up to the purpose , where you had marked savepoint sav1.
After that, the new changes that you simply make will start.
Automatic Transaction Control
To execute a COMMIT automatically whenever an INSERT, UPDATE or DELETE command is executed, you’ll set the AUTOCOMMIT environment variable as −
SET AUTOCOMMIT ON;
You can turn-off the auto commit mode using the subsequent command −
SET AUTOCOMMIT OFF;
So, this brings us to the end of blog. This Tecklearn ‘Transactions in PL-SQL’ blog helps you with commonly asked questions if you are looking out for a job in Oracle Pl-SQL. If you wish to learn Oracle PL-SQL and build a career in Database domain, then check out our interactive, Oracle PL-SQL Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

Oracle PL SQL Training

Oracle PL-SQL Training

About the Course

Oracle PL/SQL online training course provides you the complete skills needed to create, implement and manage robust database applications using the Oracle Database tools. Our expert instructors will help you to master PL SQL advanced features, from performance to maintainability to the application code architecture. Our best online classes will help you to gain a precise knowledge of PL SQL language, architecture, interactions with the SQL engine, data types, and much more. The entire training is in line with the Oracle PL/SQL certification.

Why Should you take Oracle PL-SQL Training?

• The Average salary of a Senior Oracle PL-SQL Developer is $131,878 per annum – ZipRecuiter.com
• PL-SQL has a market share of 23% globally.
• IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle SQL
• Database Models
• RDBMS
• Components of SQL
• DataTypes
• DDL-Create, Alter, Rename, Drop, Truncate
Manipulating Data using SQL
• Constraints –Unique, Not Null, Primary Key, Check Constraint, Foreign Key
• DML Commands-Insert, Update, Delete
• Order by Clause
• Group Functions
• SET Operators- Union All, Union, Intersect, Minus
• TCL Commands-Commit, RollBack, Savepoint
Oracle Views and Synonyms
• Types of Views
• Synonyms
• Types of Synonyms
• Indexes
• Types of Indexes
Using Subqueries to Solve Queries
• Subqueries
• Co-Related Subquery
OLAP Functions
• OLAP Features
• Roll Up
• Model Clause
• Dimension Modelling
Conditional Statement
• Block
• Variable Attributes
• Nested Blocks
• Conditional Control Statements
• Iterative Controls (Loop)
Cursor Management
• Types of Cursor
• Writing Explicit cursors
• Explicit cursor functions
• Advance Explicit cursor
• Cursor with parameters
Exception Handling
• Handling Exception
• Handling Exception with PL/SQL Predefined Exceptions,
• User Defined Exceptions
• Non-Predefined Error
• Function for trapping Exception
• Trapping user-defined Exception
Subprogram, Procedure and passing parameters and Advance Package Concepts and functions
• Important Features of Sub-Programs
• Procedure
• Functions
Trigger Management
• Introduction to Triggers
• Types of Triggers
• Compound Triggers
Oracle Job Scheduling
Large Object Functions
• Large object functions – BFILENAME, EMPTY_BLOB, EMPTY_CLOB etc
Important Features of Oracle
Advance level- Scripting

454