A transaction is a sequence of SQL statements to accomplish
a single task.
Example: Transfer funds between bank accounts.
-- assume source_acct, dest_acct, amount,
-- and source_balance are defined
SELECT balance INTO source_balance
FROM Accounts WHERE acct# = source_acct;
-- check whether sufficient funds
UPDATE Accounts SET balance = balance-amount
WHERE acct# = source_acct;
UPDATE Accounts SET balance = balance+amount
WHERE acct# = dest_acct;
Oracle treats such a sequence as an indivisible unit, to
ensure that database is left in a consistent state.
PL/SQL gives fine-grained control over progress of
This also gives responsibility to ensure that transaction completes ok.
The first SQL statement begins a transaction.
COMMIT forces any changes made to be written to database.
ROLLBACK restores database to state at start of transaction.
Both COMMIT and ROLLBACK finish the transaction.
Can achieve finer-grained rollback via savepoints:
UPDATE Employees SET ... WHERE id# = emp_id;
DELETE FROM Employees WHERE ...
-- make changes to Employees
-- possibly raise some_exception
WHEN some_exception THEN ROLLBACK TO more_changes;
Locking with Cursors
When accessing tables via a cursor, normally the table is
PL/SQL provides a mechanism to lock individual rows instead:
CURSOR managers IS
SELECT emp#, job, salary
FROM Employees WHERE job =
FOR UPDATE OF salary;
FOR e IN managers LOOP
UPDATE Employees SET salary =
WHERE CURRENT OF managers;
To Page : 1,