Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
A stored function is defined with the create function statement. For this exercise we'll be using the album data base. MySQL provides a function for converting seconds to time, but I usually need to reformat it as a string anyway, so I tend to use a stored function like this one. Create function, and we'll call this track_len. And it takes one parameter, seconds and it's an integer. It returns a VARCHAR and it's not a very long one so 16 bytes will do.
And it's deterministic, what deterministic means is that this function will always return the same value for the same input. And tagging it as deterministic like this, helps the optimizer in MySQL to create more efficient code. The default is not deterministic, so you want to put this in if your code is going to always return the same value for the same input, and this certainly will. Return concat with separator and uses a colon for separator and starts with seconds DIV 60.
So that's the minutes part, and then we're going to use LPAD, which is a string function that pads the left side of a string. And we'll have a pad with zeros to a fixed length of two so it's always got that leading zero. If it's just one digit second, if it's two digit seconds, it doesn't need the leading zero, so LPAD does this beautifully. And we'll use seconds MOD 60, which is just the seconds part without the minutes. It's the remainder after the division by 60.
And it's two in length, and it pads with a zero. And we need two parenthesis here, one for the LPAD and one for the CONCAT WS. And a semicolon. So we've now created our function and all we need to do to use it is say select title track or track_len. Use our function here. Duration from track. Like that. And when I press Go, we've now used this function.
There it is. And you see that the minutes are not padded, but the seconds are. We have 08 seconds there, so it's got that leading zero, right? So that time has been converted to a string. And now that the function is created. Every time we use it, we will get that same result. And there we are. So I like the way this formats the time, with the leading zeros for the seconds, but not for the minutes. The beauty of this approach though is that if you don't like it, you can easily change it and you only need to change it in one place.
wherever this function is used, it will be updated when you change it. For the next example, I'm going to, just go ahead and grab it here from the exercise files. And this is the chapter nine exercise file. I'm just copying, and pasting, this second select statement here. We'll paste that in, and we'll see. This is a nice listing of all the tracks for all the albums in the database and we have this nice little JOIN query and you notice that I'm using my track_len function there for the length column.
And that's just really convenient and easy to do. Now, stored functions are not used in aggregate context. You cannot use this. As an aggregate function. But you can use it with the result of an aggregate function. So, I'm going to go and grab this next example here from the exercise file. And I'll paste that in and press go. And here we have the total length of each of the albums in the database. And the way that's working, is this is, you see we have a group eye, so it is an aggregate context.
But we have the sum function here and track link is simply processing the result of sum. And so track link is technically not being used in an aggregate context, although. The value that it is processing is an aggregate value. So, that works just fine. And that's a great way to get around this limitation. So, I'm going to leave this stored function in place. We're going to use it in the next lesson. A stored function is a very powerful and convenient tool. It's easy to define and to use.
And allows you increased control over the functionality of your systems.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 101634 Viewers
61 Video lessons · 88389 Viewers
71 Video lessons · 72227 Viewers
56 Video lessons · 103975 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.
Your file was successfully uploaded.