MySQL入門系列：儲存程式（二）之儲存函式簡介

小孩子4919發表於2019-03-20

儲存例程

儲存例程是儲存程式的一種型別，本質上也是封裝了一些可執行的語句，只不過它的呼叫方式是：需要手動去呼叫！儲存例程又可以分為儲存函式和儲存過程，下邊我們詳細嘮叨這兩個傢伙。

儲存函式

建立儲存函式

儲存函式其實就是一種函式，只不過在這個函式裡可以執行命令語句而已。函式的概念大家都應該不陌生，它可以把處理某個問題的過程封裝起來，之後我們直接呼叫函式就可以去解決同樣的問題了，簡單方便又環保。MySQL中定義儲存函式的語句如下：

CREATE FUNCTION 儲存函式名稱([引數列表])
RETURNS 返回值型別
BEGIN
    函式體內容
END
複製程式碼

從這裡我們可以看出，定義一個儲存函式需要指定函式名稱、引數列表、返回值型別以及函式體內容，如果該函式不需要引數，那引數列表可以被省略，函式體內容可以包括一條或多條語句，每條語句都要以分號;結尾。裡邊的製表符和換行僅僅是為了好看，如果你覺得煩，完全可以用空格代替！光看定義理解的不深刻，我們先寫一個儲存函式開開眼：

mysql> delimiter $
mysql> CREATE FUNCTION avg_score(s VARCHAR(100))
    -> RETURNS DOUBLE
    -> BEGIN
    ->     RETURN (SELECT AVG(score) FROM student_score WHERE subject = s);
    -> END $
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
複製程式碼

我們定義了一個名叫avg_score的函式，它接收一個VARCHAR(100)型別的引數，宣告的返回值型別是DOUBLE，需要注意的是，我們在RETURN語句後邊寫了一個SELECT語句，表明這個函式的最後返回結果就是根據這個查詢語句產生的，也就是返回了指定科目的平均成績。

儲存函式的呼叫

我們自定義的函式和系統內建函式的使用方式是一樣的，都是在函式名後加小括號()表示函式呼叫，有引數的函式呼叫可以把引數寫到小括號裡邊。函式呼叫可以作為查詢物件或者搜尋條件，或者和別的運算元一起組成更復雜的表示式，我們現在來呼叫一下剛剛寫好的這個函式吧：

mysql> SELECT avg_score('母豬的產後護理');
+------------------------------------+
| avg_score('母豬的產後護理')        |
+------------------------------------+
|                                 73 |
+------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT avg_score('論薩達姆的戰爭準備');
+------------------------------------------+
| avg_score('論薩達姆的戰爭準備')          |
+------------------------------------------+
|                                    73.25 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql>
複製程式碼

這樣呼叫函式就比我們直接寫兩個又臭又長的查詢語句簡單多了。

檢視和刪除儲存函式

如果我們想檢視我們已經定義了多少個儲存函式，可以使用下邊這個語句：

SHOW FUNCTION STATUS [LIKE 需要匹配的函式名]
複製程式碼

由於這個命令得到的結果太多，我們就不演示了哈，自己試試。

如果我們想檢視某個函式的具體定義，可以使用這個語句：

SHOW CREATE FUNCTION 函式名
複製程式碼

比如這樣：

mysql> SHOW CREATE FUNCTION avg_score\G
*************************** 1. row ***************************
            Function: avg_score
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `avg_score`(s VARCHAR(100)) RETURNS double
BEGIN
        RETURN (SELECT AVG(score) FROM student_score WHERE subject = s);
    END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.01 sec)

mysql>
複製程式碼

忽略結果中我們看不懂的那些東東，後邊都會詳細嘮叨的，現在主要聚焦在這個函式的定義處。

如果想刪除某個儲存函式，使用這個語句：

DROP FUNCTION 函式名
複製程式碼

比如我們來刪掉avg_score這個函式：

mysql> DROP FUNCTION avg_score;
Query OK, 0 rows affected (0.00 sec)

mysql>
複製程式碼

什麼？你以為到這裡儲存函式就嘮叨完了麼？寫完是不可能的，這輩子都不可能寫完的！到現在為止我們只是勾勒出一個儲存函式的大致輪廓，下邊我們來詳細說一下MySQL定義函式體時支援的一些語句。

在函式體中定義變數

我們在前邊說過在命令列（黑框框）中自定義變數的方式，它可以不用宣告就為變數賦值（也就是呼叫SET語句）。而在函式體中使用變數前必須先宣告這個變數，宣告方式如下：

DECLARE 變數名 資料型別 [DEFAULT 預設值];   
複製程式碼

