Stored Procedure Programming for MySQL5 - Part 1

by Ligaya Turmelle — Page 1 of 5

Click to rate:
216 votes / avg. rating 36.33%

With the release of MySQL5 a bold new world opens up to the PHP developer... the world of a database programmer. In this world the interaction with the data can be done right where the data is located - not in a script that is far far away in a distant server. In this article we will be taking you on a journey that will introduce you to MySQL's stored procedures.

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.

Note:MySQL follows the SQL::2003 syntax for stored procedures. This syntax is also followed by IBM's DB2.

CONTINUED >

Tags: mysql, procedure, d, storedprocedure, programming, ghdf

Add tag(s) (comma separated):

[Tags beta] — [Add New] — [Help]

Tags Help

Tags are keywords associated with a web page that help classifying information. You can find a good explanation here.

To add one or more tags to this page, simply enter them below (separate them with a comma) and hit enter or click on the "Go" button.

New Comment

This form allows you to type in a new comment. Keep in mind the following:

The system accepts input in plain text format. Newlines will be converted to the HTML equivalent, and the system will try to catch most URLs and make them clickable.
Your e-mail address will never be displayed. We will use it only to notify you when new comments are posted to this page.
As a rule, we do not delete comments unless they are offensive, racist, spam or otherwise inappropriate.
Bold fields are required