MySQL 存储函数

一、MySQL 存储函数 语法

MySQL 中的存储函数是一组 SQL 语句，它们执行某些任务/操作并返回单个值。它是 MySQL 中存储程序的类型之一。当您将创建存储函数时，请确保您具有 CREATE ROUTINE 数据库权限。通常，我们使用这个函数来封装可在存储程序或SQL语句中复用的常见业务规则或公式。存储函数与MySQL中的过程几乎相似，但有一些不同之处如下：

• 函数参数可以只包含IN参数但不能指定该参数，而过程可以允许IN、OUT、INOUT参数。
• 存储的函数只能返回函数头中定义的单个值。
• 存储的函数也可以在 SQL 语句中调用。
• 它可能不会产生结果集。

因此，当我们的程序的目的是仅计算和返回单个值或创建用户定义的函数时，我们将考虑存储函数。

MySQL中创建存储函数的语法如下：

DELIMITER $$  
   
CREATE FUNCTION fun_name(fun_parameter(s))  
RETURNS datatype  
[NOT] {Characteristics}  
fun_body;  

参数说明

参数名称	说明
fun_name	它是我们要在数据库中创建的存储函数的名称。它不应与 MySQL 的内置函数名称相同。
fun_parameter	它包含函数体使用的参数列表。它不允许指定 IN、OUT、INOUT 参数。
datatype	它是函数返回值的数据类型。它应该是任何有效的 MySQL 数据类型。
characteristics	只有在声明中定义了特征（DETERMINISTIC、NO SQL 或 READS SQL DATA）时，才接受 CREATE FUNCTION 语句。
fun_body	该参数有一组 SQL 语句来执行操作。它至少需要一个 RETURN 语句。执行 return 语句时，函数将自动终止。函数体如下： BEGIN -- SQL 语句 END $$ DELIMITER

二、MySQL 存储函数 示例

让我们通过示例了解存储函数在 MySQL 中是如何工作的。假设我们的数据库有一个名为“customer”的表，其中包含以下数据：

现在，我们将创建一个函数，使用以下语句根据年龄返回客户职业。

DELIMITER $$  
CREATE FUNCTION Customer_Occupation(  
    age int  
)   
RETURNS VARCHAR(20)  
DETERMINISTIC  
BEGIN  
    DECLARE customer_occupation VARCHAR(20);  
    IF age > 35 THEN  
        SET customer_occupation = 'Scientist';  
    ELSEIF (age <= 35 AND   
            age >= 30) THEN  
        SET customer_occupation = 'Engineer';  
    ELSEIF age < 30 THEN  
        SET customer_occupation = 'Actor';  
    END IF;  
    -- return the customer occupation  
    RETURN (customer_occupation);  
END$$  
DELIMITER; 

在命令行工具上执行上述语句，如下图：

函数创建成功后，我们可以在 MySQL 工作台的 Function 部分下看到它，如下图所示：

我们还可以使用以下语句查看当前数据库中可用的所有存储函数：

SHOW FUNCTION STATUS WHERE db = 'mysqltestdb';  

执行上述命令后，我们将得到如下输出：

三、存储函数调用

现在，我们将看到如何使用 SQL 语句调用存储函数。以下语句使用customer_occupation存储函数来获取结果：

SELECT name, age, Customer_Occupation(age)  
FROM customer ORDER BY age;  

它将给出如下输出。

我们还可以在另一个存储程序中调用上述函数，例如过程、函数或触发器或任何其他 MySQL 内置函数。

四、一个存储函数调用另一个存储函数

在这里，我们将看到如何在存储过程中调用此函数。此语句在使用Customer_Occupation()存储函数的数据库中创建一个过程。

DELIMITER $$  
CREATE PROCEDURE GetCustomerDetail()  
BEGIN  
    SELECT name, age, Customer_Occupation(age) FROM customer ORDER BY age;  
END$$  
DELIMITER ;  

以下语句可用于调用存储过程：

CALL GetCustomerDetail();  

我们将得到如下输出：