需要特別留心的是，函式體中的變數名不允許加@字首，這一點和黑框框中定義變數的方式是截然不同的，特別注意一下。在宣告瞭這個變數之後，才可以使用它：

mysql> delimiter $;
mysql> CREATE FUNCTION var_demo()
-> RETURNS INT
-> BEGIN
->     DECLARE c INT;
->     SET c = 5;
->     RETURN c;
-> END $
Query OK, 0 rows affected (0.00 sec)
    
mysql> delimiter ;
複製程式碼

我們定義了一個名叫var_demo而且不需要引數的函式，我們在函式體中宣告瞭一個名稱為c的INT型別變數，之後我們呼叫SET語句為這個變數賦值了整數5，並且把變數c當作函式結果返回，我們呼叫一下這個函式：

mysql> select var_demo();
+------------+
| var_demo() |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)

mysql>
複製程式碼

如果我們不對宣告的變數賦值的話，它的預設值就是NULL，當然我們也可以通過DEFAULT子句來顯式的指定變數的預設值，比如這樣：

mysql> delimiter $
mysql> CREATE FUNCTION var_default_demo()
-> RETURNS INT
-> BEGIN
->     DECLARE c INT DEFAULT 1;
->     RETURN c;
-> END $
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql>
複製程式碼

在新建立的這個var_default_demo函式中，我們宣告瞭一個變數c，並且指定了它的預設值為1，然後看一下函式的呼叫結果：

mysql> SELECT var_default_demo();
+--------------------+
| var_default_demo() |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

mysql>
複製程式碼

得到的結果是1，說明了我們指定的變數預設值生效了！另外，特別需要注意一下我們可以將某個查詢語句的結果賦值給變數的情況，比如我們改寫一下前邊的avg_score函式：

CREATE FUNCTION avg_score(s VARCHAR(100)) 
RETURNS DOUBLE
BEGIN
    DECLARE a DOUBLE;
    SET a = (SELECT AVG(score) FROM student_score WHERE subject = s);
    return a;
END
複製程式碼

我們先把一個查詢語句的結果賦值給了變數a，然後再返回了這個變數。

引數的編寫

在定義函式的時候，可以指定多個引數，每個引數都要指定對應的資料型別，就像這樣：

引數名 資料型別
複製程式碼

比如我們上邊編寫的這個avg_score函式：

CREATE FUNCTION avg_score(s VARCHAR(100))
RETURNS DOUBLE
BEGIN
    RETURN (SELECT AVG(score) FROM student_score WHERE subject = s);
END 
複製程式碼

這個函式只需要一個型別為VARCHAR(100)引數，我們這裡給這個引數起的名稱是s，需要注意的是，引數名不要和函式體語句中其他的變數名、命令語句的識別符號衝突，比如如果把這個變數名命名為subject，它就與下邊用到WHERE子句中的列名衝突了，導致列名失效。

另外，函式引數不可以指定預設值，我們在呼叫函式的時候，必須顯式的指定所有的引數，並且引數型別也一定要匹配，比方說我們在呼叫函式avg_score時，必須指定我們要查詢的課程名，不然會報錯的：

mysql> select avg_score();
ERROR 1318 (42000): Incorrect number of arguments for FUNCTION xiaohaizi.avg_score; expected 1, got 0
mysql>
複製程式碼

判斷語句的編寫

像其他的程式語言一樣，在MySQL的函式體裡也可以使用判斷的語句，語法格式如下：

IF 布林表示式 THEN 
    處理語句
[ELSEIF 布林表示式 THEN
    處理語句]
[ELSE 
    處理語句]    
END IF;
複製程式碼

需要注意的是，這裡的處理語句可以是由多條語句構成的複合語句。我們舉個例子：

mysql> delimiter $
mysql> CREATE FUNCTION condition_demo(i INT)
-> RETURNS VARCHAR(10)
-> BEGIN
->     DECLARE result VARCHAR(10);
->     IF i = 1 THEN
->         SET result = '結果是1';
->     ELSEIF i = 2 THEN
->         SET result = '結果是2';
->     ELSEIF i = 3 THEN
->         SET result = '結果是3';
->     ELSE
->         SET result = '非法引數';
->     END IF;
->     RETURN result;
-> END $
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> 
複製程式碼

在我們定義的函式condition_demo中，它接收一個INT型別的引數，這個函式的處理邏輯如下：

如果這個引數的值是1，就把result變數的值設定為'結果是1'。
否則如果這個這個引數的值是2，就把result變數的值設定為'結果是2'。
否則如果這個這個引數的值是3，就把result變數的值設定為'結果是3'。
否則就把result變數的值設定為'非法引數'。

