Why and When Use MySQL Stored Routines

As your SQL business logic becomes more complex, you might find yourself repeatedly writing blocks of SQL statements to perform the same database operation at the application level—for example, inserting a set of linked records or performing calculations on a particular result set. In these situations, it usually makes sense to turn this block of SQL code into a reusable routine, which resides on the database server (rather than in the application) so that it can be managed independently and invoked as needed from different modules in your application.

Packaging SQL statements into server-side routines has four important advantages.

• A stored routine is held on the database server, rather than in the application. For applications based on a client-server architecture, calling a stored routine is faster and requires less network bandwidth than transmitting an entire series of SQL statements and taking decisions on the result sets. Stored routines also reduce code duplication by allowing developers to extract commonly used SQL operations into a single component. The end result is that application code becomes smaller, more efficient, and easier to read.
• A stored routine is created once but used many times, often from more than one program. If the routine changes, the changes are implemented in one spot (the routine definition) while the routine invocations remain untouched. This fact can significantly simplify code maintenance and upgrades. Debugging and testing an application also becomes easier, as errors can be traced and corrected with minimal impact to the application code.
•  Implementing database operations as stored routines can improve application security, because application modules can be denied access to particular tables and only granted access to the routines that manipulate those tables. This not only ensures that an application only sees the data it needs, but also ensures consistent implementation of specific tasks or submodules across the application (because all application modules will make use of the same stored routines rather than attempting to directly manipulate the base tables).
• Using stored routines encourages abstract thinking, because packaging SQL operations into a stored routine is nothing more or less than understanding how a specific task may be encapsulated into a generic component. In this sense, using stored routines encourages the creation of more robust and extensible application architecture.

It’s worth noting also that in the MySQL world, the term “stored routines” is used generically to refer to two different animals: stored procedures and stored functions. While both types of routines contain SQL statements, MySQL imposes several key restrictions on stored functions that are not applicable to stored procedures, as follows:

• Stored functions cannot use SQL statements that return result sets.
• Stored functions cannot use SQL statements that perform transactional commits or rollbacks.
• Stored functions cannot call themselves recursively.
• Stored functions must produce a return value.

NOTE

Stored routines, although useful, are yet to be fully optimized in MySQL 5.x. Therefore, as much as possible, you should avoid using complex stored routines in MySQL, as they can significantly increase overhead. The lack of a fully optimized cache or debugging tools for stored routines are also a hindrance to users and developers.