Database Reference
In-Depth Information
23
Procedures
In this chapter, you learn what stored procedures are, why they are used, and how
they are used. You also look at the basic syntax for creating and using them.
Most of the SQL statements that we've
used thus far are simple in that they
use a single statement against one or more tables. Not all operations are that
simple—often, multiple statements are needed to perform a complete opera-
tion. For example, consider the following scenario:
■
To process an order, checks must be made to ensure that items are
in stock.
■
If items are in stock, they need to be reserved so they are not sold to
anyone else, and the available quantity must be reduced to reflect the
correct amount in stock.
■
Any items not in stock need to be ordered; this requires some interac-
tion with the vendor.
■
The customer needs to be notified as to which items are in stock (and
can be shipped immediately) and which are back ordered.
This is obviously not a complete example, and it is even beyond the scope
of the example tables that we have been using in this topic, but it will suffice
to help make a point. Performing this process requires many MariaDB state-
ments against many tables. In addition, the exact statements that need to be
performed and their order are not fixed; they can (and will) vary according to
which items are in stock and which are not.
How would you write this code? You could write each of the statements indi-
vidually and execute other statements conditionally, based on the result. You'd
have to do this every time this processing was needed (and in every application
that needed it).
