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.
We will come back to the tables later so now we will turn our attention to creating a simple function.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’);
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
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[,..
[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'; are typically preceded by a marker, such as a small dot or a number. This marker isn't actually part of the list-item's content area, so you get effects like those illustrated in Figure 8-25.
In CSS1, very little is said about the placement and effects of this marker with regard to the layout of a document. CSS2 introduces properties specifically designed to address this issue, such as
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.
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 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') //
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.
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.
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. writing: Opera 3.6 for Windows, Internet Explorer 4.5 and 5 forMacintosh, and Internet Explorer 5 for Windows.