當然了，我們舉的這個例子還是比較白痴的啦，當然了，我們只是為了說明語法怎麼用，等於到更復雜一點的業務邏輯再往復雜了說哈。我們現在呼叫一下這個函式：

mysql> SELECT condition_demo(2);
+-------------------+
| condition_demo(2) |
+-------------------+
| 結果是2           |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT condition_demo(5);
+-------------------+
| condition_demo(5) |
+-------------------+
| 非法引數          |
+-------------------+
1 row in set (0.00 sec)

mysql>
複製程式碼

迴圈語句的編寫

除了判斷語句，MySQL還支援迴圈語句的編寫，不過有3種形式的迴圈語句，我們一一道來：

WHILE迴圈語句：
```
WHILE 布林表示式 DO
    迴圈語句
END WHILE;
複製程式碼
```
這個語句的意思是：如果滿足給定的表示式，則執行迴圈語句，否則退出迴圈。比如我們想定義一個從1到n這n個數的和（假設n大於0），可以這麼寫：
```
mysql> delimiter $
mysql> CREATE FUNCTION sum_all(n INT UNSIGNED)
-> RETURNS INT
-> BEGIN
->     DECLARE result INT DEFAULT 0;
->     DECLARE i INT DEFAULT 1;
->     WHILE i <= n DO
->         SET result = result + i;
->         SET i = i + 1;
->     END WHILE;
->     RETURN result;
-> END $
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql>
複製程式碼
```
在函式sum_all中，我們接收一個INT UNSIGNED型別的引數，宣告瞭兩個INT型別的變數i和result。我們先測試一下這個函式：
```
mysql> SELECT sum_all(3);
+------------+
| sum_all(3) |
+------------+
|          6 |
+------------+
1 row in set (0.00 sec)

mysql>
複製程式碼
```
分析一下這個結果是怎麼產生的，初始的情況下result的值是0，i的值是1，給定的引數n的值是3。這個過程就是：
1. 先判斷i <= n是否成立，顯然成立，進入迴圈體，將result的值設定為1（result + i），i的值為2（i + 1）。
2. 再判斷i <= n是否成立，顯然成立，進入迴圈體，將result的值設定為3（result + i），i的值為3（i + 1）。
3. 再判斷i <= n是否成立，顯然成立，進入迴圈體，將result的值設定為6（result + i），i的值為4（i + 1）。
4. 再判斷i <= n是否成立，顯然不成立，退出迴圈。
所以最後返回的result的值就是6，也就是1、2、3這三個數的和。
REPEAT迴圈語句

REPEAT迴圈和WHILE迴圈差不多，只是形式上變了一下：
```
REPEAT
    迴圈語句
UNTIL 布林表示式 END REPEAT;
複製程式碼
```
先執行迴圈語句，再判斷布林表示式是否成立，如果成立繼續執行迴圈語句，否則退出迴圈。與WHILE迴圈不同的一點是：WHILE迴圈先判斷布林表示式的值，再執行迴圈語句，REPEAT迴圈先執行迴圈語句，再判斷布林表示式的值，所以至少執行一次迴圈語句，所以如果sum_all函式用REPEAT迴圈改寫，可以寫成這樣：
```
CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT
BEGIN
    DECLARE result INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    REPEAT 
        SET result = result + i;
        SET i = i + 1;
    UNTIL i <= n END REPEAT;
    RETURN result;
END
複製程式碼
```

LOOP迴圈語句

這只是另一種形式的迴圈語句：

迴圈標記:LOOP
    迴圈語句
    LEAVE 迴圈標記;
END LOOP 迴圈標記;
複製程式碼

在LOOP迴圈語句中，比較特別的是需要我們設定迴圈標記來標識一個迴圈，在迴圈體內依靠 LEAVE 迴圈標記的形式來中斷某個迴圈，比方說我們可以把sum_all函式改寫成這樣：

CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT
BEGIN
    DECLARE result INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    flag:LOOP  
        IF i > n THEN
            LEAVE flag;
        END IF;
        SET result = result + i;
        SET i = i + 1;
    END LOOP flag;
    RETURN result;
END
複製程式碼

其中的flag就是一個迴圈標記，在迴圈體內判斷i > n成立的時候就呼叫LEAVE flag來跳出這個迴圈。

註釋的使用

不論什麼時候，對語句新增註釋都是一件好事兒！註釋不僅僅是幫助別人理解我們寫的語句是什麼意思，對於我們自己來說，可能隔了幾天之後再看自己寫的語句就不知道是什麼意思了。在函式體內以--開頭的語句都算作註釋語句，MySQL伺服器在執行語句的時候會忽略掉這些註釋語句。

