Understanding Stored Procedures
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:
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.