A stored routine is a set of SQL statements that can be stored in the server. Stored routines are not generally accepted. They have some advantages but also several disadvantages. Stored routines are typically used in data validation or access control.
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.
[dropcap type=”1″]S[/dropcap]tored 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.
Stored procedures may be useful in situations, where there are many client applications written in different languages or work on different platforms, but need to perform the same database operations. They can lead to some performance gains. Stored routines are stored in the server and so the network load decreases. In some database systems the stored routines can be precompiled, which increases the performance. If you change some logic on the database, it is automatically ready for all possible clients. When we change some logic on the client side, this must be done in all possible clients.
On the other hand, stored routines have some drawbacks. Stored routines violate the principal design pattern, where business logic, data and presentation are separated in specific tiers. Stored routines mangle business logic with data. Stored routines are more difficult to debug and test. The application that has a lot of business logic in stored routines is less scalable. Moreover, there is no version control system for stored routines. Finally, stored routines are implemented differently in various database systems. This makes potential migration between database systems more difficult.