(casual music)
- [Emma] Now we're going to look at functions.
You may be familiar with functions already
from Visual Basic or JavaScript,
but if you've never used a programming language,
you've probably used a function in Excel.
So if you've typed in something like =SUM
and then you've given a range of cells and you've hit Enter,
you've been using a function.
And Excel will go off and count up
all the values in the range of cells
and return you a value.
That's what functions do typically.
You give them an input and they calculate
some sort of output along rules that you specify
and return you with usually a number or a text or date.
So what they don't do is they don't run off and edit data.
They don't delete rows of data.
You give them an input and they calculate an output for you.
You can see from the breadcrumb bar that we are
at the top level of the sakila database,
and we have Functions, Procedures, Tables, and Views.
And this time we're looking at functions,
and the sakila database
has three for us already defined.
We're going to look at inventory_held_by_customer.
So I've just clicked on it there.
And if we scroll down, there's a definition.
Now we could have returned this definition programmatically
in my SQL by saying show create function
and then using the function name.
But this is just as easy to do.
And just a reminder that what follows
is specific to MySQL.
So I've just copied that and pasted it into Atom
so that we can look at it a bit more easily.
So first of all, we have log in data, root @ local host,
which we can largely ignore,
and then we have FUNCTION and the function name
inventory_held_by_customer.
After inventory_held_by_customer, we have brackets
p_inventory_id INT, and INT means integer.
p_inventory_id is a variable name,
so what does it mean to have that in brackets
after the name of the function?
When we use this function in SQL,
we're going to use it much like this
SELECT inventory_held_by_customer.
And then we're going to supply a number in the brackets
and that's the p_inventory_id,
and the number needs to be an integer.
And then this line says RETURNS int.
So this function is expecting an integer as an input,
and it's going to give us an integer as an output as well.
That's the whole purpose.
So let's get rid of that.
You can see that we have BEGIN and END declarations
to this function,
and after that, we have a variable declaration,
and then an EXIT HANDLER at the top.
Now this might look a bit odd because we're declaring
a variable using the DECLARE statement.
And we've just seen that we can use the SET statement
with an @ sign to declare a variable like so.
So why are these two things different?
They're both variables in my SQL,
but they're different kinds.
Where you see the SET keyword followed by the @ sign,
you're using a user defined variable.
And these are sorts of
longer lasting variables, if you like,
and let me tell you a bit more about that.
If you tried to reference v_customer_id
after the END keyword,
MySQL wouldn't know what you were talking about.
Where you have a variable name
and the DECLARE keyword with no @ sign,
you're using a local variable.
And these cease to exist after the END keyword.
If instead we wanted to make reference down here
to the user defined keyword, that would be fine.
They're longer-lasting.
They exist outside of the BEGIN and the END declarations.
But in a function like this,
it's nice and tidy to have those variables
disappear after the END statement.
So we don't need to use these sort of
larger user defined ones.
We can use local variables.
In our second declaration statement, it says
DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL;
which looks like really bad grammar.
And what it's saying is if the query finds no rows at all,
then return a NULL.
So show on screen the word NULL.
After that, we have what looks like
a regular SELECT statement,
except that this one has an INTO keyword.
And what it's saying is select the customer ID
and pop it into a variable called v_customer_id.
v_customer_id is what is going to be returned
as in shown on screen,
and you can see that further down
the query next to the END declaration
it says RETURN v_customer_id.
So that is what we're going to see on screen.
So what's this SELECT statement doing?
It says select the customer ID from the rental table
where the return date is NULL,
that is to say the DVD is still on loan,
and then the inventory_id matches
the number that's been given in the parameters.
So let's run this and make a bit more sense
of what's going on.
Now I haven't just made that number up.
I've had a look through the tables to try and work out
a sensible inventory_id to use.
So SELECT inventory_held_by_customer 2047 returns 155.
So have a quick look back at our query.
We have supplied an inventory_id,
that is to say a piece of stock or DVD
with the number 2047 in the database.
And this query is returning us the customer ID
of the person who still has that on loan.
What if we tried a different number?
And again, here's one I prepared earlier.
We can try 367.
You can see we returned a NULL.
So now that could be a couple of different things.
It may be that there's no customer
has ever hired at DVD 367,
or it may be no customer has it currently on loan.
Let's have a quick look at what's going on.
Select everything from the rental table
where the inventory_id is 367,
and we get five rows.
So this DVD has been rented before,
but if we look, the return date is filled in in every case,
which means all of those DVDs have been returned.
There's no outstanding item.
And that's why we got the NULL when we ran our function.
Our DECLARE EXIT HANDLER came into play.
Now on its own, this function is a little bit unhelpful.
Knowing that customer 155 has a movie that's still on loan
is not really human readable.
But functions can be useful as part of a query,
and that's really how they're used.
So let's say you're chasing up on an item of stock
and you want the email address for the customer
who still has a movie on loan.
Then we could say
SELECT email from the customer table
WHERE
inventory_held_by_customer
2047
is customer_id
AND
inventory_held_by_customer
2047
IS NOT NULL.
So this time we're using our function in a query
to get the email address of anybody who still has
an item of stock on loan,
and we know that that was customer 155.
And there we have the email
of the offending customer as it were.
This tells us two things at once then.
The customer does indeed have the movie
still in her possession,
and there's the email address to contact her.
Using a function here is a substitute really
for a subquery or joining tables together.
It can be a lot quicker.
And for common queries, this is a way of storing them
so that you and other people can use them easily.
(casual music)