CBSE Class 12 Informatics Practices Transactions Procedures and Functions

Chapter – 4

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

PL/SQL provides packaging mechanism for small blocks of procedural code:

PROCEDURE ProcName(ArgList) IS

declarations;

BEGIN

statements;

EXCEPTION handlers;

END ProcName;

FUNCTION FuncName(ArgList) RETURN Type IS

declarations;

BEGIN

statements; -- including RETURN Expr;

EXCEPTION handlers;

END FuncName;

Please refer to attached file for CBSE Classs 12 Informatics Practices Transactions Procedures and Functions