Page 216 - Open Soource Technologies 304.indd

Unit 13: Stored Procedure

Introduction Notes

In a Database Management System (DBMS), a stored procedure is a set of Structured Query
Language (SQL) statements with an assigned name that’s stored in the database in compiled
form so that it can be shared by a number of programs. The use of stored procedures can be
helpful in controlling access to data (end-users may enter or change data but do not write
procedures), preserving data integrity (information is entered in a consistent manner), and
improving productivity (statements in a stored procedure only need to be written one time).

A stored procedure is a set of SQL commands that has been compiled and stored on the database
server. Once the stored procedure has been “stored”, client applications can execute the stored
procedure over and over again without sending it to the database server again and without
compiling it again. Stored procedures improve performance by reducing network traffic and
CPU load.

13.1 Transactions

A transaction is a sequential group of database manipulation operations, which is performed
as if it were one single work unit. In other words, a transaction will never be complete unless
each individual operation within the group is successful. If any operation within the transaction
fails, the entire transaction will fail.

A good example would be a banking transaction, specifically a transfer of $100 between two
accounts. In order to deposit money into one account, you must first take money from another
account. Without using transactions, you would have to write SQL statements that do the
following:
1. Check that the balance of the first account is greater than $100.
2. Deduct $100 from the first account.

3. Add $100 to the second account.
Additionally, you would have to write your own error-checking routines within your program,
specifically to stop the sequence of events should the first account not have more than $100 or
should the deduction statement fail. This all changes with transactions, for if any part of the
operation fails, the entire transaction is rolled back. This means that the tables and the data
inside them revert to their previous state.
13.1.1 Properties of Transactions

Transactions have the following four standard properties, usually referred to by the acronym
ACID:
• Atomicity ensures that all operations within the work unit are completed successfully;
otherwise, the transaction is aborted at the point of failure, and previous operations are
rolled back to their former state.
• Consistency ensures that the database properly changes states upon a successfully
committed transaction.
• Isolation enables transactions to operate independently of and transparent to each other.
• Durability ensures that the result or effect of a committed transaction persists in case of
a system failure.

LOVELY PROFESSIONAL UNIVERSITY 211