首頁 > 文章 > 資料庫 > MySQL自訂函數

MySQL自訂函數

黄舟原創: 2017-02-27 13:32:362892瀏覽

自訂函數：

使用者自訂函數（user-defined function，UDF）是一種對MySQL擴充的途經，其用法與內建函數相同。

自訂函數有兩個必要條件：
1. 參數
2. 傳回值

函數可以傳回任意類型的值，同樣可以接收這些類型的參數。

函數的參數和回傳值之間沒有必然的內在的聯繫。

範例1：
建立一個無參數的函數，傳回目前時間的，年月日時分秒。
例如：

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2016-09-08 21:17:17 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');
+--------------------------------------------------+
| DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒')             |
+--------------------------------------------------+
| 2016年09月08日 21点:19分:54秒                                |
+--------------------------------------------------+
1 row in set (0.01 sec)

對上述過程經行封裝：

mysql> CREATE FUNCTION f1() RETURNS VARCHAR(30)
    -> RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');Query OK, 0 rows affected (0.08 sec)

mysql> SELECT f1();
+-------------------------------+
| f1()                          |
+-------------------------------+
| 2016年09月08日 21点:21分:25秒             |
+-------------------------------+
1 row in set (0.02 sec)

註：returns後面的為回傳值類型，return後面的為回傳值。

範例2：
建立有參數的函數，例如求兩個數的平均值。

mysql> CREATE FUNCTION f2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED)
    -> RETURNS FLOAT(10,2) UNSIGNED
    -> RETURN (num1+num2)/2;Query OK, 0 rows affected (0.00 sec)

呼叫：

mysql> SELECT f2(4,6);
+---------+
| f2(4,6) |
+---------+
|    5.00 |
+---------+
1 row in set (0.02 sec)

如果要刪除上面所建立的兩個自訂函數：

mysql> DROP FUNCTION f2;Query OK, 0 rows affected (0.11 sec)

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

建立具有複合結構函數體的自訂函數

例如：建立一個函數向表中插入數據，並傳回所插入數據的id。

mysql> DESC tdb_test;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(20)      | NO   |     | NULL    |                |
| last_name  | varchar(20)      | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> CREATE FUNCTION adduser(first_name VARCHAR(20),last_name VARCHAR(20))
    -> RETURNS INT UNSIGNED
    -> RETURN
    -> INSERT tdb_test(first_name,last_name) VALUES(first_name,last_name);
    ERROR 1064 (42000): You have an error in your SQL syntax; 
    check the manual that corresponds to your MySQL server version for the right syntax to use near 'tdb_test(first_name,last_name) 
    VALUES(first_name,last_name)' at line 4

這時我們發現，在輸入要執行的sql語句後，後面的分號為目前預設的分隔符，導致函數無法再繼續輸入。因此需要修改預設的分隔符號。

mysql> DELIMITER //

意思是用//結束

mysql> CREATE FUNCTION adduser(first_name VARCHAR(20),last_name VARCHAR(20))
    -> RETURNS INT UNSIGNED
    -> RETURN
    -> INSERT tdb_test(first_name,last_name) VALUES(first_name,last_name);
    -> LAST_INSERT_ID();
    -> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to 
use near 'tdb_test(first_name,last_name) VALUES(first_name,last_name);
LAST_INSERT_ID()' at line 4

同樣回傳錯誤，因為，傳回有兩個語句要執行，需放入begin和end構成一個聚合體，下面來看正確的做法。

mysql> CREATE FUNCTION adduser(first_name VARCHAR(20),last_name VARCHAR(20))
    -> RETURNS INT UNSIGNED
    -> BEGIN
    -> INSERT tdb_test(first_name,last_name) VALUES(first_name,last_name);
    -> RETURN LAST_INSERT_ID();
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

再將分隔符號改回來

mysql> DELIMITER ;

測試結果如下：

mysql> SELECT adduser("ttt","ddd");
+----------------------+
| adduser("ttt","ddd") |
+----------------------+
|                    5 |
+----------------------+
1 row in set (0.11 sec)

mysql> SELECT * FROM tdb_test;
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | A          | B         |
|  2 | Jack       | Bob       |
|  3 | tom%       | 123       |
|  4 | 11         | 22        |
|  5 | ttt        | ddd       |
+----+------------+-----------+
5 rows in set (0.00 sec)

關於函數體

函數體由合法的sql語句構成；
函數體可以是簡單的SELECT或INSERT語句；
函數本體如果為複合結構則使用BEGIN… END語句；
複合結構可以包含聲明，循環，控制結構；

刪除函數：

DROP FUNCTION [IF EXISTS] function_name