MySQL儲存函式

2019-10-16 22:56:35

在本教學中,您將學習如何使用CREATE FUNCTION語句建立儲存的函式。

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

儲存過程不同,您可以在SQL語句中使用儲存的函式,也可以在表示式中使用。 這有助於提高程式程式碼的可讀性和可維護性。

MySQL儲存函式語法

以下說明了建立新儲存函式的最簡單語法:

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

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

MySQL儲存函式範例

我們來看一下使用儲存函式的例子,這裡將使用範例資料庫(yiibaidb)中的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語句中使用它; 否則,儲存的函式將減慢查詢的速度。


以下是糾正/補充內容:

RETURNS子句指示函式返回值的型別為{STRINGINTEGERREALDECIMAL}  提交時間:2019-09-10