Stored procedures and functions offer several benefits for application development, deployment, and operation:
More flexible SQL syntax. Stored routines can be written using extensions to SQL syntax, such as compound statements and flow-control constructs, that make it easier to express complex logic.
Error handling capabilities. A stored routine can create error handlers to be used when exceptional conditions arise. The occurrence of an error need not cause termination of the routine but can be handled appropriately.
Standards compliance. The MySQL implementation of stored routines conforms to standard SQL syntax. Routines written for MySQL should be reasonably portable to other database servers that also follow standard SQL syntax.
Note: Although the implementation is based on standard SQL, it is not yet complete as of this writing and there are limitations. For example, cursors are read-only and cannot be used to modify tables. Cursors also advance through a result set a row at a time; that is, they are not scrollable. Expect these restrictions to be removed over time.
Code packaging and encapsulation. A routine allows the code that performs an operation to be stored once on the server and accessed from multiple applications. The code need not be included within multiple applications. This reduces the potential for variation in how different applications perform the operation.
Less "re-invention of the wheel." A collection of stored routines acts as a library of solutions to problems. Developers can use them "off the shelf " rather than re-implementing the code from scratch themselves. Stored routines also facilitate sharing of knowledge and experience. A skilled SQL developer who solves a difficult problem can implement the solution in a stored routine to be used by developers with less expertise.
Separation of logic. Factoring out the logic of specific application operations into stored routines reduces the complexity of an application's own logic and makes it easier to understand.
Ease of maintenance. A single copy of a routine is easier to maintain than a copy embedded within each application. Upgrading applications is easier if they all use a routine in common, because it is necessary to upgrade only the routine, not every application that uses it.
Reduction in network bandwidth requirements. Consider a multiple-statement operation performed by a client without the use of a stored routine: Each statement and its result crosses the network, even those that calculate intermediate results. If the operation is performed within a stored routine instead, intermediate statements and results are processed entirely on the server side and do not cross the network. This improves performance and results in less contention for resources, particularly for busy or low-bandwidth networks. (The potential benefit of this factor must be weighed against the number of clients and the amount of client processing that is moved onto the server through the use of stored routines.)
Server upgrades benefit clients. Upgrades to the server host improve the performance of stored routines that execute on that host. This improves performance for client applications that use the routines even though the client machines are not upgraded.
Better security. A routine can be written to access sensitive data on the definer's behalf for the invoker, but not return anything that the invoker should not see. A routine can also be used to modify tables in a safe way, without giving users direct access to the tables. This prevents them from making possibly unsafe changes themselves.
