How to Create Stored Procedures and Functions in a MySQL Database

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.

1. 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.

2. 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.

Read more: "MySQL Stored Procedures and Functions: How to Create Stored Procedures and Functions in a MySQL Database" -
http://database-programming.suite101.com/article.cfm/mysql_stored_procedures_and_functions#ixzz0DVzBNS5i&A