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工作台中的"函数"部分下看到它，如下图所示:

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

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();

我们将得到如下输出。