Chapter – III

Transactions, Procedures and Functions

(Informatics Practices)

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.