MySQL 5.0 Stored Procedures : Getting Started

In response to MySQL's call to arms (or should that be call to keyboards) with regard to MySQL 5.0 going to the RC stage, over the next few days I'll be giving you a brief introduction to Stored Procedures within MySQL. Hopefully this should give you enough of a heads up to start seriously testing this new aspect of MySQL.

Stage one of course is to download and install the lastest version of MySQL, that's currently 5.0.13 and is available here http://dev.mysql.com/downloads/mysql/5.0.html

OK, once you're all installed let's start up a MySQL command line session and select a database to develop against. Let's keep it really simple to start with, I won't go into too much detail during these tutorials, if you need more info I'll point you in the right direction at the end.

To start we will use the ubiquitous HelloWorld program to simply display some text on the MySQL command line. To do this all we need to do is call a select statment. To create a procedure we just need to type the keywords CREATE PROCEDURE give the procedure a name, add a set of brackets (more on those tomorrow) and then enter our select command.

mysql> create procedure helloword() 
     -> select 'Hello World';
Query OK, 0 rows affected (0.30 sec)

Great, we can tell by the message that the procedure was created (there were no errors or warnings reported). All we need to do now is run our procedure, this is just as simple as writing it in the first place. Just type CALL, then our procedure name followed by those brackets again.

mysql> call helloword();
+-------------+
| Hello World |
+-------------+
| Hello World |
+-------------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

Congratulations thats your first MySQL Stored Procedure. You may be saying to yourself that we haven't done anything you could have done straight from the command line (and you would be correct), but the thing with stored procedures is we can run the same thing again and again and agian.... and so on. I'll explain more about why thats good later but just to test the theory try and run the procedure again.

OK before you get a call from your MySQL DBA I better tell you how to remove your procedure. It's just as easy to get rid of one as it is to create one.

mysql> drop procedure helloword;
Query OK, 0 rows affected (0.20 sec)

Thats all there is to it. Next time we will look at creating a procedure which has more than one line of code and how to pass values into our procedure and adapt the output accordningly.

If you keen to learn more and don't want to wait for the next installment check out the following resources.

http://www.mysqldevelopment.com
http://dev.mysql.com/doc/mysql/en/stored-procedures.html
http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html