SQL 函數(shù)

1. 定義

慕課解釋：函數(shù)可以把經(jīng)常使用的代碼封裝起來，以便需要的時候直接調(diào)用。

本小節(jié)，我們將一起學習 SQL 函數(shù)。

2. 前言

我們在學習編程語言時候，也會遇到函數(shù)，在 SQL 中也是如此，如果你熟悉 Python 或者其它語言，那么一定使用過print這個內(nèi)置函數(shù)。SQL 為了給開發(fā)者提供便利，也提供了一系例的內(nèi)置函數(shù)，它們大致可分為算術(shù)函數(shù)、字符串函數(shù)、日期函數(shù)、轉(zhuǎn)換函數(shù)和聚合函數(shù)五大類。

SQL 除了提供好用的內(nèi)置函數(shù)外，還可以通過 Create 指令來新建一個自定義函數(shù)。在這個小節(jié)中，我們會簡單的介紹自定義函數(shù)的創(chuàng)建和使用，而內(nèi)置函數(shù)我們將在后面的幾個小節(jié)中詳細介紹。

本小節(jié)測試數(shù)據(jù)如下，請先在數(shù)據(jù)庫中執(zhí)行：

DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
  id int PRIMARY KEY,
  username varchar(20),
  age int
);
INSERT INTO imooc_user(id,username,age)
VALUES (1,'peter',18),(2,'pedro',24),(3,'jerry',22),(4,'mike',18),(5,'tom',20);

3. 語法

SQL 函數(shù)是一組 SQL 語句的封裝，因此它的創(chuàng)建是頗為復雜的，大致的步驟可概括為如下幾步：

1. 通過CREATE FUNCTION [function_name]來聲明一個函數(shù)。如：CREATE FUNCTION getUsername；
2. 在函數(shù)名中通過變量聲明函數(shù)參數(shù)，如getUsername(uid int)，并通過returns指定返回值類型，如 returns varchar，表示函數(shù)返回字符串類型；
3. 通過BEGIN開始函數(shù)體，并通過END來結(jié)束函數(shù)體；
4. 若在函數(shù)體內(nèi)使用了變量，需通過DECLARE來聲明變量，如 DECLARE uname varchar(20)；
5. 在函數(shù)體內(nèi)使用 SQL 語句得到結(jié)果，并通過RETURN指定返回值。

我們通過上述步驟新建一個 getUsername 函數(shù)，該函數(shù)通過用戶 id 獲取用戶名。

CREATE FUNCTION getUsername(uid int) RETURNS varchar(20)
BEGIN
	DECLARE uname varchar(20);
	SELECT username FROM imooc_user WHERE id = uid INTO uname;
	RETURN uname;
END;

在 getUsername 函數(shù)體中，若要給 uname 這樣的局部變量賦值，需要使用INTO關鍵字。函數(shù)創(chuàng)建完畢后，通過 Select 來調(diào)用，如SELECT getUsername(1)。

結(jié)果如下：

# SELECT getUsername(1);
+----------------+
| getUsername(1) |
+----------------+
| pedro          |
+----------------+

4. 實踐

4.1 例1 獲得最大年齡

請書寫 SQL 語句，新建一個getOldestAge函數(shù)，該函數(shù)獲得imooc_user中用戶的最大年齡。

分析：

按照上面步驟新建函數(shù) getOldestAge，且該函數(shù)無參，但返回類型為 int ，最大年齡我們可以先使用 Order By 對其排序，然后通過Limit 取第一位用戶，則可得到最大的年齡。

語句：

整理可得語句如下：

CREATE FUNCTION getOldestAge() RETURNS int
BEGIN
	RETURN (SELECT age FROM imooc_user ORDER BY age DESC LIMIT 1);
END;

創(chuàng)建該函數(shù)后，使用 Select 來調(diào)用：

SELECT getOldestAge();

結(jié)果如下：

+----------------+
| getOldestAge() |
+----------------+
| 23             |
+----------------+

5. 小結(jié)

SQL 自定義函數(shù)是十分復雜的一環(huán)，難學且使用少，我們不推薦你在自定義函數(shù)上死磕。在這個小節(jié)里，我們沒有介紹 PostgreSQL 自定義函數(shù)的使用，因為在 PostgreSQL 中，自定義函數(shù)更加復雜。在市面上，我們很少看到書籍和資料會花大量篇幅在自定義函數(shù)上，但是如果你感興趣，我們可以給你推薦幾個鏈接。

Postgresql之自定義函數(shù)

MySQL 自定函數(shù)

• 在查詢的時候，尤其是在 Where 的條件中慎用函數(shù)，它會導致查詢不走索引，從而拉低查詢的速度。
• 自定義函數(shù)用的非常少，因為它的移植性很差，取得的效果也不明顯。