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数据类型。
• 第四，对于相同的输入参数，如果存储的函数返回相同的结果，则认为是确定性的; 否则，存储的功能不是确定性的。您必须确定存储的函数是否是确定性的。如果声明不正确，则存储的函数可能会产生意外结果，或者不使用可用的优化会降低性能。
• 第五，你在存储函数的主体中编写代码。它可以是单个语句或复合语句。在body部分中，您必须至少指定一个RETURN语句。RETURN语句向调用者返回一个值。无论何时到达RETURN语句，存储函数的执行都会立即终止。

---

MySQL存储函数示例

让我们看一下使用存储函数的示例。我们将使用示例数据库中的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 ;

现在，我们可以CustomerLevel()在SELECT声明中调用如下：

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                     |
...

我们还重写了GetCustomerLevel()在MySQL IF语句教程中开发存储过程中，如下所示：

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 ;

如您所见，使用CustomerLevel()存储函数时比GetCustomerLevel()存储过程更具可读性  。 请注意，存储的函数仅返回单个值。如果包含SELECT不带  INTO子句的语句，则会出现错误。 另外，如果存储的函数包含SQL语句，则不应在其他SQL语句中使用它; 否则，存储的函数将降低查询的速度。 在本教程中，您学习了如何创建存储函数来封装公共公式或业务规则。

---