MySQL 5.0 Stored Procedures : Part 3

SO far we have looked at creating a simple procedure, then we added a couple of more lines to make it into a multiline procedure, but after all that our procedure is still a little useless. So let's look at how to make it a little more dynamic by passing in a value (or values) via a parameter.

Again I'll keep things simple, if you want to know more about parameters in programming in general pretty much any book on the subject of programming will have some sort of explanation so I'll leave you to do the research on that. But in essence parameters allow us to pass information in (and in the case of procedures, out) of our stored routines. "Hey, hold on" you all say.... "What's all this routine buisness". I suppose now is as good a time as any to introduce the word routine, so far we have been using the term "Stored Procedure" to mean program elements stored permanently in MySQL. But it's possible to use both functions and procedures, therefore it's good to have a term which covers both and make a distinction when talking explicitly about a procdure rather than routines in general.

...anyway back to parameters. So let's take our "hello world" example a stage further , lets say we wanted to say hello to anybody we chose. We could of course create a procedure for all our users, but a much better solution would be to pass in the name of the person we want to say hello to. I glossed over the () placed at the end of the procedure name in the first part, but it's this that we use to pass the parameters in and out of our routine. By default parameters are classed as IN parameters, this means the procedure will accept values into them, but we can also define them as OUT or even INOUT.

All we need to do is give the parameter a name and a datatype, the datatype can be any valid MySQL datatype that you would use for a column datatype in a table. Once it's defined we can then reference the parameter in our procedure using it's name.

mysql> delimiter //
mysql> create procedure 
    -> testParam(p_name varchar(30))
    -> begin
    ->    select concat('Hello ',p_name);
    -> end
    -> //
Query OK, 0 rows affected (0.06 sec)

mysql> call testParam('Bob')//
+-------------------------+
| concat('Hello ',p_name) |
+-------------------------+
| Hello Bob               |
+-------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

That's all there is to it. We can now just call the procedure using any name we like.

mysql> call testParam('Sue')//
+-------------------------+
| concat('Hello ',p_name) |
+-------------------------+
| Hello Sue               |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

OK, now for your home work.... This page has information on the different types of parameters.

http://mysql.gilfster.com/page.php?parent_id=1.1&page_id=1.1.6

In the next blog entry I'll be looking at interacting with the database using SQL inside the procedure. I promise that this is going somewhere usefull :)

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.

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

MySQL on the Mac

I read with interest Zack Urlocker's blog entry for today on his experience with a new Intel based Mac MySQL have recently got their hands on. I was looking for a new PC at the start of the year, maily because of some problems with my current windows box having problems communicating with various USB devices. I've always liked the look of Macs but the expense has always put me off, I'm not made of money after all.

But lucky for me (and apple), my looking for a new PC coincided with Steve Jobs announcment of the Mac Mini. Anyway I'm sure you're not interested in my shopping habits so on to the important part.

I purchased the Mac Mini to develop my website www.mysqldevelopment.com (shamless plug) on, that meant I needed some html writing software, an FTP client and of course a MySQL database. One of the great things which became apparent straight away was that the MySQL command line client is identical to the Windows version that I had been using before, this meant that there was zero downtime waiting for me to get up to speed with using MySQL on the Mac. The other great thing was that all the scripts I produced were immediately portable to the windows (or Linux) versions of MySQL.

To try and tie this in to the theme of this blog (a guide to MySQL for Oracle users) it's the same with Oracle's SQL*Plus, it's the same on any platform Oracle supports (which is pretty much everything).

So if the switch to Intel by apple has put the seed in your head, don't worry for a second about running MySQL, it works just the way you like it.

Here ends the Mac/MySQL plug.

5.0.13 Coming to a MySQL installation soon

I've taken a great deal of interest in version 5 of MySQL, mainly because of my background in procedural extensions for other databases. It's been pretty exciting watching the development of version 5 and as somebody who has been keen to exploit the features I've been running as current a version as possible (it's currently at 5.0.12), this has meant at times a bi-weekly installation of the latest version.

One of the biggest requests from people on the stored procedures forum over on the MySQL website has been for information on using prepared statements, I'll gloss over the fact that for 90% of these situations prepared statements are not the way to go. MySQL Stored Procedures did infact support prepared statements for a while but this support was removed in version 5.0.9, this caused all manner of confusion and fist waving by the masses and resulted in a flood of angry forum entires asking where they had gone and why MySQL had pulled the plug....

...the great news is that the latest release notes for MySQL 5.0.13 tell us they are back. For the moment they will only be supported in Stored Procedures and not Functions or Triggers. The bad news is that 5.0.13 hasn't been released yet (at least not at the time of writing) so all you prepared statement fans need to hold on just a little longer.

Now all we need to do is to convince people that a monster stored procedure with 25 parameters to build all their SQL calls dynamically isn't the best use of stored procedures ;)

Sorry

It seems the day job has finally caught up with me. I've been so busy that I haven't had time to update this blog in a few weeks. But please do keep checking back as we should be back to normal service soon.