- [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)