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.