Stored Procedure Programming for MySQL5 - Part 1

We will go over some basic concepts involved, some background information you may or may not know and then break a couple of examples down line by line to help you understand what is happening. I won't bore you with basic programming information - that you should know already. However I will be going over what differentiates stored procedure programming from PHP programming. So sit back and relax as we go for a bit of a ride. And please remember to place your trays in the full upright position and to keep your hands and feet inside the vehicle at all times.

Basic Concepts

Basically, a stored procedure is a “routine” written in SQL statements that are saved on the database server. For example you can create a stored procedure specificaly for your application. A payroll application may have a stored procedure that may (for example) read the employee worktime table and calculate the gross and net pay, then insert that information into the accounting tables, and finally calculate an employee's vacation time and update the employee table to reflect it. Without a stored procedure this would require a number of database calls with data going to and from the database and your program. With a stored procedure - it can all be done at once only returning to the application when it is fully completed.

Stored procedures are useful for reducing the network traffic between the client and server. This is done by only passing the data needed to run the stored procedure and the results of the stored procedure, not all the individual SQL statements and their results needed in between. Of course there is a trade off for this - increased load on the database server because more work is being done on there.

There are a few situations where stored procedures in particular excel.

• When you have multiple applications in different languages on different platforms all trying to interact with the same data. Stored procedures can form a unified API for accessing all the data.
• When data security is absolutely essential. If the only way you can get to the data is through specific stored procedures, you can easily control it and log all interactions.