(E-Book) Class XII Informatics Practices : Chapter - IV (Transactions, Procedures and Functions)

Disclaimer: This website is NOT associated with CBSE, for official website of CBSE visit - www.cbse.gov.in

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
    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 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.