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

翁智華發表於2021-01-20
MySql函式

定義

我們之前學習了MySQL的內建函式，傳送門，非常豐富，滿足了我們對資料操作的大部分需求。

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

所以，函式是指 一組預編譯好的sql語句集合，理解成批處理語句，類似於C# 中的方法，但是必須有返回值。呼叫函式等於一次性執行了這些語句，有利降低語句重複編寫和呼叫。

作用

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 ;

 使用 select 呼叫，無需傳入引數

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

獲取班級號為1的同學的平均成績，引數cid 為班級號

 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 ;

 使用 select 呼叫，傳入引數1 

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

  

 

相關文章