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
BEGIN
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;
COMMIT;
END;
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 transaction.
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:
BEGIN
...
UPDATE Employees SET ... WHERE id# = emp_id;
DELETE FROM Employees WHERE ...
...
SAVEPOINT more_changes;
...
-- make changes to Employees
-- possibly raise some_exception
...
COMMIT;
EXCEPTION
WHEN some_exception THEN ROLLBACK TO more_changes;
END;
Locking with Cursors
When accessing tables via a cursor, normally the table is locked.
PL/SQL provides a mechanism to lock individual rows instead:
DECLARE
CURSOR managers IS
SELECT emp#, job, salary
FROM Employees WHERE job = 'Manager'
FOR UPDATE OF salary;
BEGIN
FOR e IN managers LOOP
UPDATE Employees SET salary = new_sal
WHERE CURRENT OF managers;
COMMIT;
END LOOP;
END;