MySQL Stored Procedures and Functions

How to Create Stored Procedures and Functions in a MySQL Database

Aug 29, 2008

MySQL stored procedures and functions, Mark Alexander Bain

Oracle, unlike MySQL, has stored procedures and functions available for the database developer, giving Oracle a major advantage. That's all changed with MySQL 5.

Over the past few years one major advantage that some databases, such as Oracle and Microsoft SQL Server, have had over MySQL is their ability to use stored functions and stored procedures. Well, that was before MySQL 5; with MySQL 5 a database developer can start adding in their own bespoke functionality.

What are Stored Procedures and Stored Functions?

Any programmers reading this should already be comfortable with the concept of subroutines and functions: encapsulated pieces of code that can be called by programs - often used to carry out repetative or complicated tasks.

Subroutines and functions can be made available to a single program or many; and that, of course, is what stored procedures and stored functions are - they are procedures (or subroutines) and functions stored in the database.

What's the Difference Between a Stored Procedure and a Stored Function?

The difference between a stored procedures and stored functions is the same as the difference between a subroutine and a function:

a stored procedure runs some code
a stored function runs some code and then returns a result

Why Use Stored Procedures and Stored Functions?

The real advantage to using stored procedures and stored functions is that they provide functionality which is platform and application independant. For example, a team of developers may provide:

a Visual Basic application on Windows
a Gambas application on Linux
a PHP application on a web server

Without stored procedures and stored functions then the functionality would have to be developed independently for each application, but with stored procedures and stored functions the functionality only has to be developed once.

Creating MySQL Stored Procedures

A stored procedure is the same as a subroutine in that it cannot directly return a result, however it can receive variables that can be modified by the procedure; these variables are defined as:

in - the variable can only be used as an input to the procedure
out - the variable can only be used as an output from the procedure
inout - this is both an input to, and an output from, the procedure

Procedures are always declared in the same way:

define the procedure name
define the procedure inputs and outputs
define the the body of the procedure (enclosed within a BEGIN ... END statement)

One other (rather imporant) thing to bear in mind is that semicolons are used as part of the definition of the procedure. For this reason the end of line delimiter must be redefined to something that won't be used in the definition. For example:

delimiter //

create procedure circle_area (in r double, out a double)

begin

set a = r * r * pi();

end

delimiter ;

Running MySQL Stored Procedures

A MySQL stored procedure is run by using the call method:

call circle_area(22, @a);

select @a;

In this example 1520.5308443375 would be displayed on the screen.

Creating MySQL Stored Functions

Unlike stored procedures stored functions always return a result, they will also be one of two types:

not deterministic - may produce different results for the same inputs (for instance random numbers or dates)
deterministic - will always produce the same result for any given inputs

Like procedures, all functions are created the same way:

define the function name
declare any inputs
define the data type to be returned by the function
state whether or not the function is deterministic
define the body of the function (again within a BEGIN ... END statement)

For example:

delimiter //

create function circumference (r double) returns double

deterministic

begin

declare c double;

set c = 2 * r * pi();

return c;

end

delimiter ;

Running MySQL Stored Functions

Unlike stored procedures stored functions are used as part of a select statement:

select circumference(22);

In this case (for anyone that's interested) the result would be 138.23007675795.

Conclusion

Stored procedures and stored functions are important tools for any database developer, and thankfully, those tools are now available to MySQL users - provided, of course, that they're using MySQL 5.

The copyright of the article MySQL Stored Procedures and Functions in Database Programming is owned by Mark Alexander Bain. Permission to republish MySQL Stored Procedures and Functions in print or online must be granted by the author in writing.

MySQL stored procedures and functions

Create procedures and functions in a batch file

Print Article

View All Articles

How to subscribe to feeds

Comments

Sep 25, 2008 12:36 PM

Guest :

Don't you think you ought to make it clear whether or not you need a text editor to type this procedure circumference or whether you just type the code line by line at the MySQL command line prompt? Anything that is not done well is a reflection on what the author looks like

Sep 26, 2008 2:51 AM

Mark Alexander Bain :

That's a good point, but I would have thought that anyone considering creating stored procedures or functions will already understand how to create objects on a database. However, if that's not the case then you may find this article useful: http://computerprogramming.suite101.com/article.cfm/using_batch_files_to_cr eate_mysql_databases.

Oct 3, 2008 10:20 PM

Guest :

Thank You so much for this intuitive description. It is so easy to comprehend.

Jul 21, 2009 10:35 AM

Guest :

Thanks for the info. Very well done indeed. P.S. Don't pay attention to the idiot who wrote the first post.

Aug 6, 2009 2:22 AM

Guest :

congrats for the post, I liked it . I've been using Oracle and I didn't know it was so easy to create procedures and functions with MySQL.

5 Comments