Placeholder Image

Subtitles section Play video

  • (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.