Stored Procedures in MySQL 5.0

MySQL 5.0 introduced Stored Procedures which allow us to automate or program our way out of many tasks on directly on the server rather than having to write external scripts to do complex manipulation of data.

As you get used to writing stored Procedures in MySQL 5.0, you will, as with any other programming language, want to generalize your stored procedures as much as possible. The more flexible your stored procedure is, the more tasks it can be used for -- and the less places you have to go searching for that elusive bug that just keeps giving you the wrong result. The day you end up making a copy of a stored procedure just to change a name or two is the day you need to think how tweaking the original procedure can accomplish what you want without breaking old functionality.

Most stored procedures that you will be writing for MySQL 5.0 will undoubtedly reference static table and column names. However, sometimes it is desirable to be able to pass these details to a stored procedure as parameters.

There is no direct support for dynamic declaration of details such as column or table names in MySQLs stored procedures. However, by utilizing user variables (which have been around for a while) and prepared statements (introduced in MySQL 4.1), we can do some trickery to get the results we want.

What follows is a step-by step guide to demonstrate how we can build a prepared statement in the command-line client, then use the ideas gathered from these examples and build a stored procedure which can manipulate data from any table and column that we specify. The samples are by nature simplistic, but you should quickly be able to gather how you can extend these to much more complex usage.

The examples below are shown as you would type them into the command-line client, but the input prompts have been omitted for easy cut-and-paste.

The base tables for the examples are those found it the world database, which you can download on the MySQL documentation page.

In this article, we explore how to find the average of a given column in some table. For example, we might want to find the average life expectancy in all countries:

SELECT AVG(LifeExpectancy) FROM Country;
+---------------------+
| AVG(LifeExpectancy) |
+---------------------+
| 66.48604 |
+---------------------+

Another example is the average population of the cities defined in the world database:

SELECT AVG(Population) FROM City;
+-----------------+
| AVG(Population) |
+-----------------+
| 350468.2236 |
+-----------------+

The goal is to build a stored procedure that will take a table name and a column name and display the average of the values in the column, just as the two examples above.

The first thing we need to understand is how user variables are used and assigned values. A user variable is distinguished by having a '@' symbol in front of it, and values are assigned using the SET statement:

SET @a := 'abc';
SELECT @a;
+------+
| @a |
+------+
| abc |
+------+

We can assign the value of any expression to a user variable, so the following also works:

SET @s := CONCAT('SELECT AVG(' , 'Population' , ') FROM ' , 'City');
SELECT @s;
+----------------------------------+
| @s |
+----------------------------------+
| SELECT AVG(Population) FROM City |
+----------------------------------+

...which is exactly one of the statements we'd like our stored procedure to build on-the-fly and execute.

Next in line to consider is prepared statements. Prepared statements are statements (such as queries) which are sent to the server, but the processing of the statement is halted as soon as the statement has been parsed, and the parsed statement is remembered by the server. We then have the option of telling the server to execute that statement one or more times as we wish.

We can prepare a statement from within the command-line client, and we will do much the same later on in our stored procedure:

PREPARE stmt FROM "SELECT AVG(Population) FROM City";
EXECUTE stmt;
+-----------------+
| AVG(Population) |
+-----------------+
| 350468.2236 |
+-----------------+

As you can see, we prepare the statement using a simple string expression. A statement can also be prepared from a user variable:

PREPARE stmt FROM @s;
EXECUTE stmt;
+-----------------+
| AVG(Population) |
+-----------------+
| 350468.2236 |
+-----------------+

Note that a literal string expression or a user variable are the only ways you can specify the statement to be prepared. You cannot prepare a statement using an expression (which is also the reason why we're taking the seemingly roundabout way of using user variables to complete this task).

We're now a the stage where we can build our stored procedure colavg that will take a table and column name, and return the average of the values found in the column.

The first thing we do is to change the command-line client's command delimiter, to ensure that we can use semicolons inside the stored procedure without the client regarding them as end-of-statement. If you're using another client, you should of course skip the delimiter commands.

The input parameters are both of CHAR(64), which is the maximum size of these identifiers in MySQL. Since we are indeed reading from tables, we specify READS SQL DATA in the procedure declaration.

Inside the procedure, we concatenate the two static parts of the SELECT statement with the parameter values col and tbl into the user variable @s (remember, we can't use expressions in PREPARE statements). Finally, we execute the statement which will return the result to our client.

Finally, we reset the mysql client delimiter to the well-known semicolon.

delimiter //
DROP PROCEDURE IF EXISTS colavg//
CREATE PROCEDURE colavg(IN tbl CHAR(64), IN col CHAR(64))
READS SQL DATA
COMMENT 'Selects the average of column col in table tbl'
BEGIN
SET @s = CONCAT('SELECT AVG(' , col , ') FROM ' , tbl);
PREPARE stmt FROM @s;
EXECUTE stmt;
END;
//
delimiter ;

We now have a stored procedure which will allow us to select the average of any column in any table:

CALL colavg('Country', 'LifeExpectancy');
+---------------------+
| AVG(LifeExpectancy) |
+---------------------+
| 66.48604 |
+---------------------+

CALL colavg('City', 'Population');
+-----------------+
| AVG(Population) |
+-----------------+
| 350468.2236 |
+-----------------+

One final note on compatibility with other SQL dialects: In the stored procedure, we execute simple SELECT statements which returns the output of the SELECT statement to the client. This behavior is a MySQL extention to the SQL standard, which is not likely to work in other RDBMSs. In most cases, you'll probably want to SELECT into one or more user variables, then pass the result back in an OUT or INOUT parameter.

References:

• World sample database »
• The MySQL 5.0 Reference Manual »
• User variables »
• SQL Syntax for Prepared Statements »
• Stored Procedures and functions »