Join Bill Weinman for an in-depth discussion in this video Understanding MySQL stored routines, part of MySQL Essential Training.
- View Offline
A stored routine is a set of SQL statements that are stored on the database server and can be used by any client with permission to use them. This provides a number of benefits. Database operations are normalized so various applications will operate uniformly, even when written in different languages and operating on different platforms. Stored routines are easy to maintain, because they're all in one place rather than distributed among different applications. Traffic is reduced between the client and server, because data is processed on the server.
And security is enhanced by allowing clients to run with reduced permissions while still being able to perform necessary database operations. There are also some disadvantages. Migration to a different server platform can be difficult as stored routines tend to use a lot of platform specific features and codes. And stored procedures can be difficult to debug and maintain. There are two different kinds of stored routines. Stored functions return a value, and are used in the context of an expression.
Stored procedures are called separately, using the call statement, and may return result sets or set variables. Stored functions are created with the create function statement. They are used as you would use a built-in function. Stored functions are only available in a scalar context. They are not available in an aggregate context. Stored procedures are created with the create procedure statement. A procedure is called with the call statement, and it's used like a complete query.
A procedure can return a result set like a select statement or it can be used with output parameters to return scalar values by reference. Stored routines can be a valuable tool for improving the performance, maintainability and security of your database systems. In the rest of this chapter, I'll show you some examples of both stored functions and stored procedures.
- Writing queries
- Creating and updating databases and tables
- Using MySQL built-in functions
- Sorting and filtering data
- Updating tables with triggers
- Working with subselects and views
- Creating and using a stored function