Reading:  

To the point guide to PL/SQL


Working with Transactions

In PL/SQL transaction is a logical unit of work which consist of one or number of related statements of SQL for data manipulation. Database changes at the end of the transaction, the changes permanent may not be undone. In the middle of transaction if program fails PL/SQL find the error and rollback the transaction and restore the database.

SQL statement executed successfully and committed transaction is different. After SQL statement is executed successfully, unless the transaction having the committed statement it can be rolled back and changes made by the statements can be undone.

Starting an Ending a Transaction

A transaction always has beginning and end statement. To start a transaction involves executing/running the SQL query after database connection.

Ok! So how do we end a transaction?? Answer is that if one of the below scenarios is true

  • You used a A DDL statement such as CREATE TABLE, Why? Beucase CREATE statement issues an internal COMMIT, this means that COMMIT is automatically performed.
  • By using COMMIT or a ROLLBACK statement.
  • A DCL statement GRANT  is auto-COMMIT too where COMMIT is automatically performed.
  • When User exits by use EXIT command is auto-COMMIT where COMMIT is automatically performed.
  • When user disconnects from the database.
  • A DML statement fails; in that case a ROLLBACK is automatically performed for undoing that DML statement.
  • When SQL*Plus ends abnormally, automatically ROLLBACK is performed.

Committing a Transaction

A transaction is made permanent by issuing the SQL command COMMIT.

The general syntax for the COMMIT command is:

COMMIT;

Example

create table Stud(
  First_Name VARCHAR2(20),
  Subject VARCHAR2(20),
  Marks Number(3),
 )
 /
INSERT INTO Stud values('David', 'Maths',87);
INSERT INTO Stud values('Nag', 'Science',90);
INSERT INTO Stud values('Nima', 'Social',87);
COMMIT;

 

Rolling Back Transactions:

ROLLBACK statement is used to undo the action/work done by the transaction prior to commit the transaction.

Syntax:

ROLLBACK [ WORK ] [ TO [SAVEPOINT] name _of_savepoint  | FORCE 'string' ];

Parameters: 

WORK: it is optional which is added by Oracle to be SQL-compliant.

TO SAVEPOINT is optional. The statement ROLLBACK undoes entire modification for the current transaction up to the savepoint specified by savepoint_name.

FORCE 'string' is optional which is used to force the transaction for rollback that may be in doubt.

Example:

ROLLBACK;

Savepoints:

Let's look at an example of a ROLLBACK that shows how to use the rollback to a specific savepoint.

For example:

INSERT INTO Stud values('kamal', 'Maths',87);
INSERT INTO Stud values('sony', 'Science',90);
INSERT INTO Stud values('jas', 'Social',87);
SAVEPOINT savepointExm1;
INSERT INTO Stud values('nimrit', 'Arts',89);
ROLLBACK TO savepointExm1;

ROLLBACK TO savepointExm1;

this statement rolls back the modification till the point marked as savepoint savepointExm1

Automatic Transaction Control

User can set the AUTOCOMMT environment variable, to execute a commit automatically whenever an INSERT, DELETE or UPDATE command is executed.

SET AUTOCOMMIT ON;

To turn-off auto commit mode:

SET AUTOCOMMIT OFF;

 In next part of this quick introduction to PL/SQL we will explore Date and Time functions

Description

This tutorial focus on PL/SQL and covers the below topics

  • What is PL/SQL?
  • Environment Setup
  • Variables
  • Data Types 
  • Constants 
  • Operators
  • Conditions
  • Loops
  • Strings
  • Arrays
  • Procedures
  • Functions
  • Cursors
  • Records
  • Exceptions
  • Packages
  • Triggers
  • Collections
  • Transactions
  • Date & Time
  • Object Oriented
  • DBMS Output

If you found any error with any of the docs please let us know.

 



Learning Objectives

Learn PL/SQL from a beginners perspective, this guide can also help you if you are trying to brush up your PLSQL skills

Author: Subject Coach
Added on: 20th Apr 2015

You must be logged in as Student to ask a Question.

None just yet!