MySQL Stored Function

摘要：在本教程中，您将学习如何使用CREATE FUNCTION语句创建存储函数。

存储函数是一种特殊类型的存储程序，它返回单个值。通常，您使用存储函数来封装可在 SQL 语句或存储程序之间重用的通用公式或业务规则。

与存储过程不同，只要在 SQL 语句中使用表达式，就可以使用存储函数。这有助于提高过程代码的可读性和可维护性。

要创建存储函数，请使用CREATE FUNCTION语句。

MySQL CREATE FUNCTION语法

下面说明了创建新存储函数的基本语法：

DELIMITER $$

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

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

在这个语法中：

首先，在CREATE FUNCTION关键字后指定要创建的存储函数的名称。

其次，在括号内列出存储函数的所有参数，后跟函数名称。默认情况下，所有参数都是IN参数。您不能为参数指定IN 、 OUT或INOUT修饰符

第三，在RETURNS语句中指定返回值的数据类型，可以是任何有效的MySQL数据类型。

第四，使用DETERMINISTIC关键字指定函数是否是确定性的。

确定性函数对于相同的输入参数始终返回相同的结果，而非确定性函数对于相同的输入参数返回不同的结果。

如果您不使用DETERMINISTIC或NOT DETERMINISTIC ，MySQL 默认使用NOT DETERMINISTIC选项。

第五，将代码写入BEGIN END块中存储函数的主体中。在正文部分中，您需要至少指定一个RETURN语句。 RETURN语句向调用程序返回一个值。每当到达RETURN语句时，存储函数的执行就会立即终止。

MySQL CREATE FUNCTION示例

让我们以创建存储函数为例。我们将使用示例数据库中的customers表进行演示。

以下CREATE FUNCTION语句创建一个根据信用返回客户级别的函数：

DELIMITER $$

CREATE FUNCTION CustomerLevel(
	credit DECIMAL(10,2)
) 
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE customerLevel VARCHAR(20);

    IF credit > 50000 THEN
		SET customerLevel = 'PLATINUM';
    ELSEIF (credit >= 50000 AND 
			credit <= 10000) THEN
        SET customerLevel = 'GOLD';
    ELSEIF credit < 10000 THEN
        SET customerLevel = 'SILVER';
    END IF;
	-- return the customer level
	RETURN (customerLevel);
END$$
DELIMITER ;Code language: SQL (Structured Query Language) (sql)

创建函数后，您可以在 MySQL Workbench 的Functions部分下查看它：

或者您可以使用SHOW FUNCTION STATUS来查看当前classicmodels数据库中所有存储的函数，如下所示：

SHOW FUNCTION STATUS 
WHERE db = 'classicmodels';Code language: SQL (Structured Query Language) (sql)

在 SQL 语句中调用存储函数

以下语句使用CustomerLevel存储函数：

SELECT 
    customerName, 
    CustomerLevel(creditLimit)
FROM
    customers
ORDER BY 
    customerName;Code language: SQL (Structured Query Language) (sql)

在存储过程中调用存储函数

以下语句创建一个调用CustomerLevel()存储函数的新存储过程：

DELIMITER $$

CREATE PROCEDURE GetCustomerLevel(
    IN  customerNo INT,  
    OUT customerLevel VARCHAR(20)
)
BEGIN

	DECLARE credit DEC(10,2) DEFAULT 0;
    
    -- get credit limit of a customer
    SELECT 
		creditLimit 
	INTO credit
    FROM customers
    WHERE 
		customerNumber = customerNo;
    
    -- call the function 
    SET customerLevel = CustomerLevel(credit);
END$$

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

下面说明如何调用GetCustomerLevel()存储过程：

CALL GetCustomerLevel(-131,@customerLevel);
SELECT @customerLevel;Code language: SQL (Structured Query Language) (sql)

需要注意的是，如果存储函数包含从表中查询数据的 SQL 语句，那么您不应该在其他 SQL 语句中使用它；否则，存储的函数会减慢查询的速度。

在本教程中，您学习了如何创建存储函数来封装常见公式或业务规则。