Open Source

A couple of MySQL stored procedures myths get busted


This post is also available as a TechRepublic download.

Two often repeated myths about MySQL stored procedures include (1) MySQL does not support stored procedures and (2) MySQL stored procedures are too difficult to work with under typical circumstances. This tutorial busts those myths and shows you how easy it is to implement stored procedures in your database using MySQL.

I have to admit something -- I am a big a fan of the television show MythBusters. For those who are unfamiliar with MythBusters, the basic format of each episode is an attempt to confirm, bust or find plausible one or more myths or urban legends. For example, in their 40th episode they covered the legend that claimed the Confederate States of America launched a long-range two stage rocket at Washington DC from Richmond Virginia.

In the spirit of MythBusters I'd like to try and bust a couple of myths myself. I figure that I'm as qualified as Adam Savage and Jaime Hyneman, because my hair is red and I have a mustache. In addition to that I also have little regard for personal safety -- after all, I eat my own cooking. Yes, danger is my middle name, well okay maybe it isn't danger, maybe it is more like food poisoning, but I can dream.

The myths that I'd like to bust, confirm, or find plausible involve the open source database MySQL and stored procedures. I'm probably opening up a can of worms here, but the first myth is that MySQL doesn't support stored procedures. Fortunately, this is an easy myth to test.

Since I'm playing MythBusters, I'll first attempt to find documented proof of the existence of MySQL stored procedures. Yeah, this is an easy one. A Google search of "MySQL stored procedures" results in 1,230,000 hits as shown in Figure A. A search on Amazon.com for books covering the same topic also returns results as Figure B illustrates. Finally, a search for the same on Safari, the online book shelf, returns the results shown in Figure C.

Figure A

Google results on "MySQL stored procedures"

Figure B

Amazon results on "MySQL stored procedures"

Figure C

Safari results on "MySQL stored procedures"

From the examination of these results it's pretty easy to proclaim the myth that MySQL does support stored procedures as BUSTED.

Another myth

There's a second myth that isn't specifically a MySQL myth, I've actually heard it used to describe stored procedures in general. This is the myth that stored procedures are, in general, too hard to understand. In order to test this final myth we'll need a database and a couple of tables like the ones shown in Listing A and Listing B and Figure D and Figure E.

Listing A -- Video table

DROP TABLE IF EXISTS `video`.`video`;

CREATE TABLE `video`.`video` (

`indx` int(10) unsigned NOT NULL auto_increment,

`name` varchar(255) NOT NULL,

`minutes` int(10) unsigned default NULL,

`released` int(10) unsigned default NULL,

`format` int(10) unsigned NOT NULL,

PRIMARY KEY USING BTREE (`indx`,`name`,`format`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Listing B -- Format table

DROP TABLE IF EXISTS `video`.`format`;

CREATE TABLE `video`.`format` (

`indx` int(10) unsigned NOT NULL auto_increment,

`name` varchar(10) NOT NULL,

PRIMARY KEY (`indx`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Figure D

SELECT from the video table

Figure E

SELECT from the format table

Yeah, I admit it, I like movies.

First let's try to query the video table with a join to the format table. After all, what good is a numeric code as a video format when the actual text is available? With this in mind I wrote the SQL statement shown in Listing C.

Listing C -- An SQL statement

SELECT v.name,

v.minutes,

v.released,

f.name AS format

FROM video v

JOIN format f ON v.format = f.indx

ORDER BY v.name ASC;

Not to shabby, eh?

Well, let's examine the difficult, convoluted, arduous process of using the SQL statement shown above as the basis of a stored procedure. The first thing that we'll need to do is declare a delimiter so that MySQL can distinguish between the semi-colon used to terminate individual SQL statements and whatever we chose to terminate the stored procedure. For example, if we chose to use $$ to terminate the stored procedure we'd code the statement DELIMITER $$ and the delimiter is two dollar signs. Of course, we will have to remember to set it back with DELIMITER ; after the stored procedure to prevent issues further down the road.

After working out the issues involving the delimiter, the process of creating a stored procedure is rather straight-forward. This is especially true once when starting with a previously written SQL statement like the one in Listing C. It essentially comes down to surrounding the SQL statement with a few lines of code and ending-up with a stored procedure like the one shown in Listing D.

Listing 4 -- The SQL from listing 3 as a stored procedure

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getVideos`()

BEGIN

SELECT v.name,

v.minutes,

v.released,

f.name AS format

FROM video v

JOIN format f ON v.format = f.indx

ORDER BY v.name ASC;

END $$

DELIMITER ;

While the above is all well and good, it does leave a few unanswered questions. The first of which is just how does one invoke a stored procedure? Easy, you do it as shown in Figure F.

Figure F

Invoking a stored procedure
The next question is what if I make a mistake? This one is easy, too, but there are two answers. The first is that you could always edit the stored procedure as shown in Figure G. The alternative is to give-up on it and drop the stored procedure as shown below:

DROP PROCEDURE IF EXISTS `video`.`getVideos`;

Figure G

Editing a stored procedure
Before declaring this myth as busted, I'd like to provide the final version of my stored procedure in Listing E. As you can see it accepts an input parameter that allows me to specify which format that I'm interested in. In instances where I'm not feeling particular about the format, passing a NULL in parenthesis will result in all of the videos stored in the table.

Listing E -- Final stored procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `video`.`getVideos` $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getVideos`(

IN format INTEGER)

BEGIN

SELECT v.name,

v.minutes,

v.released,

f.name AS format

FROM video v

JOIN format f ON v.format = f.indx

WHERE (format IS NULL

OR format = v.format)

ORDER BY v.name ASC;

END $$

DELIMITER ;

Try it

The final myth that stored procedures are too hard is BUSTED. However, unlike the real MythBusters, while I am a professional, you really should try this at home. So, download MySQL 5.0 or higher and the GUI Tools, install them and bust some myths yourself.

6 comments
perkiset
perkiset

Yeah, I should get the Thread Resurrection award ... but in any case, I've written and am growing a package called phpMyIDE which is specifically for working with stored procedures, functions. triggers and views, and it's all web based ala phpMyAdmin. If anyone is still reading this topic, love to have you come by and try it. Find it here: http://www.phpmyide.com/

Lcdelgado
Lcdelgado

can you help me to create an stored procedure?

gustavo.cruzortiz
gustavo.cruzortiz

i always think that the MySql's stored procedures are very difficult to implement so until now i never use or intend to use it, with your simple but very helpful tutorial i will start using it, thanks.

pointzerotwo
pointzerotwo

MySql has come a long way in the past few years, but I'm wondering why it is so much more popular than Postgres in the open source community. Postgres has had advanced features like stored procedures, views, transactions, etc. years before MySql. Postgres also uses the wide-open BSD license that really lets you use it however you want, compared to MySql's much more restrictive dual GPL/commercial licensing. I used PostgreSql for the first time about six months ago and was very impressed. Most of my prior experience was with Sql Server 2000/5. It just seems funny that people still get excited about "new" technologies like stored procedures.

Ed Woychowsky
Ed Woychowsky

It only took a couple of days, a six-pack of Dr Pepper and a bag of Goldfish crackers to figure them out. Open Source is wonderful, but as with most software the documentation is lacking.