本文為mariadb官方手冊：CREATE FUNCTION的譯文。原文：https://mariadb.com/kb/en/library/create-function/我提交到MariaDB官方手冊的譯文：https://mariadb.com/kb/zh-cn/create-functio ...

本文為mariadb官方手冊：CREATE FUNCTION的譯文。

原文：https://mariadb.com/kb/en/library/create-function/
我提交到MariaDB官方手冊的譯文：https://mariadb.com/kb/zh-cn/create-function/

語法

CREATE [OR REPLACE]
    [DEFINER = {user | CURRENT_USER | role | CURRENT_ROLE }]
    [AGGREGATE] FUNCTION [IF NOT EXISTS] func_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...]
    RETURN func_body

func_parameter:
    param_name type

type:
    Any valid MariaDB data type

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

func_body:
    Valid SQL procedure statement

描述

可以使用CREATE FUNCTION語句創建一個新的存儲函數stored function。要使用CREATE FUNCTION語句，必須要具備CREATE ROUTINE許可權。

函數可以定義任意數量的參數，在函數體(func_body)部分會返回一個值。函數體部分可以是任意有效的SQL表達式，例如某些select語句。如果你有合適的許可權，你完全可以像調用內置函數一樣調用存儲函數。關於許可權的詳細信息，見下文：Security。

此外，你也可以使用CREATE FUNCTION語句的變體格式來安裝一個用戶自定義函數(UDF)。關於UDF，詳細信息見：CREATE FUNCTION (UDF)。

你可以使用一個圓括弧包圍SELECT作為func_body部分，正如使用子查詢一樣。但註意，SELECT語句必須返回單個值(標量值，即單行且單列的值)。調用函數時，如果SELECT語句返回了多列，則報1241的錯誤，如果SELECT語句返回了多行，則報1242的錯誤。為了保險，可以使用LIMIT子句保證只返回單行數據。

你可以使用BEGIN...END語句塊替換這裡的RETURN子句，但是在語句塊中，必須要包含一個RETURN語句。當調用函數時，執行到RETURN子句時將立即返回其結果，在RETURN子句之後的語句都不會再執行。

預設情況下，函數是關聯到預設資料庫上的。如果要將函數顯式關聯到一個指定的資料庫，可以在創建時使用全稱db_name.func_name。如果創建的存儲函數名和內置的函數名同名，則必須使用全稱來調用它。

定義存儲函數時，參數列表可以為空。如果指定參數名，則參數名不區分大小寫。

每個參數都可以聲明為任意有效的數據類型，但無法使用COLLATE屬性。

>RETURNS子句

RETURNS子句指定函數的返回類型。可以使用NULL值來表示返回任意有效數據類型。

如果RETURN子句的返回值類型和此處定義的數據類型不一致會如何？這取決於創建函數的時候，SQL_MODE的影響行為。

如果SQL_MODE為strict模式的值(即指定了STRICT_ALL_TABLES或STRICT_TRANS_TABLES)，將報1366錯誤。

除這種情況，如果返回值類型不一致，則返回值將被強制轉換為指定的數據類型。例如，RETURNS子句指定返回一個ENUM或SET數據類型，但RETURN子句返回了一個整型，則返回值將強制轉換為ENUM或SET成員對應的字元串(譯者註：雖然ENUM允許存儲數值，但強烈建議不要存儲數值，因為非常容易混淆ENUM的索引值和實際存儲的數值，因此這裡直接說是字元串)。

MariaDB將在創建routine的時候保留系統變數SQL_MODE的值，以後任何時間調用routine時都使用該SQL_MODE值，而不管當前調用routine時的SQL MODE值是什麼。

>LANGUAGE SQL

LANGUAGE SQL代表的是一個標準的SQL子句，它是為了移植性而存在的。但是，該子句在MariaDB中沒有任何意義，因為MariaDB的存儲函數中唯一支持的語言只有SQL。

>OR REPLACE

如果使用了OR REPLACE子句，它的行為等價於：

DROP FUNCTION IF EXISTS function_name;
CREATE FUNCTION function_name ...;

但不會刪除該函數已有的許可權privileges。

>IF NOT EXISTS

如果使用 IF NOT EXISTS 子句，那麼當函數存在時，MariaDB將返回一個warning信息而不是直接返回錯誤。IF NOT EXISTS不能和OR REPLACE一起使用。

>[NOT] DETERMINISTIC

如果函數根據給定的參數列表能夠返回一個確定的結果，則該函數是確定的(deterministic)。如果函數的返回值會因某些數據、變數、隨機數或任意不確定的值而受影響，則函數是不確定的。此外，如果存儲函數中使用了不確定的函數(如NOW()或CURRENT_TIMESTAMP())，則該存儲函數也是不確定的。

如果優化器知道函數是確定的，它會選擇一個更快更有效的執行計劃。你可以使用DETERMINISTIC關鍵字來定義這個routine。如果你想顯式將函數標記為不確定的(預設就是如此)，可以使用NOT DETERMINISTIC關鍵字。

如果你將一個不確定的函數聲明為DETERMINISTIC，將返回一個錯誤結果。如果你將一個確定的函數聲明為NOT DETERMINISTIC，則某些情況下，該查詢語句的性能將大幅降低。

[NOT] DETERMINISTIC子句還會影響二進位日誌binary logging，因為日誌中的語句格式無法存儲或替換不確定的語句。

