Wednesday 23rd of April 2014 04:45:30 PM 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.

Lets now go into some more detail about how a procedural program is built within MySQL. 

To create procedures and functions we use the following syntax 

CREATE PROCEDURE sp_name ([parameter[,.
[characteristic ...] routine_body

CREATE FUNCTION sp_name ([parameter[,..
[RETURNS type]
[characteristic ...] routine_body

A program unit name will follow the basic naming conventions used with in MySQL, its best to use simple but descriptive names and to avoid anything other than alphanumeric characters and underscores. The routine body is the main body of the code where you write your SQL statements. We will talk about parameters and characteristics later, so at this stage the only thing worth mentioning about the create syntax is the RETURNS keyword. As yet we haven’t talked about the difference between functions and procedures. A function may or not accept a parameter or parameters but must always return 1 and only 1 value to the calling program. A procedure again can accept none or more parameters but can also return none or more values. As a general rule of thumb functions are used to make simple changes to individual values, procedures are used to do more complex processing. So back to the RETURNS keyword, RETURNS must be used when writing a function to specify what datatype the procedure will return as being a function it will return a value. 

The Routine Body 

The routine body is where we place our SQL statements that will actually perform the calculations and manipulation of data. In our first simple function we had a single line of code, which in itself was fun but pretty useless. When adding more than one line of code we need to enclose our statements within a BEGIN and END. Even with a single line function its still possible to use BEGIN and END and in fact it should be positively encouraged. So lets do that now with our simple one line function. Firstly we need to drop the function.

Drop function HelloWorld
CREATE FUCNTION HelloWorld() RETURNS VARCHAR2
BEGIN
RETURN ‘Hello World’;
END

It may at this point be worth creating a file to store your function so that we don’t have to keep dropping it and then recreating it by hand. Create a file like so

use pers
DROP FUNCTION IF EXISTS HelloWorld
CREATE FUNCTION HelloWorld() RETURNS VARCHAR(20)
RETURN 'Hello World 2';

I have saved mine in a folder called source in my MySQL folder. So now all I have to do to run the function is call the following 

source c:/mysql/source/helloworld.sql
select HelloWorld() //

A word of warning, use / rather than \ as certain folder names can cause MySQL to think your calling various MySQL commands. 

Variables

Those who are familiar with other programming languages will be equally familiar with the term variable. A variable is in essence a named and reserved area of memory, which can be referenced from within your program. We can create variables using any datatype supported in MySQL. 

This rule will cause the first line of any paragraph to be indented aquarter-inch, as shown in Figure 4-1.

Figure 4-1

Figure 4-1. Text indenting

images -- which makes sense, ofcourse. However, if you have an image within the first line of ablock-level element like a paragraph, it will be shifted over withthe rest of the text, as shown in
Figure 4-2.

Figure 4-2

Figure 4-2. Text indenting and images

To create a variable we using the following syntax 

DECLARE variable_name variable_type;

Where the variable_name is the unique name we wish to assign and variable type is the datatype of the variable we wish to create. 

So to create a variable called output_text containing a VARCHAR of 20 characters we use the following syntax 

DECLARE output_text VARCHAR(20);

Now within our program we can set and reference that variable like so.

CREATE FUNCTION HelloWorld() RETURNS VARCHAR(20)
BEGIN
DECLARE outtext VARCHAR(20);
SET outtext = 'Hello World';
RETURN outtext;
END

The SET keyword simple allows us to assign a value to the variable. However what we can do is give the variable a default value so on creation it is populated.

Lets try that out using out HelloWorld function.

CREATE FUNCTION HelloWorld() RETURNS VARCHAR(20)
BEGIN
DECLARE output_text VARCHAR(20) DEFAULT 'HelloWorld';
RETURN output_text;
END

We can also declare more than one variable of the same type at the same time. 

DECLARE output_text, name, department VARCHAR;

Parameters

Parameters are another common programming tool. Using parameters we can pass data into and in the case of procedures out of our program units. Lets extend our HelloWorld function to accept a parameter add this to our ‘hello world’ variable and return the result. 

CREATE FUNCTION HelloWorld(p_inparam VARCHAR(20)) RETURNS
VARCHAR(20)
BEGIN
DECLARE output_text VARCHAR(20);
SET output_text = p_inparam;
RETURN output_text;
END

This time when we run the function we need to supply the parameter like so 

select HelloWorld(' Hello World') //

Running Procedures  

Because we know functions only ever return one value its easy for us to run them using the select command within MySQL, however procedures do not need to return a value or can in fact return more than one. Lets see how to create and run a procedure, accept the values it returns and how we can display those values. 

Creating a procedure is just as simple as creating a function. Here we are going to create a procedure which will return a simple string. 

use pers
DROP PROCEDURE IF EXISTS HelloWorld
CREATE procedure HelloWorld(out p_text VARCHAR(30))
BEGIN
set p_text = 'Hello World';
END

So now we need to run the procedure. Because MySQL doesn’t know if the procedure will return a value we need to call it in a different way. We can pass values in and out of the procedure using user variables (@). The syntax to run the procedure is as follows.

CALL HelloWorld(@out)

Then to see the result.

Select @out //
+-------------+
| @out        |
+-------------+
| Hello World |
+-------------+
1 row in set (0.00 sec)

So as you can see, it’s easy to call both functions and procedures with MySQL.

Select Into  

So far our function or procedures are not offering us a great deal so lets use another feature of stored procedures called select into. Rather than hard coding the variable value or passing in as a parameter as we have done so far we can select the value from a table. 

Note: Currently functions are restricted so that they cannot access tables. This also applies to some set and select statements. In the MySQL documentation it says this restriction will be lifted soon. 

So lets try and use the select into functionality in a procedure. We will use the database tables we have set up to do this. A useful procedure would be if we could request a department description (we could do this easily using a straight SQL statement but that would bring a quick end to this document). 

CREATE procedure GetDept(out p_dept_description VARCHAR(30))
BEGIN
SELECT description INTO p_dept_description FROM DEPT LIMIT 1;
END

Now lets call the procedure and see what the result was. 

Select @dept //
+-------+
| @dept |
+-------+
| Sales |
+-------+
1 row in set (0.00 sec)

In this simple example we are simply returning the first department, what would be much more useful would be the ability to pass in a department id and get the corresponding description. So lets do that now. 

CREATE procedure GetDept(out p_dept_description VARCHAR(30), in p_dept_id
VARCHAR(5))
BEGIN
SELECT description INTO p_dept_description FROM DEPT WHERE dept_id = p_dept_id;
END
call GetDept(@out,'IT') //
mysql> select @out //
+---------------------------+
| @out                      |
+---------------------------+
| Information Technology    |
+---------------------------+
1 row in set (0.00 sec)

We’re now starting to see the power of stored procedures within MySQL and how they might be more useful than standard SQL statements. It’s true that we could have achieved the same thing using an SQL select statement, but using stored procedures allows us to protect the database and increase the level of security. Rather than giving direct access to the tables we can place procedures in between to do a number of tasks and maintain the data we store in tables. 

This concludes the first part of this introductory look at stored procedures within MySQL, but there is still more to learn and in part 2 we will look at how we can expand on the functionality we have seen so far.  hand, allows any element to float, from images to paragraphs tolists. In CSS, this behavior is accomplished using the propertyfloat.

float

For example, to float an image to the right, you could use thismarkup:

<IMG SRC="b5.gif" style="float: right;" alt="section b5">

Figure 9-2

Figure 9-2. Positioning an element within its containing block

Note that the positioned element has padding, a double border, and a slightly different background color. In Figure 9-2, it has no margins, but if it did, they would create blank space between the borders and the offset edges. This would make the positioned element appear as though it did not completely fill the lower-right quarter of the containing block. In truth, it would do so, but this would not be immediately apparent to the eye. In otherunder CSS2 but not CSS1, will come out as a really awful shade ofyellow-green. This is not correct behavior, but you'll need toremember it as you write your styles.