Page 218 - Open Soource Technologies 304.indd

Unit 13: Stored Procedure

Setting an exclusive lock requires you to add the FOR UPDATE clause to your query. In the Notes
sequence below, you can see how locks are used to check available inventory in a product
catalog before processing an order. This example builds on the previous example by adding
more condition-checking.
This sequence of events is independent of the programming language used; the logical path can
be created in whichever language you use to create your application.

1. Begin transaction.
BEGIN WORK;

2. Check available inventory for a product with a specific ID, using a table called inventory
and a field called qty.
SELECT qty FROM inventory WHERE id = ‘ABC-001’ FOR UPDATE;

3. If the result is less than the amount ordered, rollback the transaction to release the lock.
ROLLBACK;
4. If the result is greater than the amount ordered, continue issuing a statement that reserves
the required amount for the order.
UPDATE inventory SET qty = qty - [amount ordered] WHERE id = ‘ABC-001’;

5. Insert a master record into the master order table.
6. Retrieve the ID from the master order record you just entered.
7. Insert records into the line items table for each item ordered.

8. If steps 5 through 7 are successful, commit the transaction and release the lock.
COMMIT;
While the transaction remains uncommitted and the lock remains in effect, no other users
can access the record in the inventory table for the product with the ID of ABC-001. If a user
requests the current quantity for the item with the ID of ABC-002, that row still operates under
the shared lock rules and can be read.

13.2 Stored Procedures

A stored procedure is a subroutine available to applications accessing a relational database
system. Stored procedures (sometimes called a proc, sproc, StoPro, StoredProc, or SP) are actually
stored in the database data dictionary.

Typical uses for stored procedures include data validation (integrated into the database) or access
control mechanisms. Furthermore, stored procedures are used to consolidate and centralize logic
that was originally implemented in applications. Extensive or complex processing that requires
the execution of several SQL statements is moved into stored procedures, and all applications
call the procedures. One can use nested stored procedures, by executing one stored procedure
from within another.
Stored procedures are similar to user-defined functions (UDFs).
The major difference is that UDFs can be used like any other expression
within SQL statements, whereas stored procedures must be invoked using
the CALL statement.

LOVELY PROFESSIONAL UNIVERSITY 213