Stored Procedure Programming for MySQL5 - Part 1

by Ligaya Turmelle — Page 3 of 5

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

Stored Procedures

Now for the nitty gritty. Time to get started with stored procedures. Go to the database you will be learning in and follow along.

First we will create a simple example (Hello World of course - I firmly believe in tradition), then we will break it down into it's various parts.

Listing 4 – Toggle plain text mode – Select listing

mysql> CREATE PROCEDURE HelloWorld()
-> SELECT 'Hello World';
Query OK, 0 rows affected (0.08 sec)

Ok. So what did I do, and why does that kinda look familiar? Lets break it down.

Listing 5 – Toggle plain text mode – Select listing

mysql> CREATE PROCEDURE HelloWorld()

Here we are using the 'CREATE PROCEDURE' keywords to make a procedure named HelloWorld() in the current database. Note the empty parenthesis behind HelloWorld. Like a PHP function those are required. If we were passing in data or passing out data - that is where we would declare it. Looks kinda like what we do when we make a user defined function in PHP.

Listing 6 – Toggle plain text mode – Select listing

-> SELECT 'Hello World';

The next line is where we placed our SQL statement. In this case it is a simple SELECT with a string. Note the semi-colon at the end of the line. That submits the SQL to MySQL... Anyway - nothing scary here.. moving right along.

Listing 7 – Toggle plain text mode – Select listing

Query OK, 0 rows affected (0.08 sec)

Well there you go. The query ran ok - and you created your first stored procedure. Congratulations! Flex those coding muscles.

Feeling proud of yourself? Wait... You want to use it now?! Greedy lot aren't you. Very well, let's use our stored procedure to display “Hello World”...

Listing 8 – Toggle plain text mode – Select listing

mysql> CALL HelloWorld();
+-------------+
| Hello World |
+-------------+
| Hello World |
+-------------+
1 row IN SET (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

Did you notice we used the SQL keyword CALL along with the stored procedure's name? And let's not forget the parenthesis after the procedures name. Pretty easy isn't it - almost insultingly easy. Ready to try something a bit harder?

Good. Again - I am assuming you already know basic programming logic. I won't be explaining that as we go along, so if you don't understand it - go back and read the manual pages provided in the Background Information section.

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