Stored routines are a relatively new addition to the MySQL server, and aren’t generally used by a lot of people. Although they can greatly simplify application code and ensure that all parts of a system behave in a consistent manner, creating and using stored routines can still be a daunting prospect to those who have never used them before. In this series of articles I will introduce stored routines, how to create (and use) them and the reasons for using them.

The MySQL server implementation of stored routines is still under developement, so things may change slightly in the future. There is a standard for stored routines that the MySQL server adheres to (SQL:2003) so the changes shouldn’t be too significant

What are stored routines?

Stored routines are a collection of statements that are executed by the MySQL server with a single command. Programmers should already be familiar with the concept of subroutines – stored routines can simply (and almost totally correctly) be thought of as MySQL subroutines.

“Stored routines” refers to both stored procedures and stored functions (and also things like triggers and views, but I won’t cover them here)

What’s the difference between a stored procedure and a stored function?

Functions return a value. Procedures don’t. (although both can modify a parameter passed to them that has been marked as an output parameter)
Functions can be used within another SQL statement. Procedures need to be executed with the ‘CALL’ command.
Other than that, they are the same – they’re created in (almost) the same way, they are stored internally the same way, and the same access rights are required (no distinction is made between a procedure or a function).

Why use stored routines?

Stored routines have several advantages: They can make application code simpler Instead of having blocks of SQL code within the application code, stored routines could be used instead. Not only does this make the code smaller and easier to read, if stored routines are given sensible names it allows programmers with no understanding of SQL to retrieve data from the database without too many problems (they should already understand the concept of subroutines) They can improve application consistency The use of modular programming is now pretty much the norm (and the concept is extended further by Object Orientated Programming), which has greatly improved the consistency of programs – instead of having duplicated blocks of code, common functions are put into seperate modules and called when necessary by the main application code. Stored routines can take this one step further – whenever database access is required, a stored routine can ensure that the application or module code does the same thing wherever necessary. This can be vital for financial aspects of an application – the profit margin of a particular product, for example, should be the same when displayed by the company buyers (so they can tell if a purchasing from supplier is actually worth it), the marketing department (so they can tell exactly how much they can drop the sale price for and still make a profit – or even how much of a loss for loss-leader products) and the accounting department (so they can produce accurate financial reports).
Although modular coding can acheive this, it still requires all parts of an application to use the code modules to get the data. For ad-hoc applications or queries, however, this may not be possible, and so the SQL would need to be copied out of the module. If the way a particular value is calculated changes, this change will also need to be applied across all instances of the SQL, which may not happen properly (especially for ad-hoc applications that are developed/maintained outside of the main development team). With a stored routine, the change only needs to be applied to one place – the database – and everything that uses the routine will automatically get the result of the new method. They can improve security Stored routines don’t necessarily have to execute with the privileges of the person who runs them (the ‘invoker’) – they can run with the privileges of the account who created it (the ‘definer’). This means that MySQL users can be given controlled access to data that they may normally not have – if a stored routines runs as the definer and not the invoker, it will have a completely different set of access rights. Setting stored routines to run as the definer instead of the invoker is potentially another layer in a defence-through-depth security approach. They can help improve application performance Many programmers, whilst competent working with databases, are not DBAs – the database designs and SQL code they write may work, but may not be as efficient as possible. The use of stored routines allows the application programmers to pass off all database work to people who are extremely familiar with getting the best database performance. A complete reliance on stored procedures (rather than accessing the database tables) would allow the DBA team to completely redesign a database (for performance reasons) with no visible changes to the programmers. They can help fulfil legal or contractual agreements If an audit trail of data access/modification is required, either by law or by a contract agreement, the use of stored routines can automatically build up this data. Whenever data is accessed (or modified) the stored routine can log the details of the request before performing the required function. This feature may be particularly interesting to applications that handle credit card data, which needs to maintain audit trails to comply with the Payment Card Industry Data Security Standard (PCI-DSS).

What’s the difference between a stored routine and a User Defined Function? (UDF)

A stored routine is create and executed within the MySQL server. It can only use the features and functions available to the server.

UDFs are external pieces of code that are called by the MySQL server. They are programmed and built outside of the server, and are not restricted by the features and functions available to the server.

For example, if you wanted to have a single function to create and insert/update tables (like a new application account being created), it would be possible (and easier!) to create a stored procedure to do this – all of the things you need are already available to the MySQL server.

If, however, you wanted a routine that would also make a HTTP request (maybe to PUT a file somewhere) you would have to create a UDF – the MySQL server cannot make HTTP requests.

Hopefully by now you will understand what stored routines are a bit better, and begin to see how they can be used to improve your own projects. Next time I’ll cover the basics of creating and using stored routines.