-- 函式名：sum_all
-- 引數：n = 從1累加到的數字

CREATE FUNCTION sum_all(n INT UNSIGNED) COMMENT '求1到n這n個數的和'
RETURNS INT
BEGIN
    -- 當前累加的和
    DECLARE result INT DEFAULT 0;
    
    -- 當前累加的數字
    DECLARE i INT DEFAULT 1;
    
    -- 若當前累加的數字不大於指定數字，則繼續執行迴圈
    WHILE i <= n DO
        SET result = result + i;
        SET i = i + 1;
    END WHILE;
    
    -- 返回累加的和
    RETURN result;
END
複製程式碼

除了--開頭的語句表示註釋，我們還可以在函式引數後寫COMMENT註釋語句說明這個函式的作用。

小冊

本系列專欄都是MySQL入門知識，想看進階知識可以到小冊中檢視：《MySQL是怎樣執行的：從根兒上理解MySQL》的連結。小冊的內容主要是從小白的角度出發，用比較通俗的語言講解關於MySQL進階的一些核心概念，比如記錄、索引、頁面、表空間、查詢優化、事務和鎖等，總共的字數大約是三四十萬字，配有上百幅原創插圖。主要是想降低普通程式設計師學習MySQL進階的難度，讓學習曲線更平滑一點～

MySQL入門系列：儲存程式（三）之儲存過程簡介
2019-03-21
MySql儲存過程
MySQL入門--儲存過程(PROCEDURE)和儲存函式(FUNCTION)
2019-05-30
MySql儲存過程儲存函式Function
MySQL儲存引擎入門介紹
2020-09-28
MySql儲存引擎
MySQL之儲存函式詳細介紹艹籟
2022-03-01
MySql儲存函式
MySQL入門--儲存引擎
2019-06-28
MySql儲存引擎
MySQL 儲存函式及呼叫
2024-06-10
MySql儲存函式
mysql和orcale的儲存過程和儲存函式
2018-06-04
MySql儲存過程儲存函式
儲存過程與儲存函式
2020-11-16
儲存過程儲存函式
MySQL-09-mysql 儲存過程入門介紹
2024-04-13
MySql儲存過程
MySQL儲存過程 (即函式)
2019-01-24
MySql儲存過程函式
mySql 儲存過程與函式
2024-05-24
MySql儲存過程函式
MySQL 儲存過程和函式
2022-04-23
MySql儲存過程函式
MySQL儲存過程和函式
2020-12-12
MySql儲存過程函式
MySQL系列-儲存引擎
2018-12-03
MySql儲存引擎
儲存引擎簡介
2020-11-05
儲存引擎
Azure 儲存簡介
2021-01-15
MySQL資料庫儲存引擎簡介
2019-03-12
MySql資料庫儲存引擎
MySQL入門系列：查詢簡介（三）之表示式和函式
2019-03-11
MySql函式
InnoDB儲存引擎簡介
2020-08-07
儲存引擎
SQL---------儲存函式
2024-07-24
SQL儲存函式
HDU-安卓程式開發之簡單儲存/內部儲存/外部儲存 & 捉蟲
2020-12-04
安卓
MySQL自定義函式與儲存過程
2019-04-13
MySql函式儲存過程
行式儲存列式儲存
2024-04-23
mysql 匯入匯出資料庫以及函式、儲存過程的介紹
2021-09-09
MySql資料庫函式儲存過程
Gartner：浪潮儲存進入分散式儲存前三
2021-10-25
分散式
[MySQL光速入門]015 聊聊儲存過程
2019-04-18
MySql儲存過程
MySQL之儲存過程
2018-04-17
MySql儲存過程
MySQL之四儲存引擎
2021-03-02
MySql儲存引擎
MySQL優化---儲存過程和儲存函式-1-轉自部落格園
2018-10-11
MySql優化儲存過程儲存函式
MySQL儲存
2024-06-10
MySql
mysql儲存過程及日期函式實踐
2021-09-09
MySql儲存過程函式
mysql儲存過程procedure、函式function的用法
2021-01-03
MySql儲存過程函式Function
kubernetes系列(十三) - 儲存之Volume
2020-07-08
kubernetes系列(十二) - 儲存之Secret
2020-07-07
kubernetes系列(十四) - 儲存之PersistentVolume
2020-07-10
分散式文件儲存資料庫之MongoDB基礎入門
2020-11-08
分散式資料庫MongoDB
Mysql中儲存引擎簡介、修改、查詢、選擇
2024-07-08
MySql儲存引擎
分散式儲存技術解讀系列之GFS
2022-05-23
分散式