MySQL入门系列：存储程序（二）之存储函数简介
存儲(chǔ)例程
存儲(chǔ)例程是存儲(chǔ)程序的一種類型，本質(zhì)上也是封裝了一些可執(zhí)行的語(yǔ)句，只不過它的調(diào)用方式是：需要手動(dòng)去調(diào)用！存儲(chǔ)例程又可以分為存儲(chǔ)函數(shù)和存儲(chǔ)過程，下邊我們?cè)敿?xì)嘮叨這兩個(gè)家伙。
存儲(chǔ)函數(shù)
創(chuàng)建存儲(chǔ)函數(shù)
存儲(chǔ)函數(shù)其實(shí)就是一種函數(shù)，只不過在這個(gè)函數(shù)里可以執(zhí)行命令語(yǔ)句而已。函數(shù)的概念大家都應(yīng)該不陌生，它可以把處理某個(gè)問題的過程封裝起來，之后我們直接調(diào)用函數(shù)就可以去解決同樣的問題了，簡(jiǎn)單方便又環(huán)保。MySQL中定義存儲(chǔ)函數(shù)的語(yǔ)句如下：
CREATE FUNCTION 存儲(chǔ)函數(shù)名稱([參數(shù)列表]) RETURNS 返回值類型 BEGIN函數(shù)體內(nèi)容 END 復(fù)制代碼從這里我們可以看出，定義一個(gè)存儲(chǔ)函數(shù)需要指定函數(shù)名稱、參數(shù)列表、返回值類型以及函數(shù)體內(nèi)容，如果該函數(shù)不需要參數(shù)，那參數(shù)列表可以被省略，函數(shù)體內(nèi)容可以包括一條或多條語(yǔ)句，每條語(yǔ)句都要以分號(hào);結(jié)尾。里邊的制表符和換行僅僅是為了好看，如果你覺得煩，完全可以用空格代替！ 光看定義理解的不深刻，我們先寫一個(gè)存儲(chǔ)函數(shù)開開眼：
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 ; 復(fù)制代碼我們定義了一個(gè)名叫avg_score的函數(shù)，它接收一個(gè)VARCHAR(100)類型的參數(shù)，聲明的返回值類型是DOUBLE，需要注意的是，我們?cè)赗ETURN語(yǔ)句后邊寫了一個(gè)SELECT語(yǔ)句，表明這個(gè)函數(shù)的最后返回結(jié)果就是根據(jù)這個(gè)查詢語(yǔ)句產(chǎn)生的，也就是返回了指定科目的平均成績(jī)。
存儲(chǔ)函數(shù)的調(diào)用
我們自定義的函數(shù)和系統(tǒng)內(nèi)置函數(shù)的使用方式是一樣的，都是在函數(shù)名后加小括號(hào)()表示函數(shù)調(diào)用，有參數(shù)的函數(shù)調(diào)用可以把參數(shù)寫到小括號(hào)里邊。函數(shù)調(diào)用可以作為查詢對(duì)象或者搜索條件，或者和別的操作數(shù)一起組成更復(fù)雜的表達(dá)式，我們現(xiàn)在來調(diào)用一下剛剛寫好的這個(gè)函數(shù)吧：
mysql> SELECT avg_score('母豬的產(chǎn)后護(hù)理'); +------------------------------------+ | avg_score('母豬的產(chǎn)后護(hù)理') | +------------------------------------+ | 73 | +------------------------------------+ 1 row in set (0.00 sec)mysql> SELECT avg_score('論薩達(dá)姆的戰(zhàn)爭(zhēng)準(zhǔn)備'); +------------------------------------------+ | avg_score('論薩達(dá)姆的戰(zhàn)爭(zhēng)準(zhǔn)備') | +------------------------------------------+ | 73.25 | +------------------------------------------+ 1 row in set (0.00 sec)mysql> 復(fù)制代碼這樣調(diào)用函數(shù)就比我們直接寫兩個(gè)又臭又長(zhǎng)的查詢語(yǔ)句簡(jiǎn)單多了。
查看和刪除存儲(chǔ)函數(shù)
如果我們想查看我們已經(jīng)定義了多少個(gè)存儲(chǔ)函數(shù)，可以使用下邊這個(gè)語(yǔ)句：
SHOW FUNCTION STATUS [LIKE 需要匹配的函數(shù)名] 復(fù)制代碼由于這個(gè)命令得到的結(jié)果太多，我們就不演示了哈，自己試試。
如果我們想查看某個(gè)函數(shù)的具體定義，可以使用這個(gè)語(yǔ)句：
SHOW CREATE FUNCTION 函數(shù)名 復(fù)制代碼比如這樣：
mysql> SHOW CREATE FUNCTION avg_score\G *************************** 1. row ***************************Function: avg_scoresql_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_SUBSTITUTIONCreate Function: CREATE DEFINER=`root`@`localhost` FUNCTION `avg_score`(s VARCHAR(100)) RETURNS double BEGINRETURN (SELECT AVG(score) FROM student_score WHERE subject = s);END character_set_client: utf8 collation_connection: utf8_general_ciDatabase Collation: utf8_general_ci 1 row in set (0.01 sec)mysql> 復(fù)制代碼忽略結(jié)果中我們看不懂的那些東東，后邊都會(huì)詳細(xì)嘮叨的，現(xiàn)在主要聚焦在這個(gè)函數(shù)的定義處。
如果想刪除某個(gè)存儲(chǔ)函數(shù)，使用這個(gè)語(yǔ)句：
DROP FUNCTION 函數(shù)名 復(fù)制代碼比如我們來刪掉avg_score這個(gè)函數(shù)：
mysql> DROP FUNCTION avg_score; Query OK, 0 rows affected (0.00 sec)mysql> 復(fù)制代碼什么？你以為到這里存儲(chǔ)函數(shù)就嘮叨完了么？寫完是不可能的，這輩子都不可能寫完的！到現(xiàn)在為止我們只是勾勒出一個(gè)存儲(chǔ)函數(shù)的大致輪廓，下邊我們來詳細(xì)說一下MySQL定義函數(shù)體時(shí)支持的一些語(yǔ)句。
在函數(shù)體中定義變量
我們?cè)谇斑呎f過在命令行（黑框框）中自定義變量的方式，它可以不用聲明就為變量賦值（也就是調(diào)用SET語(yǔ)句）。而在函數(shù)體中使用變量前必須先聲明這個(gè)變量，聲明方式如下：
DECLARE 變量名 數(shù)據(jù)類型 [DEFAULT 默認(rèn)值]; 復(fù)制代碼需要特別留心的是，函數(shù)體中的變量名不允許加@前綴，這一點(diǎn)和黑框框中定義變量的方式是截然不同的，特別注意一下。在聲明了這個(gè)變量之后，才可以使用它：
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 ; 復(fù)制代碼我們定義了一個(gè)名叫var_demo而且不需要參數(shù)的函數(shù)，我們?cè)诤瘮?shù)體中聲明了一個(gè)名稱為c的INT類型變量，之后我們調(diào)用SET語(yǔ)句為這個(gè)變量賦值了整數(shù)5，并且把變量c當(dāng)作函數(shù)結(jié)果返回，我們調(diào)用一下這個(gè)函數(shù)：
mysql> select var_demo(); +------------+ | var_demo() | +------------+ | 5 | +------------+ 1 row in set (0.00 sec)mysql> 復(fù)制代碼如果我們不對(duì)聲明的變量賦值的話，它的默認(rèn)值就是NULL，當(dāng)然我們也可以通過DEFAULT子句來顯式的指定變量的默認(rèn)值，比如這樣：
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> 復(fù)制代碼在新創(chuàng)建的這個(gè)var_default_demo函數(shù)中，我們聲明了一個(gè)變量c，并且指定了它的默認(rèn)值為1，然后看一下函數(shù)的調(diào)用結(jié)果：
mysql> SELECT var_default_demo(); +--------------------+ | var_default_demo() | +--------------------+ | 1 | +--------------------+ 1 row in set (0.00 sec)mysql> 復(fù)制代碼得到的結(jié)果是1，說明了我們指定的變量默認(rèn)值生效了！另外，特別需要注意一下我們可以將某個(gè)查詢語(yǔ)句的結(jié)果賦值給變量的情況，比如我們改寫一下前邊的avg_score函數(shù)：
CREATE FUNCTION avg_score(s VARCHAR(100)) RETURNS DOUBLE BEGINDECLARE a DOUBLE;SET a = (SELECT AVG(score) FROM student_score WHERE subject = s);return a; END 復(fù)制代碼我們先把一個(gè)查詢語(yǔ)句的結(jié)果賦值給了變量a，然后再返回了這個(gè)變量。
參數(shù)的編寫
在定義函數(shù)的時(shí)候，可以指定多個(gè)參數(shù)，每個(gè)參數(shù)都要指定對(duì)應(yīng)的數(shù)據(jù)類型，就像這樣：
參數(shù)名 數(shù)據(jù)類型 復(fù)制代碼比如我們上邊編寫的這個(gè)avg_score函數(shù)：
CREATE FUNCTION avg_score(s VARCHAR(100)) RETURNS DOUBLE BEGINRETURN (SELECT AVG(score) FROM student_score WHERE subject = s); END 復(fù)制代碼這個(gè)函數(shù)只需要一個(gè)類型為VARCHAR(100)參數(shù)，我們這里給這個(gè)參數(shù)起的名稱是s，需要注意的是，參數(shù)名不要和函數(shù)體語(yǔ)句中其他的變量名、命令語(yǔ)句的標(biāo)識(shí)符沖突，比如如果把這個(gè)變量名命名為subject，它就與下邊用到WHERE子句中的列名沖突了，導(dǎo)致列名失效。
另外，函數(shù)參數(shù)不可以指定默認(rèn)值，我們?cè)谡{(diào)用函數(shù)的時(shí)候，必須顯式的指定所有的參數(shù)，并且參數(shù)類型也一定要匹配，比方說我們?cè)谡{(diào)用函數(shù)avg_score時(shí)，必須指定我們要查詢的課程名，不然會(huì)報(bào)錯(cuò)的：
mysql> select avg_score(); ERROR 1318 (42000): Incorrect number of arguments for FUNCTION xiaohaizi.avg_score; expected 1, got 0 mysql> 復(fù)制代碼判斷語(yǔ)句的編寫
像其他的編程語(yǔ)言一樣，在MySQL的函數(shù)體里也可以使用判斷的語(yǔ)句，語(yǔ)法格式如下：
IF 布爾表達(dá)式 THEN 處理語(yǔ)句 [ELSEIF 布爾表達(dá)式 THEN處理語(yǔ)句] [ELSE 處理語(yǔ)句] END IF; 復(fù)制代碼需要注意的是，這里的處理語(yǔ)句可以是由多條語(yǔ)句構(gòu)成的復(fù)合語(yǔ)句。我們舉個(gè)例子：
mysql> delimiter $ mysql> CREATE FUNCTION condition_demo(i INT) -> RETURNS VARCHAR(10) -> BEGIN -> DECLARE result VARCHAR(10); -> IF i = 1 THEN -> SET result = '結(jié)果是1'; -> ELSEIF i = 2 THEN -> SET result = '結(jié)果是2'; -> ELSEIF i = 3 THEN -> SET result = '結(jié)果是3'; -> ELSE -> SET result = '非法參數(shù)'; -> END IF; -> RETURN result; -> END $ Query OK, 0 rows affected (0.00 sec)mysql> delimiter ; mysql> 復(fù)制代碼在我們定義的函數(shù)condition_demo中，它接收一個(gè)INT類型的參數(shù)，這個(gè)函數(shù)的處理邏輯如下：
當(dāng)然了，我們舉的這個(gè)例子還是比較白癡的啦，當(dāng)然了，我們只是為了說明語(yǔ)法怎么用，等于到更復(fù)雜一點(diǎn)的業(yè)務(wù)邏輯再往復(fù)雜了說哈。我們現(xiàn)在調(diào)用一下這個(gè)函數(shù)：
mysql> SELECT condition_demo(2); +-------------------+ | condition_demo(2) | +-------------------+ | 結(jié)果是2 | +-------------------+ 1 row in set (0.00 sec)mysql> SELECT condition_demo(5); +-------------------+ | condition_demo(5) | +-------------------+ | 非法參數(shù) | +-------------------+ 1 row in set (0.00 sec)mysql> 復(fù)制代碼循環(huán)語(yǔ)句的編寫
除了判斷語(yǔ)句，MySQL還支持循環(huán)語(yǔ)句的編寫，不過有3種形式的循環(huán)語(yǔ)句，我們一一道來：
-
WHILE循環(huán)語(yǔ)句：
WHILE 布爾表達(dá)式 DO循環(huán)語(yǔ)句 END WHILE; 復(fù)制代碼這個(gè)語(yǔ)句的意思是：如果滿足給定的表達(dá)式，則執(zhí)行循環(huán)語(yǔ)句，否則退出循環(huán)。比如我們想定義一個(gè)從1到n這n個(gè)數(shù)的和（假設(shè)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> 復(fù)制代碼在函數(shù)sum_all中，我們接收一個(gè)INT UNSIGNED類型的參數(shù)，聲明了兩個(gè)INT類型的變量i和result。我們先測(cè)試一下這個(gè)函數(shù)：
mysql> SELECT sum_all(3); +------------+ | sum_all(3) | +------------+ | 6 | +------------+ 1 row in set (0.00 sec)mysql> 復(fù)制代碼分析一下這個(gè)結(jié)果是怎么產(chǎn)生的，初始的情況下result的值是0，i的值是1，給定的參數(shù)n的值是3。這個(gè)過程就是：
- 先判斷i <= n是否成立，顯然成立，進(jìn)入循環(huán)體，將result的值設(shè)置為1（result + i），i的值為2（i + 1）。
- 再判斷i <= n是否成立，顯然成立，進(jìn)入循環(huán)體，將result的值設(shè)置為3（result + i），i的值為3（i + 1）。
- 再判斷i <= n是否成立，顯然成立，進(jìn)入循環(huán)體，將result的值設(shè)置為6（result + i），i的值為4（i + 1）。
- 再判斷i <= n是否成立，顯然不成立，退出循環(huán)。
-
REPEAT循環(huán)語(yǔ)句
REPEAT循環(huán)和WHILE循環(huán)差不多，只是形式上變了一下：
REPEAT循環(huán)語(yǔ)句 UNTIL 布爾表達(dá)式 END REPEAT; 復(fù)制代碼先執(zhí)行循環(huán)語(yǔ)句，再判斷布爾表達(dá)式是否成立，如果成立繼續(xù)執(zhí)行循環(huán)語(yǔ)句，否則退出循環(huán)。與WHILE循環(huán)不同的一點(diǎn)是：WHILE循環(huán)先判斷布爾表達(dá)式的值，再執(zhí)行循環(huán)語(yǔ)句，REPEAT循環(huán)先執(zhí)行循環(huán)語(yǔ)句，再判斷布爾表達(dá)式的值，所以至少執(zhí)行一次循環(huán)語(yǔ)句，所以如果sum_all函數(shù)用REPEAT循環(huán)改寫，可以寫成這樣：
CREATE FUNCTION sum_all(n INT UNSIGNED) RETURNS INT BEGINDECLARE 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 復(fù)制代碼 -
LOOP循環(huán)語(yǔ)句
這只是另一種形式的循環(huán)語(yǔ)句：
循環(huán)標(biāo)記:LOOP循環(huán)語(yǔ)句LEAVE 循環(huán)標(biāo)記; END LOOP 循環(huán)標(biāo)記; 復(fù)制代碼在LOOP循環(huán)語(yǔ)句中，比較特別的是需要我們?cè)O(shè)置循環(huán)標(biāo)記來標(biāo)識(shí)一個(gè)循環(huán)，在循環(huán)體內(nèi)依靠 LEAVE 循環(huán)標(biāo)記的形式來中斷某個(gè)循環(huán)，比方說我們可以把sum_all函數(shù)改寫成這樣：
CREATE FUNCTION sum_all(n INT UNSIGNED) RETURNS INT BEGINDECLARE result INT DEFAULT 0;DECLARE i INT DEFAULT 1;flag:LOOP IF i > n THENLEAVE flag;END IF;SET result = result + i;SET i = i + 1;END LOOP flag;RETURN result; END 復(fù)制代碼其中的flag就是一個(gè)循環(huán)標(biāo)記，在循環(huán)體內(nèi)判斷i > n成立的時(shí)候就調(diào)用LEAVE flag來跳出這個(gè)循環(huán)。
所以最后返回的result的值就是6，也就是1、2、3這三個(gè)數(shù)的和。
注釋的使用
不論什么時(shí)候，對(duì)語(yǔ)句添加注釋都是一件好事兒！注釋不僅僅是幫助別人理解我們寫的語(yǔ)句是什么意思，對(duì)于我們自己來說，可能隔了幾天之后再看自己寫的語(yǔ)句就不知道是什么意思了。在函數(shù)體內(nèi)以--開頭的語(yǔ)句都算作注釋語(yǔ)句，MySQL服務(wù)器在執(zhí)行語(yǔ)句的時(shí)候會(huì)忽略掉這些注釋語(yǔ)句。
-- 函數(shù)名：sum_all -- 參數(shù)：n = 從1累加到的數(shù)字CREATE FUNCTION sum_all(n INT UNSIGNED) COMMENT '求1到n這n個(gè)數(shù)的和' RETURNS INT BEGIN-- 當(dāng)前累加的和DECLARE result INT DEFAULT 0;-- 當(dāng)前累加的數(shù)字DECLARE i INT DEFAULT 1;-- 若當(dāng)前累加的數(shù)字不大于指定數(shù)字，則繼續(xù)執(zhí)行循環(huán)WHILE i <= n DOSET result = result + i;SET i = i + 1;END WHILE;-- 返回累加的和RETURN result; END 復(fù)制代碼除了--開頭的語(yǔ)句表示注釋，我們還可以在函數(shù)參數(shù)后寫COMMENT注釋語(yǔ)句說明這個(gè)函數(shù)的作用。
小冊(cè)
本系列專欄都是MySQL入門知識(shí)，想看進(jìn)階知識(shí)可以到小冊(cè)中查看：《MySQL是怎樣運(yùn)行的：從根兒上理解MySQL》的鏈接 。小冊(cè)的內(nèi)容主要是從小白的角度出發(fā)，用比較通俗的語(yǔ)言講解關(guān)于MySQL進(jìn)階的一些核心概念，比如記錄、索引、頁(yè)面、表空間、查詢優(yōu)化、事務(wù)和鎖等，總共的字?jǐn)?shù)大約是三四十萬(wàn)字，配有上百幅原創(chuàng)插圖。主要是想降低普通程序員學(xué)習(xí)MySQL進(jìn)階的難度，讓學(xué)習(xí)曲線更平滑一點(diǎn)～
轉(zhuǎn)載于:https://juejin.im/post/5c9198f96fb9a070aa5ce939
總結(jié)
以上是生活随笔為你收集整理的MySQL入门系列：存储程序（二）之存储函数简介的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Python 语言介绍
- 下一篇： Flutter介绍 - Flutter,