Stored Procedure Programming for MySQL5 - Part 1

by Ligaya Turmelle — Page 4 of 5

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

A Better Example

This is a slightly more advanced example then last one. This stored procedure will take take in and pass out a variable and do some simple logic amongst other things. Nothing to hard - but something that will hopefully expand your mind a bit to the possibilities and power of stored procedures.

Listing 9 – Toggle plain text mode – Select listing

mysql> DROP PROCEDURE statusOfDay;
Query OK, 0 rows affected, 3 warnings (0.00 sec)
mysql> DELIMITER //
mysql> CREATE PROCEDURE statusOfDay
(IN inVar VARCHAR(10), OUT outVar VARCHAR(30))
-> BEGIN
-> IF(inVar = "Woohoo") THEN
-> SET outVar = "Today is a good day.";
-> ELSE
-> SET outVar = "Nothing to chear about today.";
-> END IF;
-> END //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

This example has a number of things that were not in the last example, so lets again break it down to see what each part does.

Listing 10 – Toggle plain text mode – Select listing

mysql> DROP PROCEDURE statusOfDay;

I personally like to drop the procedure with the same name as the procedure I am about to create. Saves me from any name clashes when I create the new procedure. Granted I have to be very careful not to accidentally drop a procedure created earlier (by me or someone else) with the same name that may or may not do something completely different then the stored procedure I am currently making... but I should have checked that BEFORE I got ready to write (never mind run) the SQL to create a new stored procedure.

Listing 11 – Toggle plain text mode – Select listing

mysql> DELIMITER //
...
mysql> DELIMITER ;

The DELIMITER command is a mysql client command that allows the user to change the statement delimiter. Since a stored procedure can be (and in this case is) a series of mysql SQL commands - when we are creating one we have to be able to place multiple SQL statements in the mysql client without it “running”. By using DELIMITER we can change the statement delimiter to something other then the “;”. A very quick example that changes the delimiter from a semi-colon to a double slash and back to a semi-colon.

Listing 12 – Toggle plain text mode – Select listing

mysql> delimiter //
mysql>SELECT COUNT(*) FROM t//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

< Back CONTINUED >

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! :=)