- [Emma] Now we're going to look at functions.

- [艾瑪]現在我們要看一下函數。

You may be familiar with functions already

你可能已經熟悉了這些功能

from Visual Basic or JavaScript,

從Visual Basic或JavaScript。

but if you've never used a programming language,

但如果你從來沒有使用過編程語言。

you've probably used a function in Excel.

你可能已經在Excel中使用了一個函數。

So if you've typed in something like =SUM

所以，如果你輸入了像=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

而Excel會去計算一下

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,

在sakila數據庫的最高層。

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.

我們要看的是 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

在我的SQL中說，顯示創建函數

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.

是針對MySQL的。

So I've just copied that and pasted it into Atom

是以，我剛剛複製了這些內容，並將其粘貼到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

然後我們有FUNCTION和函數名稱

inventory_held_by_customer.

客戶持有的庫存。

After inventory_held_by_customer, we have brackets

在 inventory_held_by_customer 之後，我們有括號

p_inventory_id INT, and INT means integer.

p_inventory_id INT，INT表示整數。

p_inventory_id is a variable name,

p_inventory_id是一個變量名稱。

so what does it mean to have that in brackets

那麼，括號裡的這句話是什麼意思呢？

after the name of the function?

在函數的名稱後面？

When we use this function in SQL,

當我們在SQL中使用這個函數時。

we're going to use it much like this

我們將像這樣使用它

SELECT inventory_held_by_customer.

SELECT inventory_held_by_customer.

And then we're going to supply a number in the brackets

然後我們要在括號裡提供一個數字

and that's the p_inventory_id,

而這就是p_inventory_id。

and the number needs to be an integer.

而這個數字需要是一個整數。

And then this line says RETURNS int.

然後這一行說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

你可以看到，我們有BEGIN和END聲明

to this function,

到這個功能。

and after that, we have a variable declaration,

而在這之後，我們有一個變量聲明。

and then an EXIT HANDLER at the top.

然後在頂部有一個EXIT HANDLER。

Now this might look a bit odd because we're declaring

現在這可能看起來有點奇怪，因為我們正在聲明

a variable using the DECLARE statement.

使用DECLARE語句，一個變量。

And we've just seen that we can use the SET statement

而我們剛剛看到，我們可以使用SET語句

with an @ sign to declare a variable like so.

用@符號來聲明一個變量，像這樣。

So why are these two things different?

那麼，為什麼這兩件事是不同的呢？

They're both variables in my SQL,

它們在我的SQL中都是變量。

but they're different kinds.

但它們是不同的種類。

Where you see the SET keyword followed by the @ sign,

在你看到SET關鍵詞後面有@符號的地方。

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

如果你試圖引用v_customer_id

after the END keyword,

在END關鍵字之後。

MySQL wouldn't know what you were talking about.

MySQL不會知道你在說什麼。

Where you have a variable name

在你有一個變量名的地方

and the DECLARE keyword with no @ sign,

和沒有@符號的DECLARE關鍵字。

you're using a local variable.

你在使用一個局部變量。

And these cease to exist after the END keyword.

而這些在END關鍵字之後就不存在了。

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.

它們存在於BEGIN和END的聲明之外。

But in a function like this,

但在這樣的一個函數中。

it's nice and tidy to have those variables

有這些變量是很好的，很整潔的。

disappear after the END statement.

在END語句之後消失。

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;

聲明未找到的退出處理程序，返回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.

則返回一個NULL。

So show on screen the word NULL.

所以在螢幕上顯示NULL這個詞。

After that, we have what looks like

在這之後，我們有看起來像

a regular SELECT statement,

一個普通的SELECT語句。

except that this one has an INTO keyword.

除了這個有一個INTO關鍵字。

And what it's saying is select the customer ID

它所說的是選擇客戶的ID

and pop it into a variable called v_customer_id.

並將其彈入一個名為v_customer_id的變量。

v_customer_id is what is going to be returned

v_customer_id是將被返回的內容。

as in shown on screen,

如螢幕上所示。

and you can see that further down

而且你可以看到，在更遠的地方

the query next to the END declaration

在END聲明旁邊的查詢

it says RETURN v_customer_id.

它說RETURN v_customer_id。

So that is what we're going to see on screen.

是以，這就是我們將在螢幕上看到的情況。

So what's this SELECT statement doing?

那麼這個SELECT語句在做什麼？

It says select the customer ID from the rental table

它說從租賃表中選擇客戶ID

where the return date is NULL,

其中返回日期為NULL。

that is to say the DVD is still on loan,

也就是說，該DVD仍在借閱中。

and then the inventory_id matches

而後，庫存_id與

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.

一個合理的inventory_id來使用。

So SELECT inventory_held_by_customer 2047 returns 155.

所以SELECT inventory_held_by_customer 2047返回155。

So have a quick look back at our query.

是以，請快速回顧一下我們的疑問。

We have supplied an inventory_id,

我們已經提供了一個 inventory_id。

that is to say a piece of stock or DVD

就是說一塊股票或DVD

with the number 2047 in the database.

與數據庫中的數字2047。

And this query is returning us the customer ID

這個查詢返回給我們的是客戶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.

我們可以試試367。

You can see we returned a NULL.

你可以看到我們返回了一個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,

曾在DVD367中僱用過。

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,

其中inventory_id為367。

and we get five rows.

而我們得到了五行。

So this DVD has been rented before,

所以這張DVD以前就被租過。

but if we look, the return date is filled in in every case,

但如果我們看一下，每個案例中都填寫了返回日期。

which means all of those DVDs have been returned.

這意味著所有這些DVD都已被退回。

There's no outstanding item.

沒有未決項目。

And that's why we got the NULL when we ran our function.

這就是為什麼我們在運行我們的函數時得到了NULL。

Our DECLARE EXIT HANDLER came into play.

我們的DECLARE EXIT HANDLER開始發揮作用。

Now on its own, this function is a little bit unhelpful.

現在，就其本身而言，這個功能有點無濟於事。

Knowing that customer 155 has a movie that's still on loan

知道客戶155有一部電影還在借閱中

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

2047

is customer_id

是customer_id

AND

和

inventory_held_by_customer

客戶持有的庫存

2047

2047

IS NOT NULL.

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.

我們知道，那是客戶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)

(休閒音樂)