A Transaction ensures that the action of a group of statements is atomic.A transaction is a logical unit of work that contains one or more SQL statements.The effects of all the SQL statements in a transaction can be either all committed or all rolled back.
SQL> create table MyTable (name varchar2(100),address varchar2(500),id number); Table created SQL>
Let us start with a simple transaction example.Here the transaction inserts a row of record to MyTable.
-- Created on 11/4/2008 by Globinch PLSQLTIPS declare -- Local variables here begin -- Test statements here insert into MyTable(name,address,id) values('Abcd','address1',1); commit; end;
The above piece of code represents a single transaction.The row inserted into MyTable became permanent after the COMMIT statement.
Let us query MyTable.
SQL> select * from Mytable; NAME ADDRESS ID Abcd address1 1 SQL>
The following example show two simple transactions
-- Created on 11/4/2008 by Globinch PLSQLTIPS declare -- Local variables here begin -- Test statements here insert into MyTable(name,address,id) values('Abcd2','address2',2); insert into MyTable(name,address,id) values('Abcd4','address2',3); commit; delete from MyTable where id = 2; commit; end;
SQL> select * from MyTable; NAME ADDRESS ID Abcd4 address2 3 SQL>
A transaction ends when any of the following occurs:
1.A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.
2.A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER.
3.A user disconnects from Oracle and the current transaction is committed.
4.A user process terminates abnormally then the current transaction is rolled back.
So What is SAVEPOINT? read it here
Savepoints In SQL Transactions. – SAVEPOINT
- Savepoints In SQL Transactions. – SAVEPOINT
- Oracle Tablespace, and Datafiles – Introduction
- Formatting date in SQL (Oracle) -Simple date forma…
- FINDING database objects, finding valid and INVALI…
- Oracle Data types
SQL, SQL Transactions, transaction management, databse transactions, transactions, DML statements, DDL statements, INSERT, UPDATE, DELETE, create table
Pingback: Autonomous Transactions in PL/SQL.Oracle Autonomous Transactions | SQL and PLSQL