A DIY Approach to Stored Procedures in MySQL

What is a Stored Procedure?

A stored procedure is a set of SQL statements stored on the server that takes in certain arguments and processes that code with those arguments at execution time. It can be invoked simply by making a call to the procedure with the correct arguments. The difference between stored procedures and other sets of SQL statements is that stored procedures reside on the server and are pre-compiled.

Stored procedures are an integral part of the Oracle and MS SQL databases. MySQL has joined the bandwagon with its latest developer release. MySQL currently supports the SQL: 2003 syntax but will support T-SQL and Oracle PL/SQL syntaxes in the future.  However, I should warn you that this release is still in the alpha stage and is not ready for prime time just yet. Even then, it helps to understand a little more about stored procedures since a mature release may be available in the not–so-distant future.

Why should I care?

You have been getting by just fine without any knowledge of stored procedures, thank you very much. But take a minute to understand how stored procedures in general could benefit you:

1. Better performance  Stored procedures are faster because they are pre-compiled SQL code. This reduces the “Compile and Execute” step to just “Execute’ in most cases.  Also, only the call to the stored procedure needs to be sent to the server instead of chunks of information – this reduces the information that needs to be sent to the server and acts like a call to a remote procedure. This is an advantage when it comes to code that is called repeatedly. However, the load on the server is another point to consider since most of the processing will now be done on the server.
   
   
2. Easier to maintain  Since all the SQL can now be stored on the server, it is easier to make changes to the stored procedure than to a bunch of SQL statements distributed all over the application. 
   
   
3. Security  Although the use of stored procedures is not by itself a guarantee of security, it can be used to create an environment where applications and users can only access database tables through the stored procedures, instead of giving them direct access to the tables. The benefit is a layer of abstraction.
   
   
4. Optimization  When a SQL statement is parsed by the server, it is optimized internally by the server. If a bunch of SQL statements are sent to the server, repeatedly, they have to be optimized each time. The SQL statements in the stored procedure that is in memory have to only be optimized once and an execution plan is created for the SQL statements in the stored procedure.

But??

Frans Bouma has done a great job of listing the cons associated with the usage of stored procedures, with great passion – I might add - and so instead of repeating it, I will let you read it at http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx.

Using stored procedures is just one way to manipulate data. Good or bad – I leave it for you to decide. The scope of this tutorial is to have you creating stored procedures in MySQL in no time, should you want to. However, it is my opinion that there are times when the use of stored procedures is warranted for the reasons outlined above.