MySQL存儲函數

在本教學中，您將學習如何使用CREATE FUNCTION語句創建存儲的函數。

存儲的函數是返回單個值的特殊類型的存儲程式。您使用存儲的函數來封裝在SQL語句或存儲的程式中可重用的常用公式或業務規則。

與存儲過程不同，您可以在SQL語句中使用存儲的函數，也可以在運算式中使用。 這有助於提高程式代碼的可讀性和可維護性。

MySQL存儲函數語法

以下說明了創建新存儲函數的最簡單語法：

CREATE FUNCTION function_name(param1,param2,…)
    RETURNS datatype
   [NOT] DETERMINISTIC
 statements

首先，在CREATE FUNCTION子句之後指定存儲函數的名稱。
其次，列出括弧記憶體儲函數的所有參數。 默認情況下，所有參數均為IN參數。不能為參數指定IN，OUT或INOUT修飾符。
第三，必須在RETURNS語句中指定返回值的數據類型。它可以是任何有效的MySQL數據類型。
第四，對於相同的輸入參數，如果存儲的函數返回相同的結果，這樣則被認為是確定性的，否則存儲的函數不是確定性的。必須決定一個存儲函數是否是確定性的。 如果您聲明不正確，則存儲的函數可能會產生意想不到的結果，或者不使用可用的優化，從而降低性能。
第五，將代碼寫入存儲函數的主體中。 它可以是單個語句或複合語句。 在主體部分中，必須至少指定一個RETURN語句。RETURN語句用於返回一個值給調用者。 每當到達RETURN語句時，存儲的函數的執行將立即終止。

MySQL存儲函數示例

我們來看一下使用存儲函數的例子，這裏將使用示例資料庫(zaixiandb)中的customers表進行演示。

以下示例是根據信用額度返回客戶級別的功能。 我們使用IF語句來確定信用額度。

DELIMITER $$

CREATE FUNCTION CustomerLevel(p_creditLimit double) RETURNS VARCHAR(10)
    DETERMINISTIC
BEGIN
    DECLARE lvl varchar(10);

    IF p_creditLimit > 50000 THEN
 SET lvl = 'PLATINUM';
    ELSEIF (p_creditLimit <= 50000 AND p_creditLimit >= 10000) THEN
        SET lvl = 'GOLD';
    ELSEIF p_creditLimit < 10000 THEN
        SET lvl = 'SILVER';
    END IF;

 RETURN (lvl);
END $$
DELIMITER ;

現在，我們在SELECT語句中調用CustomerLevel()存儲函數，如下所示：

SELECT
    customerName, CustomerLevel(creditLimit)
FROM
    customers
ORDER BY customerName;

執行上面查詢語句，得到以下結果 -

+------------------------------------+----------------------------+
| customerName                       | CustomerLevel(creditLimit) |
+------------------------------------+----------------------------+
| Alpha Cognac                       | PLATINUM                   |
| American Souvenirs Inc             | SILVER                     |
| Amica Models & Co.                 | PLATINUM                   |
| ANG Resellers                      | SILVER                     |
| Anna's Decorations, Ltd            | PLATINUM                   |
| Anton Designs, Ltd.                | SILVER                     |
| Asian Shopping Network, Co         | SILVER                     |
| Asian Treasures, Inc.              | SILVER                     |
| Atelier graphique                  | GOLD                       |
| Australian Collectables, Ltd       | PLATINUM                   |
| Australian Collectors, Co.         | PLATINUM                   |
|************** 此處省略了一大波數據 *********************************|
| Vitachrome Inc.                    | PLATINUM                   |
| Volvo Model Replicas, Co           | PLATINUM                   |
| Warburg Exchange                   | SILVER                     |
| West Coast Collectables Co.        | PLATINUM                   |
+------------------------------------+----------------------------+
122 rows in set

下麵，來重寫在MySQL IF語句教學中開發的GetCustomerLevel()存儲過程，如下所示：

DELIMITER $$

CREATE PROCEDURE GetCustomerLevel(
    IN  p_customerNumber INT(11),
    OUT p_customerLevel  varchar(10)
)
BEGIN
    DECLARE creditlim DOUBLE;

    SELECT creditlimit INTO creditlim
    FROM customers
    WHERE customerNumber = p_customerNumber;

    SELECT CUSTOMERLEVEL(creditlim)
    INTO p_customerLevel;
END $$
DELIMITER ;

如您所見，GetCustomerLevel()存儲過程在使用CustomerLevel()存儲函數時可讀性更高。

請注意，存儲函數僅返回單個值。 如果沒有包含INTO子句的SELECT語句，則將會收到錯誤。

另外，如果存儲的函數包含SQL語句，則不應在其他SQL語句中使用它; 否則，存儲的函數將減慢查詢的速度。

以下是糾正/補充內容：