技術文章 » MySQL自定義函數（CREATE FUNCTION）

MySQL自定義函數（CREATE FUNCTION）

2020-07-16 10:04:45
在使用 MySQL 的過程中，MySQL 自帶的函數可能完成不了我們的業務需求，這時候就需要自定義函數。

自定義函數是一種與儲存過程十分相似的過程式資料庫物件。它與儲存過程一樣，都是由 SQL 語句和過程式語句組成的程式碼片段，並且可以被應用程式和其他 SQL 語句呼叫。

自定義函數與儲存過程之間存在幾點區別：
  • 自定義函數不能擁有輸出引數，這是因為自定義函數自身就是輸出引數；而儲存過程可以擁有輸出引數。
  • 自定義函數中必須包含一條 RETURN 語句，而這條特殊的 SQL 語句不允許包含於儲存過程中。
  • 可以直接對自定義函數進行呼叫而不需要使用 CALL 語句，而對儲存過程的呼叫需要使用 CALL 語句。

建立並使用自定義函數

可以使用 CREATE FUNCTION 語句建立自定義函數。

語法格式如下：

CREATE FUNCTION <函數名> ( [ <引數1> <型別1> [ , <引數2> <型別2>] ] … )
  RETURNS <型別>
  <函數主體>

語法說明如下：
  • <函數名>：指定自定義函數的名稱。注意，自定義函數不能與儲存過程具有相同的名稱。
  • <引數><型別>：用於指定自定義函數的引數。這裡的引數只有名稱和型別，不能指定關鍵字 IN、OUT 和 INOUT。
  • RETURNS<型別>：用於宣告自定義函數返回值的資料型別。其中，<型別>用於指定返回值的資料型別。
  • <函數主體>：自定義函數的主體部分，也稱函數體。所有在儲存過程中使用的 SQL 語句在自定義函數中同樣適用，包括前面所介紹的區域性變數、SET 語句、流程控制語句、游標等。除此之外，自定義函數體還必須包含一個 RETURN<值> 語句，其中<值>用於指定自定義函數的返回值。

在 RETURN VALUE 語句中包含 SELECT 語句時，SELECT 語句的返回結果只能是一行且只能有一列值。

若要檢視資料庫中存在哪些自定義函數，可以使用 SHOW FUNCTION STATUS 語句；若要檢視資料庫中某個具體的自定義函數，可以使用 SHOW CREATE FUNCTION<函數名> 語句，其中<函數名>用於指定該自定義函數的名稱。

【範例 1】建立儲存函數，名稱為 StuNameById，該函數返回 SELECT 語句的查詢結果，數值型別為字串型別，輸入的 SQL 語句和執行結果如下所示。 
mysql> CREATE FUNCTION StuNameById()
    -> RETURNS VARCHAR(45)
    -> RETURN
    -> (SELECT name FROM tb_students_info
    -> WHERE id=1);
Query OK， 0 rows affected (0.09 sec)

注意：當使用 DELIMITER 命令時，應該避免使用反斜槓“”字元，因為反斜槓是 MySQL 的跳脫字元。

成功建立自定義函數後，就可以如同呼叫系統內建函數一樣，使用關鍵字 SELECT 呼叫使用者自定義的函數，語法格式為：

SELECT <自定義函數名> ([<引數> [,...]])

【範例 2】呼叫自定義函數 StuNameById，檢視函數的執行結果，如下所示。 
mysql> SELECT StuNameById();
+---------------+
| StuNameById() |
+---------------+
| Dany          |
+---------------+
1 row in set (0.24 sec)

修改自定義函數

可以使用 ALTER FUNCTION 語句來修改自定義函數的某些相關特徵。若要修改自定義函數的內容，則需要先刪除該自定義函數，然後重新建立。

刪除自定義函數

自定義函數被建立後，一直儲存在資料庫伺服器上以供使用，直至被刪除。刪除自定義函數的方法與刪除儲存過程的方法基本一樣，可以使用 DROP FUNCTION 語句來實現。

語法格式如下：

DROP FUNCTION [ IF EXISTS ] <自定義函數名>

語法說明如下。
<自定義函數名>：指定要刪除的自定義函數的名稱。
IF EXISTS：指定關鍵字，用於防止因誤刪除不存在的自定義函數而引發錯誤。

【範例 3】刪除自定義函數 StuNameById，檢視函數的執行結果，如下所示。 
mysql> DROP FUNCTION StuNameById;
Query OK, 0 rows affected (0.09 sec)
mysql> SELECT StuNameById();
ERROR 1305 (42000): FUNCTION test_db.StuNameById does not exist