Stored Procedure Programming for MySQL5 - Part 1

by Ligaya Turmelle — Page 2 of 5

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

Background Information

Now we could just jump into stored procedures here - but I wish to start by talking about a few things that you may or may not know about. All of these subjects are related to stored procedures even though they aren't necessary to create a stored procedure.

Many people do not realize that MySQL has a number of logic flow and looping structures available for both inside and outside a stored procedure. I, of course, will be using the stored procedure “types” in my examples but I won't get very deep into them since it is assumed you know the basics of programming logic already. I definitely want to make sure you have a quick overveiw though. Be sure to read the manual pages if you have any questions.

MySQL has the standard IF statement with ELSEIF and ELSE support as well as the CASE statement (logical equivalent to the PHP “switch” statement). These, of course, control any logical decisions to be made in the stored procedures. Both use a different syntax then PHP so be sure to check out their manual pages for the exact syntax.

MySQL has 3 looping structures. They include the Labeled LOOP, the REPEAT statement (equivalent to a do-while), and the WHILE statement. Along with these looping structures you have the LEAVE and ITERATE statements which are equivalent to PHP's break and continue statements. The looping allows you to easily control repetitive operations, but you already knew that. Again check out their manual pages if you are unfamiliar with their syntax.

MySQL does allow the creation of user defined variables both inside and outside stored procedures. Outside of stored procedures you can create variables with the SET keyword. Inside the stored procedure you can use the DECLARE statement for locally scoped variables as well as a SET statement (an extended version of the general SET). Being able to create and manipulate variables is a wonderful thing after all.

Listing 1 – Toggle plain text mode – Select listing

mysql> SET @a = "This is a test of the emergency
broadcasting system";
Query OK, 0 rows affected (0.06 sec)
mysql> SELECT @a;
+-----------------------------------------------------+
| @a |
+-----------------------------------------------------+
| This IS a test of the emergency broadcasting system |
+-----------------------------------------------------+
1 row IN SET (0.00 sec)

MySQL has a very fine grained permission system built in. To see if you have been given the proper permissions to create a procedure you can type in

Listing 2 – Toggle plain text mode – Select listing

SHOW GRANTS FOR CURRENT_USER;

Check to see if “CREATE ROUTINE” is listed. If it is - you are golden. If not - then we need to give you the proper permissions. Otherwise you will be sitting in that chair with nothing to do but read - boring.

Now without getting too into it - log in to mysql as a user that has the GRANT priveledge on the database you will be working on (worst case scenerio - root, though that should be used only after all other users have been tried. Then be very careful.) and type in

Listing 3 – Toggle plain text mode – Select listing

mysql>GRANT CREATE ROUTINE ON DATABASE
TO 'user'@'host' IDENTIFIED BY 'password';

Fill in database, user, host and password with your appropriate values. For more information on GRANT and MySQL permissions please see their manual pages.

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