Sunday 05th of July 2015 08:01:10 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. 

App server developers are not restricted to using HTTP, they can transmit and recieve XML information using simple remote CORBA objects and RMI objects. The key is that by using XML, it makes these remote services or objects easier to build. And, by sticking with XML, any one of these technologies can be used in your design of your app server. You can use whatever technology is most appropriate to getting the job done, knowing that all the information flows as XML and can be processed by any part of the system. The reason Java object serialization did not achieve this is because it encodes object data to a binary format that is dependent on too many things (like the JVM version, and the existence of classes when things are deserialized, etc). XML is not limited by any of these restrictions (or problems), which makes it much easier to create systems that allow XML information to flow between different subsystems. Also by relying only on the data, large portions of the system can be replaced with better or different implementations for future-readiness.

App servers traditionally give their client apps access to information in remote databases, remote file systems, remote object repositories, remote web resources, and even other app servers. All these information sources don't even need to reside on the machine that hosts the app server. These remote resources may be on other machines on the Intranet or the Internet. Using Java and XML, RMI, JDBC, CORBA, JNDI, Servlet and Swing, you can create app servers that can integrate all kinds of remote and local information resources, and client apps that allow you to remotely or locally access this information from the app server.

In the future, with publicly available DTDs that are standardized for each vertical industry, XML based app servers will become very popular. Also when XML schema repositories become available and widely used, app servers will be able to take on a new role and provide application services that are not offered now. Companies will need to share information with other companies in related fields, and each company might have a different software system in which all their data is housed. By agreeing upon a set of DTDs or schemas (encoded in XML), these companies can exchange information with each other regardless of what systems they are using to store this information. If their app servers can exchange XML documents (based on some shared DTD or schema), then these disparate app servers can understand each other and share information. One of the uses for XML foreseen by the W3C is just this, vertical industries (like insurance and health care) creating sets of DTDs and schemas that all companies in the industry agree upon. Then these companies' app servers can talk to each other using some popular protocol (like HTTP or CORBA/IIOP) to exchange information between each other. This has the potential to save a lot of time and money in the daily business operations of these companies.

Web-based Applications

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. 

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.  use a file's extension to decide which MIME type to use whensending the file, it obviously becomes important to have a friendlyserver configuration.

If an external style sheet is sent using the wrong MIME type, thestyle sheet gets mangled into something unusable. If you find thatyou're having this problem, then you'll need to contactyour ISP and explain the problem. If they refuse to fix it, tryexplaining to them that IANA (the Internet Assigned Numbers There is a lot of hype surrounding XML, and a lot of hype surrounding Java. Together these technologies propose to solve many of the most common (and persistent) general computing problems that have been around for the last 20 years. XML and Java are not revolutionary in the approach to solving these problems of interoperability of code and data across and within platform and application boundaries. Rather, XML and Java provide solutions to these problems by using the most successful strategies and techniques that have been honed and refined over the last 20 years of computing.

In the following paragraphs, I will highlight some of the most basic and important advantages that XML and Java provide to almost any system that uses them properly. This is by no means a comprehensive list of benefits, but items in this list should appear across just about any use of XML and Java technologies.

I will take a break from my normal pragmatic approach to getting you (the programmer) started with using XML and Java and just talk about the high level (design level) benefits of this wonderful combination. A good design is important to a good implementation for any system.

XML is structured

When you create your data using an XML editor (that you can write), you can not only input the content of your data, but also define the structural relationships that exist inside your data. By allowing you to define your own tags and create the proper structural relationships in your information (with a DTD), you can use any XML parser to check the validity and integrity of the data stored in your XML documents. This makes it very easy to validate the structure and content of your information when you use XML. Without XML, you could also provide this validation feature at the expense of developing the code to this yourself. XML is a great time saver because most of the features that are available in XML are used by most programmers when working on most projects.

A related topic is the subject of backgrounds and their relationshipto floated elements that occur earlier in thedocument, which was also discussed in the previous chapter, as hasbeen illustrated in Figure 8-41.

Figure 8-41

Figure 8-41. Backgrounds "slide under" floated elements

Because the floated element is both within and without the flow, thissort of thing is bound to happen. What's going on? Well, thecontent of the paragraphs is being "displaced" by thefloated element. However, each paragraph's element width isrecognize the file as containing a style sheet unless it actuallyends with .css, even if youdo include the correct TYPEof text/css in the LINKelement. So make sure you name your style sheets appropriately.

1.4.1.1. LINK attributes

For the rest of the LINK