数据库基础(12) . 自定义函数

BEGIN-- 函数体-- 执行计算并返回结果END;

春哥的魔法书

1345人浏览 · 2024-11-10 09:08:28

春哥的魔法书 · 2024-11-10 09:08:28 发布

在 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 函数，并且如果该函数不存在也不会报错。

腾讯云开发者社区

腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。

更多推荐

李建忠：智能体正将互联网从信息网络重构为行动网络

腾讯云开发者社区

探秘 DeepSeek 落地进展，腾讯云携手业界专家共话 AI 生产力

腾讯云开发者社区

腾讯云TVP AI与安全高峰论坛圆满落幕，共探大模型时代的安全破局之道

腾讯云开发者社区

所有评论(0)

查看更多评论

春哥的魔法书

@yuanchun05

已为社区贡献4条内容

数据库基础(12) . 自定义函数

春哥的魔法书

1.语法格式

1.1.创建自定义函数的基本语法

1.2.注意事项

2.示例

2.1.示例：创建一个简单的数学函数

示例：计算两个整数的和

2.2.示例：创建一个字符串处理函数

示例：拼接两个字符串

2.3.示例：创建一个日期处理函数

示例：计算两个日期之间的天数差

2.4.示例：使用条件语句的函数

示例：判断一个数字是否为偶数

2.5.示例：使用循环的函数

示例：计算斐波那契数列中的第 n 个数

2.6.使用 READS SQL DATA 的函数

示例 1：计算员工平均工资

示例 2：查找客户订单总数

2.7.使用 MODIFIES SQL DATA 的函数

示例 1：增加库存数量

示例 2：用户积分更新

3.创建存储函数

3.1.什么是存储函数

3.2.如何创建存储函数

基本语法

解释各个部分

示例

4.其它操作

4.1.查询函数信息

4.1.1.使用 SHOW FUNCTION STATUS 命令

4.1.2.从 information_schema.routines 表中查询

4.1.3.使用 SHOW CREATE FUNCTION 命令

4.2.删除函数

所有评论(0)

春哥的魔法书

2.6.使用 `READS SQL DATA` 的函数

2.7.使用 `MODIFIES SQL DATA` 的函数

4.1.1.使用 `SHOW FUNCTION STATUS` 命令

4.1.2.从 `information_schema.routines` 表中查询

4.1.3.使用 `SHOW CREATE FUNCTION` 命令