Oracle Transactions
(adapted/based on
Ullman's Oracle SQL notes
and also from material from the
Oracle Database Concpets manual, Chapter 4)
Before you start
See the notes on page 1 of the PL/SQL tutorial.
Transaction Begin and End
In Oracle, a new transaction starts when you connect to the
database. A transaction ends when you explicitly issue a COMMIT or
ROLLBACK statement, or implicitly if you issue a Data Definition Language
(DDL) statement such as a create, alter, or drop statement.
A transaction also ends if you disconnect (transaction is committed).
If your process is eneded abnormally, the transaction is rolled back.
After one transaction ends, the next statement is the start of the next
transaction.
-- start of transaction 1
insert into foo values (1, 100);
insert into foo values (3, 300);
rollback;
-- start of transaction 2
insert into foo values (2, 200);
commit;
-- start of transaction 3
insert into foo values (5, 500);
rollback;
AUTOCOMMIT
When using SQL*Plus, be aware that there is an AUTOCOMMIT option
that may automatically commit each statement after you enter it.
You can turn this ON or OFF as follows:
SET AUTOCOMMIT ON
SET AUTOCOMMIT OFF
COMMIT and ROLLBACK
When you want to end a transaction and commit it, issue the COMMIT
statement. When you want to end a transaction and don't want to
commit it, you can ROLLBACK to the start of the transaction.
set autocommit off;
drop table foo;
create table foo ( pid INTEGER PRIMARY KEY, price INTEGER);
-- start of transaction 1
insert into foo values (1, 100);
insert into foo values (3, 300);
rollback;
-- start of transaction 2
insert into foo values (2, 200);
commit;
-- start of transaction 3
insert into foo values (5, 500);
rollback;
select * from foo;
SAVEPOINT
SAVEPOINT can be used to set save points in a transaction that
can be returned to using ROLLBACK.
drop table foo;
create table foo ( pid INTEGER PRIMARY KEY, price INTEGER);
set autocommit off;
-- start of transaction 1
insert into foo values (1, 100);
insert into foo values (3, 300);
savepoint sp1;
insert into foo values (2, 200);
savepoint sp2;
rollback to sp1;
-- start of transaction 2
insert into foo values (5, 500);
savepoint sp3;
commit;
select * from foo;
In-Class Exercise
Write a PL/SQL program that transfers $100 from account
101 to account 102 and records the transfer and
status (success/failure) in a ledger.
If account 101 does not have a balance
of at least $100, then the transaction should not occur,
but the failure should be logged in the ledger.
Use the following tables:
CREATE TABLE accounts (
aid INTEGER PRIMARY KEY,
balance INTEGER
);
CREATE TABLE ledger (
fromaid INTEGER,
toaid INTEGER,
amount INTEGER,
tdate DATE,
status INTEGER, -- 1 for success, 0 for failure
PRIMARY KEY (fromaid, toaid, amount, tdate),
FOREIGN KEY (fromaid) REFERENCES accounts;
FOREIGN KEY (toaid) REFERENCES accounts;
);
|