Saturday 23rd of May 2015 12:05:27 AM MySQL 5 Stored Procedures - An Introduction - Part 1
Andrew Gilfrin,

This document introduces the subject of procedural programming within MySQL, highlights the main features and functions and gives a quick tutorial on the basics. It will be followed by part 2 which will introduce more advanced features of stored procedures within MySQL.

With the release of MySQL 5 AB has addressed one of the major reasons ‘serious’ database developers may have avoided using MySQL, despite its self confessed title of “The World's Most Popular Open Source Database”. 

Stored procedures and functions are executable program units stored within the database server and can be used for a number of important data manipulation tasks that cannot be completed by SQL alone. Its true to say that with a mixture of some external programming and SQL inside MySQL it may be possible to achieve the same results as using stored procedures, but that’s not a reason to dismiss them or even think of them as in some way inferior, in fact the opposite is often true. 

One of the great advantages of stored program units within a database is the ability to deal with data at source rather than pulling it down, manipulating it and passing it back. This is particularly useful in situations where a database is accessed by multiple interfaces. For example lets say you had both a Visual Basic application and a web front end accessing a MySQL database, you may have written some code both in VB and PHP to enforce a business rule, what happens when this business rule changes? Both sections of code need to be rewritten which introduces not just two programming jobs but two testing jobs and the possibility of two points of failure or error. Using procedural programming within MySQL we can create one set of business rules callable by both the VB and web front end, in fact callable from any front end we chose to implement in the future. There are certainly those who prefer to do all their processing at the front end but to a database developer that sounds as logical as writing your presentation layer in the database, data processing surely belongs in the database. 

So onto MySQL’s implementation of procedural programming, lets first point out its early days and only available in the very latest releases (5.0 and up), when compared with other implementations such as Oracle and MS SQL Server it is very immature but MySQL have taken the decision to get the basic’s right first rather than pack it full of useful but ultimately problematic features. Having said this seasoned database developers will be pleased to see many of the features available in other databases. 

So enough of the theory lets get straight into developing a few programs and see what can be done. Unfortunately the installation and set up of MySQL is outside the scope of this document, but we will be using version 5.0.0alpha release if you would like to play along. This can be downloaded free from the MySQL website and is relatively easy to install. Note that stored procedures are only available in version 5 and up so if your using a lower version you won’t be able to try it out. 

So lets start. We will create a new database and set up a few tables first so we have some thing to work with. 

create database pers;
use pers;
create table emps (emp_no integer, ename varchar(30),dept varchar(5));
create table dept (dept_id varchar(5),description varchar(30));
insert into emps values (1,'Bob','IT'), (2,'Alan','SAL'),(3,’Jane’,’SAL’);
insert into dept values (‘SAL’,’Sales’),(‘IT’,’Information Technology’);
We will come back to the tables later so now we will turn our attention to creating a simple function. 

When writing procedures and functions we need tell the compiler when the line ends, this is done with a semi colon (;). However MySQL interprets this as a delimiter character when we are using the console so when entering our procedures and functions we need to use a different delimiter.

So lets set that now 

delimiter //

Now whenever we would have normally used the ; we use //. So lets enter the source for the function.

Create function HelloWorld() Returns varchar(20)
Return ‘Hello World’;
Query OK, 0 rows affected (0.00 sec)

Hopefully you should have created the function and received the Query OK message. If you haven’t check what you have typed carefully.

Now lets run the function. 

select helloworld() //
+--------------+
| helloworld() |
+--------------+
| Hello World  |
+--------------+
1 row in set (0.00 sec)

So that shows you how simple it is to create a function in MySQL, to call that function and see the results. That’s all there is to it.
 Okay, links!

Links reference other web pages, images, etc. on other servers by URL(Uniform Resource Locator).  A URL specifies a  schemethat tells the browser how to handle the file to be received from the server,the server name, and the path and filenameof the file on the server.  Common URL schemes include: