MySQL存储函数

时间:2019-11-20 08:52:19  来源:igfitidea点击:

简介:在本教程中,您将学习如何使用CREATE FUNCTION语句创建存储函数。

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

与存储过程不同,无论在何处使用表达式,都可以在SQL语句中使用存储函数。
这有助于提高过程代码的可读性和可维护性。

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

MySQL CREATE FUNCTION语法

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

DELIMITER $$

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

DELIMITER ;

使用以下语法:

首先,在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 ;

创建函数后,可以在MySQL Workbench中的"函数"部分下查看它:

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

SHOW FUNCTION STATUS 
WHERE db = 'classicmodels';

在SQL语句中调用存储的函数

以下语句使用CustomerLevel存储的功能:

SELECT 
    customerName, 
    CustomerLevel(creditLimit)
FROM
    customers
ORDER BY 
    customerName;

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

以下语句创建一个新的存储过程,该存储过程调用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 ;

以下说明了如何调用GetCustomerLevel()存储过程:

CALL GetCustomerLevel(-131,@customerLevel);
SELECT @customerLevel;

重要的是要注意,如果存储的函数包含从表中查询数据的SQL语句,则不应在其他SQL语句中使用它;否则,存储的函数将减慢查询速度。

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