
数据库基础(12) . 自定义函数
BEGIN-- 函数体-- 执行计算并返回结果END;
在 MySQL 中,你可以创建自定义函数来扩展其内置功能。自定义函数可以通过编写存储过程或函数来实现。这里我们将重点介绍如何创建自定义函数,并给出一些示例。
1.语法格式
1.1.创建自定义函数的基本语法
创建自定义函数的基本语法如下:
CREATE FUNCTION function_name(args)
RETURNS return_type
BEGIN
-- 函数体
-- 执行计算并返回结果
RETURN result;
END;
-
CREATE FUNCTION function_name(args)
:- 这一行声明了函数的开始。
CREATE FUNCTION
是用来创建一个新函数的关键字。 function_name
是你为函数指定的名字,它应该见名知意,以便于理解函数的目的或行为。(args)
定义了传递给函数的参数列表。每个参数都应有名称和数据类型,例如(arg1 INT, arg2 VARCHAR(50))
。如果函数不需要参数,你可以留空括号()
。
- 这一行声明了函数的开始。
-
RETURNS return_type
:RETURNS
关键字指定了函数将返回的数据类型。return_type
应该替换为实际的数据类型,比如INT
,DECIMAL
,VARCHAR
, 等等。这告诉 MySQL 和调用者函数将返回什么类型的值。
-
BEGIN
:BEGIN
标记函数体的开始。在BEGIN
和END
之间的所有内容都是函数的主体,包含了执行逻辑的 SQL 语句或命令。
-
RETURN result;
:RETURN
语句用于从函数中返回一个值。result
是要返回的实际值,它可以是变量、表达式或者直接的值。这个值必须与RETURNS
子句中指定的数据类型兼容。
-
END;
:END
标记函数定义的结束。注意,如果你改变了语句分隔符(例如使用DELIMITER $$
),你需要用新的分隔符(如$$
)来结束函数定义,并在完成后恢复默认分隔符(DELIMITER ;
)。
1.2.注意事项
- 在创建复杂的函数时,可能需要使用更复杂的逻辑结构,如条件语句 (
IF
,CASE
)、循环 (LOOP
,WHILE
,REPEAT
) 和异常处理 (DECLARE ... HANDLER
)。 - 如果函数体内包含多个语句,则通常会使用
BEGIN...END
块来封装这些语句。 - 如果函数涉及到对数据库的操作(如查询、插入、更新或删除),则需要在
CREATE FUNCTION
语句中指定适当的特性,例如READS SQL DATA
或MODIFIES SQL DATA
。 - 要确保在创建函数之前更改语句分隔符(例如
DELIMITER $$
),并且在创建完函数后将其改回(DELIMITER ;
),以避免由于函数体内存在分号而引起的解析错误。
2.示例
2.1.示例:创建一个简单的数学函数
假设我们要创建一个自定义函数来计算两个整数的和。
示例:计算两个整数的和
DELIMITER //
CREATE FUNCTION add_two_numbers(num1 INT, num2 INT)
RETURNS INT
BEGIN
DECLARE result INT;
SET result = num1 + num2;
RETURN result;
END //
DELIMITER ;
-- 测试函数
SELECT add_two_numbers(5, 3); -- 输出 8
2.2.示例:创建一个字符串处理函数
接下来,我们创建一个函数来处理字符串,例如拼接两个字符串。
示例:拼接两个字符串
DELIMITER //
CREATE FUNCTION concatenate_strings(str1 VARCHAR(255), str2 VARCHAR(255))
RETURNS VARCHAR(510)
BEGIN
DECLARE result VARCHAR(510);
SET result = CONCAT(str1, ' ', str2);
RETURN result;
END//
DELIMITER ;
-- 测试函数
SELECT concatenate_strings('Hello', 'World'); -- 输出 'Hello World'
2.3.示例:创建一个日期处理函数
我们还可以创建一个函数来处理日期,例如计算两个日期之间的天数差。
示例:计算两个日期之间的天数差
DELIMITER //
CREATE FUNCTION days_between_dates(date1 DATE, date2 DATE)
RETURNS INT
BEGIN
DECLARE days_diff INT;
SET days_diff = DATEDIFF(date1, date2);
RETURN days_diff;
END//
DELIMITER ;
-- 测试函数
SELECT days_between_dates('2024-01-01', '2024-12-31'); -- 输出 364
2.4.示例:使用条件语句的函数
假设我们需要创建一个函数来判断一个数字是否为偶数,并返回相应的布尔值。
示例:判断一个数字是否为偶数
DELIMITER //
CREATE FUNCTION is_even(num INT)
RETURNS BOOLEAN
BEGIN
DECLARE result BOOLEAN;
IF num % 2 = 0 THEN
SET result = TRUE;
ELSE
SET result = FALSE;
END IF;
RETURN result;
END//
DELIMITER ;
-- 测试函数
SELECT is_even(4); -- 输出 1 (表示 true)
SELECT is_even(5); -- 输出 0 (表示 false)
2.5.示例:使用循环的函数
我们还可以创建一个函数来计算斐波那契数列中的第 n 个数。
示例:计算斐波那契数列中的第 n 个数
DELIMITER //
CREATE FUNCTION fibonacci(n INT)
RETURNS INT
BEGIN
DECLARE a INT DEFAULT 0;
DECLARE b INT DEFAULT 1;
DECLARE i INT DEFAULT 2;
IF n = 0 THEN RETURN a; END IF;
IF n = 1 THEN RETURN b; END IF;
WHILE i <= n DO
SET a = b - a;
SET b = b + a;
SET i = i + 1;
END WHILE;
RETURN b;
END//
DELIMITER ;
-- 测试函数
SELECT fibonacci(10); -- 输出 55
2.6.使用 READS SQL DATA
的函数
当一个函数执行查询但不改变任何表中的数据时,应该将其声明为 READS SQL DATA
。这类函数通常用于计算、汇总或其他需要从数据库读取信息的操作。
示例 1:计算员工平均工资
假设我们有一个名为 employees
的数据库,其中包含一个 salaries
表,记录了每个员工的工资信息。我们可以创建一个函数来计算特定部门的平均工资。
DELIMITER //
CREATE FUNCTION get_avg_salary(dept_id INT)
RETURNS DECIMAL(10,2)
READS SQL DATA
BEGIN
DECLARE avg_salary DECIMAL(10,2);
SELECT AVG(salary) INTO avg_salary
FROM salaries s
JOIN employees e ON s.emp_no = e.emp_no
WHERE e.department_id = dept_id;
RETURN avg_salary;
END//
DELIMITER ;
应用场景:
- 这个函数可以在报告生成器中使用,以提供不同部门的平均薪资信息。
- 它也可以作为其他存储过程或查询的一部分,用来获取动态的数据点。
示例 2:查找客户订单总数
如果我们有一个电子商务系统,其中有一个 orders
表,记录了客户的订单信息,我们可以创建一个函数来查找特定客户的订单总数。
DELIMITER //
CREATE FUNCTION get_order_count(customer_id INT)
RETURNS INT
READS SQL DATA
BEGIN
DECLARE order_count INT;
SELECT COUNT(*) INTO order_count
FROM orders
WHERE customer_id = customer_id;
RETURN order_count;
END//
DELIMITER ;
应用场景:
- 此函数可用于客户服务界面,帮助客服人员快速了解某个客户的购买频率。
- 它还可以用于构建个性化推荐引擎,根据用户的购买行为提供服务。
2.7.使用 MODIFIES SQL DATA
的函数
如果一个函数会修改数据库中的数据(如插入、更新或删除记录),则应将其声明为 MODIFIES SQL DATA
。这类函数通常用于业务逻辑处理,比如用户注册、订单处理等。
示例 1:增加库存数量
在一个库存管理系统中,我们可能需要一个函数来增加指定商品的库存数量。
DELIMITER //
CREATE FUNCTION increase_stock(product_id INT, quantity INT)
RETURNS VARCHAR(50)
MODIFIES SQL DATA
BEGIN
UPDATE products SET stock_quantity = stock_quantity + quantity
WHERE id = product_id;
IF ROW_COUNT() > 0 THEN
RETURN CONCAT('Updated stock for product ', product_id);
ELSE
RETURN 'Product not found';
END IF;
END//
DELIMITER ;
应用场景:
- 在仓库管理应用中,这个函数可以用于处理进货流程,确保库存数据准确无误。
- 它也可以集成到自动化系统中,例如当供应商发货通知到达时自动更新库存。
示例 2:用户积分更新
假设我们的应用程序有用户积分系统,每当用户完成某些活动时,他们的积分就会增加。我们可以创建一个函数来更新用户的积分。
DELIMITER //
CREATE FUNCTION update_user_points(user_id INT, points INT)
RETURNS VARCHAR(50)
MODIFIES SQL DATA
BEGIN
UPDATE users SET points = points + points
WHERE id = user_id;
IF ROW_COUNT() > 0 THEN
RETURN CONCAT('Updated points for user ', user_id);
ELSE
RETURN 'User not found';
END IF;
END//
DELIMITER ;
应用场景:
- 在游戏或社区平台中,此函数可以用于奖励用户的行为,如登录、分享内容等。
- 它还可以用于促销活动期间,根据用户的参与情况给予额外积分。
3.创建存储函数
3.1.什么是存储函数
在 MySQL 中,当你定义一个存储函数时,可以指定 DETERMINISTIC
属性。这个属性表明该函数对于相同的输入总是返回相同的结果。具体来说:
-
DETERMINISTIC
:如果一个函数被标记为DETERMINISTIC
,这意味着对于给定的输入参数,无论调用多少次,它都将返回相同的结果。换句话说,函数的行为是完全可预测的,不会受到全局变量、系统时间、数据库状态等外部因素的影响。 -
NOT DETERMINISTIC
(默认):如果函数的行为不是确定性的,即对于相同的输入可能会有不同的输出结果,则应使用NOT DETERMINISTIC
。这可能是因为函数依赖于某些外部因素,如当前时间 (NOW()
)、随机数生成器 (RAND()
) 或者会改变数据库状态的操作。
为什么要在 MySQL 函数中指明 DETERMINISTIC
或 NOT DETERMINISTIC
?
-
优化:MySQL 可以利用这个信息来优化查询。例如,在执行重复查询时,如果 MySQL 知道函数是确定性的,它可以缓存结果,从而提高性能。
-
复制:在主从复制环境中,MySQL 使用这个属性来确保在主服务器和从服务器上应用相同的数据变更。如果一个函数是非确定性的,那么在主服务器和从服务器上可能产生不同的结果,这会导致数据不一致。
-
唯一约束和索引:当函数用于计算唯一键或索引值时,确定性是非常重要的。如果函数是非确定性的,可能会导致违反唯一约束或索引失效的问题。
-
触发器和约束:当函数用于触发器或检查约束时,它的行为也应该是确定性的,以确保数据库的一致性和完整性。
因此,在创建存储函数时,正确地声明其是否为 DETERMINISTIC
对于确保数据库系统的正确性和性能非常重要。如果你不确定你的函数是不是对于同样的输入总是返回同样的结果,那么更安全的做法是指定为 NOT DETERMINISTIC
。
3.2.如何创建存储函数
在 MySQL 中创建存储函数,你需要使用 CREATE FUNCTION
语句。存储函数与存储过程类似,但是它们返回一个单一的值,并且可以在 SQL 查询中像内置函数一样调用。以下是创建存储函数的基本语法和步骤:
基本语法
DELIMITER $$
CREATE FUNCTION function_name (parameter_list)
RETURNS datatype
[NOT] DETERMINISTIC
[LANGUAGE SQL]
[CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA]
BEGIN
-- 函数体
-- 可以包含声明、变量、逻辑控制语句等
RETURN expression;
END$$
DELIMITER ;
解释各个部分
-
**DELIMITER ∗ ∗ :更改语句结束符为 ‘ **:更改语句结束符为 ` ∗∗:更改语句结束符为‘
,这是因为存储函数体内可能包含分号(
;`),如果不改变结束符,MySQL 将无法正确解析整个函数定义。 -
CREATE FUNCTION function_name (parameter_list):指定函数名称和参数列表。每个参数必须有一个名称和数据类型。
-
RETURNS datatype:指定函数返回值的数据类型。
-
[NOT] DETERMINISTIC:指定函数是确定性的还是非确定性的。如前所述,这影响到查询优化和复制行为。
-
[LANGUAGE SQL]:这个选项是可选的,默认就是 SQL,表示函数体是由 SQL 语句构成。
-
[CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA]:这些属性描述了函数是否包含 SQL 语句,以及它如何与数据库交互。例如:
NO SQL
表示函数不包含任何 SQL 语句。READS SQL DATA
表示函数读取但不修改数据。MODIFIES SQL DATA
表示函数可以修改数据。
-
BEGIN … END:这是函数体,包含了实现函数逻辑的 SQL 语句或程序代码。你可以在此处声明变量、定义条件和循环结构等。
-
RETURN expression:用于指定函数的返回值。
-
DELIMITER ;:将结束符改回默认的分号(
;
)。
示例
下面是一个简单的例子,创建一个名为 calculate_tax
的函数,它接受一个金额作为参数并返回计算后的税额:
DELIMITER $$
CREATE FUNCTION calculate_tax(amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE tax_rate DECIMAL(5,2) DEFAULT 0.07; -- 假设税率为7%
RETURN amount * tax_rate;
END$$
DELIMITER ;
一旦创建了函数,你就可以在 SQL 查询中像使用内置函数一样使用它,例如:
SELECT product_name, price, calculate_tax(price) AS tax_amount
FROM products;
请注意,在某些 MySQL 版本中,创建或修改存储函数可能需要特定的权限。如果你遇到权限问题,请确保你的用户账户有足够的权限来执行这些操作。
4.其它操作
4.1.查询函数信息
要查询数据库中现有的函数,可以使用以下几种方法:
4.1.1.使用 SHOW FUNCTION STATUS
命令
这个命令会列出所有函数的状态信息。
SHOW FUNCTION STATUS;
如果只想查看特定数据库中的函数,可以在命令后加上 WHERE Db = 'database_name'
。
4.1.2.从 information_schema.routines
表中查询
MySQL存储了所有的存储过程和函数信息在这个表中。你可以通过查询这个表来获取更详细的信息,例如函数的定义、创建时间等。
SELECT * FROM information_schema.routines
WHERE routine_type = 'FUNCTION' AND routine_schema = 'your_database';
4.1.3.使用 SHOW CREATE FUNCTION
命令
该命令用于显示特定函数的创建语句。
SHOW CREATE FUNCTION your_function_name;
4.2.删除函数
要删除一个已经存在的函数,可以使用 DROP FUNCTION
语句。语法如下:
DROP FUNCTION [IF EXISTS] function_name;
IF EXISTS
是可选的,如果指定并且函数不存在,则不会抛出错误;如果没有指定且函数不存在,则会报错。
例如,要删除名为 calculate_tax
的函数,可以执行以下语句:
DROP FUNCTION IF EXISTS calculate_tax;
这将删除 calculate_tax
函数,并且如果该函数不存在也不会报错。
更多推荐
所有评论(0)