Explains functions in MySQL, walking through a stored function and running it as an example
- [Instructor] If you've ever used a formula in Excel like sum or vlookup, then you've been using a function. Now those functions are defined by Excel when you install the application, but in sql and most languages you can actually create your own formulae, and then use them. The sakilla database comes with three, get customer balance, inventory held by customer, and inventory in stock. But you could easily create new functions if you wished. So a function, just like sum in Excel, doesn't edit or delete your data, it doesn't perform an action.
Generally speaking, it takes an input, and gives you an output. So it normally calculates something. And we're going to have a look at inventory, held by customer, function. And that terminology there, show, create, function, is specific to my sql, as is all that follows, but you can do functions in most versions. So the create function statement, the bit that we're interested in, really is this section, down to end.
And I've popped that into atom, so that we can take a bit of a closer look at it. First of all we have a login name and a server name. Then we define the function name, and these brackets here tell us that this function takes a parameter, a single parameter, which is going to be an integer value, and it's going to get popped in to a variable called p inventory id, which is then used further down in the function. And the final thing this line does is it tells us that the output of the function is also an integer.
Now, the main body of the function is between the begin and end declarations. And as with many queries, we can help ourselves by splitting this one into three. The top section is a couple of declarations. The first one declares a variable, called v customer id, and tells us that this is an integer. The second declaration declares an exit handler, which is basically what the system should do if something goes wrong. In this case the exit handler is triggered if no rows are found.
And if no rows are found, the system will return null. Then we have what looks like a fairly regular select statement, and finally, the function says, at the end of all of that, the thing I've calculated, is v customer id, and that's what should be returned. And we know from up here that that will be an integer. So let's have a closer look at this query. It looks very much like a normal select statement. The only difference is instead of just selecting, we're selecting into, and we're selecting into a variable called v customer id, which is what is then returned by the formula.
So to look at the rest of the query we're saying select customer id from the rental table, where the return date is null. In other words, where the movie is still on loan, it hasn't been given back to the shop yet, and where the inventory id is the parameter that we've been given. Now functions can be used in line, in sql queries. So let's have a look at how it works. Select, inventory held by customer, and I'm putting an id in that I found earlier.
So when we run that function, we're returned a 155. And overall this is saying, for an item of stock with an id 2047, it is currently still on loan and hasn't been returned, and it's in the possession of customer with an id of 155. If we pick a different item of stock and run that, we get a null. And that's not because there is no item of stock with an id of 367. There is I've checked.
It's because the return date is not null, and therefore this query has returned zero rows, and when that happens, a null is what's returned. So that's why we got a null there. Now on it's own this function is a bit unhelpful. Knowing that customer 155 has a movie still on loan isn't what you'd call human readable. But where it becomes useful is as part of a broader function, a broader query. So let's say you're chasing up an item of stock and you want the email address of the customer you think has still got possession of it.
You could say, select email, from customer, where, inventory held by customer 2047 equals customer id. Now the reason we know that we can set this function legitimately to be equal to customer id, is because the function is returning us something from the customer id field.
So we're matching apples with apples there. And then if we run that we see we're given an email address to contact the offending customer, as it were. Now this tells you two things at once actually, not just telling you the customer's email address, it's also confirming that she does, in fact, have the movie on loan still. And if we were to run 367, which we know is no longer on loan, although there will have been a customer who's had that item on loan, it returns as nothing at all.
Using a function can be a substitute for using a sub-query, or a nested select. And it's a really handy way of storing queries and calculations in your database.
Join Emma Saunders as she shows you how to design and write simple SQL queries for data reporting and analysis. Review the different types of SQL, and then learn how to filter, group, and sort data, using built-in SQL functions to format or calculate results. Learn a bit about data types and database design. Discover how to perform more complex queries, such as joining data together from different database tables. Last but not least, Emma shows how to save your queries as views, so you can run them again and again.
- Using different versions of SQL
- Retrieving data with SELECT statements
- Filtering and sorting your results
- Transforming results with built-in SQL functions
- Grouping SQL results
- Merging data from multiple tables
- Identifying data types, and how to make sense of your database design
- Saving SQL queries