MySQL 5.0 Stored Procedures : Part 2

In theb last blog entry I looked at how to create a very simple stored procedure. While it demonstrated how quick and easy it was to create a stored procedure it wasn't particuarly usefull.

So today I'll be showing you two additional things (spread over two entries), you can do to expand on yesterdays example. First up it's creating a multiline procedure. On first consideration this might not seem to be something you need to deal with, why can't you just keep adding lines to the procedure. The problem with that is that MySQL needs to firstly know where the procedure code starts and ends (in our first example it wasn't a problem because it was a single line of code) and also it needs to know when a particular command ends.

Take for example the MySQL command line tool, when you enter an SQL statement you need to add a semicolon ; to the end of the SQL command to tell MySQL that there is no more to enter and to begin processing the command. This is the same for stored procedures, you need to tell MySQL where the individual commands in the procedure end. However MySQL uses the same line delimiter for stored procedures as it does for normal command line operations, this means that when entering multiline procedures we need to change the default delimiter character. Doing this is trivial but it does mean you have to get yourself into "different delimiter mode" in that if your anything like me you will be constantly wondering why MySQL hasn't processed that select.

mysql> delimiter //

Personally I use the // as the new delimiter, your free to choose one you prefer but // seems to work well and not clash with MySQL. So how does this effect MySQL, where we would have used ; we now need to use the new delimiter.

mysql> select 'Test of Delimiter'//
+-------------------+
| Test of Delimiter |
+-------------------+
| Test of Delimiter |
+-------------------+
1 row in set (0.00 sec)

So now the question is how does this relate to stored procedures. Let's create a similar procedure to our last, this time rather than using a literal in the select we can first set a user variable and then select that. The second thing we need to do when creating multiline stored procedures is enclose the body of the procedure (the actual code but not the head) within a begin and an end block. So it looks like this...

mysql> create procedure testProc()
    -> begin
    -> set @a = 'HelloWorld';
    -> select @a;
    -> end;
    -> //
Query OK, 0 rows affected (0.28 sec)

You can see that the two lines are enclosed within the begin and end and that each one is terminated with the ; delimiter. Because we have changed the MySQL command line default delimiter to get MySQL to process the create procedure statement we need to enter the new delimiter after the procedure statement.

So to run the procedure we just call it as we did with our first example.

 
mysql> call testProc()//
+------------+
| @a         |
+------------+
| HelloWorld |
+------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Great we have created a multiline procedure, but it still doesn't do a great deal.