Stored Procedure Programming for MySQL5 - Part 1

by Ligaya Turmelle — Page 5 of 5

Click to rate:
216 votes / avg. rating 36.33%

Listing 13 – Toggle plain text mode – Select listing

mysql> CREATE PROCEDURE
statusOfDay(IN inVar VARCHAR(10), OUT outVar VARCHAR(30))

Note the IN/OUT parameters in our create statement. This is how you pass in variables to the stored procedure as well as get some data out of it. There are 3 types - IN (data going in to the SP), OUT (data coming out of the SP) and INOUT (data that goes in, is (hopefully) manipulated and then is sent back out). In this case we have a variable going in (inVar VARCHAR(10)) and a variable coming out (outVar VARCHAR(30)). It is important to note that we have to not only provide a name for these variables but also provide the datatype for the variable. Using the wrong datatype can and will generate the wrong output if not an error when you use the stored procedure.

Listing 14 – Toggle plain text mode – Select listing

-> BEGIN
-> IF(inVar = "Woohoo") THEN
-> SET outVar = "Today is a good day.";
-> ELSE
-> SET outVar = "Nothing to cheer about today.";
-> END IF;
-> END //
Query OK, 0 rows affected (0.00 sec)

This time our stored procedure consists of more then 1 line of SQL (the IF statement consists of multiple lines). To compensate for that we use the <a href=“#Delimiter”>DELIMITER</a> and the BEGIN/END statements. The BEGIN and END statements clearly mark where the stored procedure's body of SQL statements begin and end. Two things to pay attention to:

that although we have altered the DELIMITER for the creation of the stored procedure, we still use the semi-colan as the statement ending between the BEGIN and END statements.
that the character(s) used in the DELIMITER are placed on the end of the line that contains the END statement.

Listing 15 – Toggle plain text mode – Select listing

mysql> CALL statusOfDay("Woohoo", @a);
Query OK, 0 rows affected, 2 warnings (0.02 sec)

Just like calling a user function in PHP - you can call a stored procedure in MySQL. If you have arguements to pass to the stored procedures parameters - there needs to be a value. “Woohoo” is passed to the statusOfDay stored procedure and the @a variable is loaded with the value that is passed out of the stored procedure. Notice that the OUT variable was NOT displayed when we called the stored procedure.

So how do we get a hold of the value that was passed out of the stored procedure? Simple, we access the @a user variable in our SQL. Simply SELECT it.

Listing 16 – Toggle plain text mode – Select listing

mysql> SELECT @a;
+----------------------+
| @a |
+----------------------+
| Today IS a good day. |
+----------------------+
1 row IN SET (0.00 sec)

I personally like to check the functionality of my scripts with a little testing to make sure I get out what I am expecting. Here I use the statusOfDay stored procedure in a way to get the ELSE side of the IF statement. This way I know that both sides of the IF statement work.

Listing 17 – Toggle plain text mode – Select listing

mysql> CALL statusOfDay("Yuk", @a);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @a;
+-------------------------------+
| @a |
+-------------------------------+
| Nothing TO cheer about today. |
+-------------------------------+
1 row IN SET (0.00 sec)

Conclusion

Well there you have it - an overview of programming stored procedures for MySQL 5. With an understanding of the Basic Concepts and the line by line explaination of the examples, you should be ready to write a few of your own stored procedures. So go out there and put that database to work!

Please wait for the ride to come to a full and complete stop before exiting the vehicle. Thank you for riding Part 1 of the Stored Procedure ride and we hope to see you again at Part 2. Have a Great Day.

Ligaya Turmelle is a full time Goddess, occasional PHP programmer, and obsessive world traveler. She currently lives in Guam with her husband and daughter and their 2 Belgian Malinois. Actively involved with the PHP Community she hopes to one day actually meet the people she talks to.

< Back

Tags: mysql, procedure, d, storedprocedure, programming, ghdf

Add tag(s) (comma separated):

[Tags beta] — [Add New] — [Help]

Tags Help

Tags are keywords associated with a web page that help classifying information. You can find a good explanation here.

To add one or more tags to this page, simply enter them below (separate them with a comma) and hit enter or click on the "Go" button.

New Comment

This form allows you to type in a new comment. Keep in mind the following:

The system accepts input in plain text format. Newlines will be converted to the HTML equivalent, and the system will try to catch most URLs and make them clickable.
Your e-mail address will never be displayed. We will use it only to notify you when new comments are posted to this page.
As a rule, we do not delete comments unless they are offensive, racist, spam or otherwise inappropriate.
Bold fields are required

Your Name:
Your e-mail:
Type this number:
Subject:
Comment:

Comments

New Comment

Re: Stored Procedure Programming for MySQL5 - Part 1 (#208)

By Kasper A. Svendsen on 2006-07-12 12:34:04

Very nice article, good with the examples and so on! :=)