Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
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.
Get unlimited access to all courses for just $25/month.Become a member
61 Video lessons · 100173 Viewers
56 Video lessons · 113180 Viewers
71 Video lessons · 82048 Viewers
131 Video lessons · 39370 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.