MySQL and stored procedures

Takeaway: Some people resisted using MySQL because it had no support for stored procedures. But starting with MySQL 5.0, that changed--now you can do stored procedures in this popular open source database.

MySQL is "The World's Most Popular Open Source Database," at least according to the MySQL Web site. But in spite of this popularity many corporations are resistant to adopting MySQL. There are several reasons for this, from the misguided belief that open source is the software equivalent of a child's wood shop project to the belief that nothing free is ever good. There was, however, one valid complaint against MySQL—unlike its shrink-wrapped counterparts, such as Oracle or DB2, MySQL doesn't support stored procedures.

Make that past tense—the latest developer release, MySQL 5.0, does support stored procedures. If you're not familiar with stored procedures, they are collections of SQL commands and program logic stored on the database server. These stored procedures can then be invoked by application programs thus eliminating the need for programmers with varying degrees of skill to create their own SQL.

Advantages
Stored procedures allow most database access logic to be separated from the application logic. One of the indirect benefits of using stored procedures is that application code becomes smaller and easier to understand. Another advantage of stored procedures is that the SQL can be "pre-compiled" increasing the speed of the application. Because stored procedures contain program logic, more processing can take place on the database server, which can reduce the amount of bandwidth consumed sending data back to the application. Also, when implementing an n-tier application, stored procedures are used to separate the data layer from the server layer.

Security can be another advantage of stored procedures. Applications can be granted execute privileges to the stored procedures, while being unable to access the tables directly. Unfortunately, at this time, MySQL doesn't support "GRANT EXECUTE". That means unless the application has the authority to access a table, then calling a stored procedure that accesses the same table won't work either. It's a good bet that this feature is pretty high up on the "to do" list for a future release.

Standards
Unlike either of the shrink-wrapped Oracle or Microsoft relational databases, which do not follow the current SQL:2003 syntax for stored procedures, MySQL and IBM's DB2 do conform to the syntax. Theoretically this means that, if the database structure is the same, stored procedures written for one will run on the other.

Supported SQL statements
Even though the paint isn't quite dry on MySQL's support of stored procedures, there's enough to get many tasks done, as Table A shows. In addition, the MySQL stored procedure documentation indicates that there may be future compatibility for Oracle's PL/SQL and SQL Server's T-SQL. My general impression of stored procedure support is that it is proceeding slowly in order to avoid any missteps that often plague large software development projects.
Table A

Statement Description CREATE PROCEDURE Creates a stored procedure, which is stored in the proc table in the MySQL database. CREATE FUNCTION Creates a user-defined function, essentially a stored procedure that returns data. ALTER PROCEDURE Alters a previously defined stored procedure that was created using the CREATE PROCEDURE statement. It does not affect related stored procedures or stored functions. ALTER FUNCTION Alters a previously defined stored function that was created using the CREATE FUNCTION statement. It does not affect related stored procedures or stored functions. DROP PROCEDURE Removes one or more stored procedures from MySQL's proc table. DROP FUNCTION Removes one or more stored functions from MySQL's proc table. SHOW CREATE PROCEDURE Returns the text of a previously defined stored procedure that was created using the CREATE PROCEDURE statement. This statement is a MySQL extension to the SQL:2003 specification. SHOW CREATE FUNCTION Returns the text of a previously defined stored function that was created using the CREATE FUNCTION statement. This statement is a MySQL extension to the SQL:2003 specification. SHOW PROCEDURE STATUS Returns the characteristics of a previously defined stored procedure; including name, type, creator, creation date, and modification date. This statement is a MySQL extension to the SQL:2003 specification. SHOW FUNCTION STATUS Returns the characteristics of a previously defined stored function; including name, type, creator, creation date, and modification date. This statement is a MySQL extension to the SQL:2003 specification. CALL Invokes a previously defined stored procedure that was created using the CREATE PROCEDURE statement. BEGIN ... END Contains a group of multiple statements for execution. DECLARE Used to define local variables, conditions, handlers, and cursors. SET Used to alter the values of both local variables and global server variables. SELECT ... INTO Used to store the indicated columns directly into variables. OPEN Used to open a cursor. FETCH Retrieves the next row using the specified cursor and advances the cursor one row. CLOSE Used to close and open cursor. IF An if-then-else-end if condition statement. CASE ... WHEN A case statement conditional construct. LOOP A simple looping structure; exiting is performed using the LEAVE statement. LEAVE Used to exit IF, CASE, LOOP, REPEAT and WHILE statements. ITERATE Used within loops to restart at the beginning of the loop. REPEAT A loop with the conditional test at the end. WHILE A loop with the conditional test at the beginning. RETURNS Returns a value from a stored function.

Stored procedure statements supported in MySQL 5.0

It is important to remember that support of stored procedures in the current incarnation of MySQL isn't as mature as Oracle, SQL Server or DB2. Also remember that it's more important to have a small number of features that work well rather than a ton of features that are, for lack of a better word, flaky. I know that it's an odd concept, but maybe the folks in the open source community have struck upon an idea that was somehow missed by the rest of the world.