Understanding Stored Procedures

Note

Requires MySQL 5 Support for stored procedures was added to MySQL 5. As such, this chapter is applicable to MySQL 5 or later only.

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 simpleoften, multiple statements will be needed to perform a complete operation. 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 interaction 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 book, but it will suffice to help make a point. Performing this process requires many MySQL statements 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 individually 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).

Or you could create a stored procedure. Stored procedures are simply collections of one or more MySQL statements saved for future use. You can think of them as batch files, although in truth they are more than that.