CONTAINS SQL, NO SQL, READS SQL DATA 以及 MODIFIES SQL DATA是信息類的子句，它們告訴伺服器該函數是做什麼的。MariaDB不會對這些語句做任何語法檢查。如果不指定這些語句，則預設使用CONTAINS SQL。

>MODIFIES SQL DATA

MODIFIES SQL DATA意味著函數中包含了要修改資料庫中數據的語句。例如函數中使用了類似於DELETE, UPDATE, INSERT, REPLACE或DDL類的語句。

>READS SQL DATA

READS SQL DATA意味著函數中包含了從資料庫中讀取數據的語句，但是不會修改任何數據。例如函數中使用了不包含任何寫操作的SELECT語句。

>CONTAINS SQL

CONTAINS SQL意味著函數包含了至少一條SQL語句，但是它不會讀也不會寫資料庫。例如函數中包含了SET或DO子句。

>NO SQL

NO SQL意味著什麼？啥也不意味著。因為MariaDB目前除了SQL語言，不支持任何其他語言。

>Security

要想調用函數，你必須要擁有該函數的EXECUTE許可權。

MariaDB會自動為創建函數CREATE FUNCTION的用戶授予EXECUTE 和 ALTER ROUTINE許可權，即使使用了DEFINER子句。

每個函數都有一個關聯的賬號(即definer)。預設情況下，definer即為函數的創建者。可以使用DEFINER子句顯式指定關聯到其他賬號上。要使用DEFINER，你必須要擁有SUPER許可權。詳細信息見：Account Names。

SQL SECURITY子句指定了當調用函數時所使用的許可權。如果SQL SECURITY的值為INVOKER，則將使用函數調用者的許可權去對比(即評估)函數體中的語句許可權。如果SQL SECURITY的值為DEFINER，則總是使用definer用戶的許可權去評估函數體的許可權。預設值為DEFINER。

通過該子句，你可以創建一個只允許某用戶訪問部分數據的函數。例如，你有一張存儲了員工信息的表，並且你已經授予了用戶roger對該表某些列(only on certain columns)的SELECT許可權。

CREATE TABLE employees (name TINYTEXT, dept TINYTEXT, salary INT);
GRANT SELECT (name, dept) ON employees TO roger;

可以定義一個函數來獲取部門中薪水最高的用戶，並授予EXECUTE許可權：

CREATE FUNCTION max_salary (dept TINYTEXT) RETURNS INT RETURN
  (SELECT MAX(salary) FROM employees WHERE employees.dept = dept);
GRANT EXECUTE ON FUNCTION max_salary TO roger;

由於SQL SECURITY的預設值為DEFINER，無論roger用戶何時調用該函數，都會使用你的許可權來執行其中的子查詢。只要你有查詢每個員工薪水的許可權，即使函數調用者不具備直接查詢薪水的許可權，他們也能獲取到每個部門的最高薪水。

>Character sets 和 collations

可以為函數聲明使用任意有效的字元集和排序規則character set and collation。如果定義了它們，COLLATE屬性需要定義在CHARACTER SET之後。

如果沒有指定字元集和排序規則，則使用函數創建時的系統預設值。即使之後系統預設字元集和排序規則改變了，函數所使用的字元集也不會隨之改變。這種情況下，應該重建函數並使用資料庫所使用的字元集和排序規則。

示例

下麵的函數示例使用了一個參數，併在函數中執行了一個SQL內置函數CONCAT()，最後返回結果。

CREATE FUNCTION hello (s CHAR(20))
    RETURNS CHAR(50) DETERMINISTIC
    RETURN CONCAT('Hello, ',s,'!');

SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+

你可以在函數內部使用一個語句塊來操作數據(即使用DML)，例如INSERT和UPDATE。下麵的例子中創建了一個函數計數器，它使用了一個臨時表來存儲當前的值。因為語句塊包含了語句終止符號";"，因此必須首先使用DELIMITER語句改變語句的終止符，使得函數體中能夠使用分號。更多信息見Delimiters in the mysql client。

CREATE TEMPORARY TABLE counter (c INT);
INSERT INTO counter VALUES (0);
DELIMITER //
CREATE FUNCTION counter () RETURNS INT
  BEGIN
    UPDATE counter SET c = c + 1;
    RETURN (SELECT c FROM counter LIMIT 1);
  END //
DELIMITER ;

字元集和排序規則：

CREATE FUNCTION hello2 (s CHAR(20))
  RETURNS CHAR(50) CHARACTER SET 'utf8' COLLATE 'utf8_bin' DETERMINISTIC
  RETURN CONCAT('Hello, ',s,'!');

回到Linux系列文章大綱：http://www.cnblogs.com/f-ck-need-u/p/7048359.html
回到網站架構系列文章大綱：http://www.cnblogs.com/f-ck-need-u/p/7576137.html
回到資料庫系列文章大綱：http://www.cnblogs.com/f-ck-need-u/p/7586194.html
轉載請註明出處：http://www.cnblogs.com/f-ck-need-u/p/8698943.html

註：若您覺得這篇文章還不錯請點擊右下角推薦，您的支持能激發作者更大的寫作熱情，非常感謝！

翻譯：CREATE FUNCTION語句（已提交到MariaDB官方手冊）

語法

描述