CBSE eBooks

CBSE Guess > eBooks > Class XII > Informatics Practices By Agnelo

Transactions

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;