MySQL全面瓦解18：自定義函數

定義

我們之前學習了MySQL的內置函數，非常豐富，滿足了我們對數據操作的大部分需求。

但是如果有一些複雜的業務邏輯在資料庫層面就可以完成，無需在程式層面完成的時候，這時候就可以寫成MySQL自定義函數。

所以，函數是指一組預編譯好的sql語句集合，理解成批處理語句。類似於C# 中的方法，並且必須有返回值。

調用函數就是一次性執行這些語句。所以函數可以降低語句重複。【但注意的是函數注重返回值，不注重執行過程，所以一些語句無法執行。所以函數並不是單純的sql語句集合。】

作用

1、可以高度抽象業務邏輯，前置到資料庫層面，而不是應用層面

2、相比於從資料庫查詢出來，然後程式操作數據，資料庫操作一定程度上提高效率。

3、高度可復用性，資料庫層面的方法封裝，不只是應用在多個同樣業務場景。還可以應用到多個不同語言中。

函數的使用

創建函數

1 CREATE FUNCTION func_name(param_list) RETURNS TYPE
2 BEGIN
3      -- Todo:function body
4 END

1、param_list指的是參數列表，參數是可選的，可以不帶參數，也可以帶多個參數。參數包含兩部分：參數名參數類型。
2、函數返回值是必選項，但是只允許返回一個值，不允許返回一個結果集（官方原文：Not allowed to return a result set from a function）。函數強調返回值，所以函數不允許返回多個值的情況，即使是查詢語句。這是他會跟存儲過程的區別。
3、函數體中如果有多個語句，使用begin end 包含
4、使用 delimiter語句設置結束標記 */

調用函數

SELECT func_name(param_list);

查看函數

1 SHOW FUNCTION STATUS;

查看函數創建腳本

1 SHOW CREATE FUNCTION func_name;

刪除函數

1 DROP FUNCTION IF EXISTS func_name;

示例

數據基礎

 1 mysql> select * from students;
 2 +-----------+-------------+-------+---------+
 3 | studentid | studentname | score | classid |
 4 +-----------+-------------+-------+---------+
 5 |         1 | brand       | 105.5 |       1 |
 6 |         2 | helen       | 98.5  |       1 |
 7 |         3 | lyn         | 97    |       1 |
 8 |         4 | sol         | 97    |       1 |
 9 |         5 | b1          | 89    |       2 |
10 |         6 | b2          | 90    |       2 |
11 |         7 | c1          | 76    |       3 |
12 |         8 | c2          | 73.5  |       3 |
13 |         9 | lala        | 73    |       0 |
14 |        10 | A           | 100   |       3 |
15 |        16 | test1       | 100   |       0 |
16 |        17 | trigger2    | 107   |       0 |
17 |        22 | trigger1    | 100   |       0 |
18 +-----------+-------------+-------+---------+
19 13 rows in set

無參函數

獲取有班級號的所有同學的平均成績

 1 /*如果存在函數func_test1，則刪除*/
 2 DROP FUNCTION IF EXISTS fun_test1;
 3 /*聲明結束符為$*/
 4 DELIMITER $
 5 /*創建函數*/
 6 CREATE FUNCTION fun_test1()
 7   RETURNS DECIMAL(10,2)
 8   BEGIN
 9     DECLARE avg_score DECIMAL(10,2) DEFAULT 0;
10     SELECT AVG(score) INTO avg_score FROM students where classid<>0;
11     return avg_score;
12   END $
13 /*重置結束符為;*/
14 DELIMITER ;

1 mysql> select fun_test1();
2 +-------------+
3 | fun_test1() |
4 +-------------+
5 | 91.83       |
6 +-------------+
7 1 row in set

有參函數

獲取班級號為1的同學的平均成績

 1 /*如果存在函數func_test2，則刪除*/
 2 DROP FUNCTION IF EXISTS fun_test2;
 3 /*聲明結束符為$*/
 4 DELIMITER $
 5 /*創建函數*/
 6 CREATE FUNCTION fun_test2(cid INT)
 7   RETURNS DECIMAL(10,2)
 8   BEGIN
 9     DECLARE avg_score DECIMAL(10,2) DEFAULT 0;
10     SELECT AVG(score) INTO avg_score FROM students where classid=cid;
11     return avg_score;
12   END $
13 /*重置結束符為;*/
14 DELIMITER ;

1 mysql> select fun_test2(1);
2 +--------------+
3 | fun_test2(1) |
4 +--------------+
5 | 99.5         |
6 +--------------+
7 1 row in set

查看函數資訊

1 mysql> SHOW FUNCTION STATUS;
2 +------+-----------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
3 | Db   | Name      | Type     | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
4 +------+-----------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
5 | test | fun_test  | FUNCTION | root@localhost | 2021-01-15 16:37:50 | 2021-01-15 16:37:50 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
6 | test | fun_test1 | FUNCTION | root@localhost | 2021-01-16 11:59:40 | 2021-01-16 11:59:40 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
7 | test | fun_test2 | FUNCTION | root@localhost | 2021-01-16 12:00:27 | 2021-01-16 12:00:27 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
8 +------+-----------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
9 3 rows in set

查看函數創建腳本

 1 mysql> show create function fun_test2;
 2 +-----------+--------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
 3 | Function  | sql_mode                                   | Create Function                                                                                                                                                                                                                         | character_set_client | collation_connection | Database Collation |
 4 +-----------+--------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
 5 | fun_test2 | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` FUNCTION `fun_test2`(cid INT) RETURNS decimal(10,2)
 6 BEGIN
 7     DECLARE avg_score DECIMAL(10,2) DEFAULT 0;
 8     SELECT AVG(score) INTO avg_score FROM students where classid=cid;
 9     return avg_score;
10   END | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
11 +-----------+--------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
12 1 row in set

刪除函數

1 mysql> DROP FUNCTION IF EXISTS fun_test2;
2 Query OK, 0 rows affected
3 
4 mysql> select fun_test2(1);
5 1305 - FUNCTION test.fun_test2 does not exist

小結

存儲過程和函數的區別

存儲過程的關鍵字為procedure，返回值可以有多個，調用時用call，一般用於執行比較複雜的的過程體、更新、創建等語句。

函數的關鍵字為function，返回值必須有一個，調用用select，一般用於查詢單個值並返回。

	存儲過程	函數
返回值	可以有0個或者多個	必須有一個
關鍵字	procedure	function
調用方式	call	select

Tags: function MySQL MySQL全面瓦解自定義函數