写点什么

MySQL 存储函数及调用

作者:EquatorCoco
  • 2024-06-11
    福建
  • 本文字数:5544 字

    阅读完需:约 18 分钟

1.mysql 存储函数及调用


在 MySQL 中,存储函数(Stored Function)是一种在数据库中定义的特殊类型的函数,它可以从一个或多个参数返回一个值。存储函数在数据库层面上封装了复杂的 SQL 逻辑,使得在应用程序中调用时更加简单和高效。


下面是一个具体的 MySQL 存储函数的示例,该函数接受一个整数参数并返回该整数的平方值。


1.1 创建存储函数


首先,我们需要在 MySQL 数据库中创建一个存储函数。这里我们使用CREATE FUNCTION语句来定义存储函数。

DELIMITER //  CREATE FUNCTION square_number(num INT) RETURNS INT  BEGIN      DECLARE result INT;      SET result = num * num;      RETURN result;  END //  DELIMITER ;
复制代码


在这个示例中:


(1)DELIMITER // 和 DELIMITER ; 是用来改变 MySQL 语句的结束符的,因为存储函数的定义中可能包含多个分号,这会导致 MySQL 解释器在存储函数定义内部错误地结束语句。


(2)CREATE FUNCTION square_number(num INT) RETURNS INT 声明了一个名为square_number的存储函数,它接受一个整数参数num,并返回一个整数结果。


(3)在BEGIN ... END块中,我们定义了存储函数的主体逻辑。这里我们声明了一个变量result,并计算num的平方值,然后将结果赋值给result


(4)RETURN result; 语句将计算得到的result值返回给调用者。


1.2 调用存储函数


一旦存储函数被创建,我们就可以在 SQL 查询中像调用普通函数一样调用它。下面是一个示例,展示如何在 SELECT 语句中调用square_number函数:

sql复制代码
SELECT square_number(5); -- 返回 25
复制代码


在这个示例中,我们调用了square_number函数并传入参数值 5,函数返回了 25 作为结果。


1.3 删除存储函数


如果我们不再需要某个存储函数,可以使用DROP FUNCTION语句来删除它:

sql复制代码
DROP FUNCTION square_number;
复制代码


这个语句将删除名为square_number的存储函数。


1.4 注意事项


(1)存储函数只能返回单一的值,而存储过程(Stored Procedure)可以返回多个值或结果集。


(2)存储函数可以在 SQL 语句的任何地方调用,就像调用内置函数一样。


(3)存储函数通常用于执行计算、数据转换或复杂的业务逻辑,这些逻辑在多个地方被重复使用。


2.MySQL 存储函数的示例


以下是一些关于 MySQL 存储函数的示例,这些示例涵盖了不同的场景和功能。


2.1 示例 1:计算两个数的和


假设我们想要创建一个存储函数来计算两个数的和。

DELIMITER //  CREATE FUNCTION add_numbers(num1 INT, num2 INT) RETURNS INT  BEGIN      DECLARE sum_result INT;      SET sum_result = num1 + num2;      RETURN sum_result;  END //  DELIMITER ;    -- 调用存储函数  SELECT add_numbers(3, 5);  -- 返回 8
复制代码


2.2 示例 2:获取字符串的长度


虽然 MySQL 已经有一个内建的LENGTH()函数来获取字符串的长度,但我们可以为了示例目的创建一个类似的存储函数。

DELIMITER //  CREATE FUNCTION my_string_length(input_string VARCHAR(255)) RETURNS INT  BEGIN      RETURN LENGTH(input_string);  END //  DELIMITER ;    -- 调用存储函数  SELECT my_string_length('Hello, World!');  -- 返回 13
复制代码


2.3 示例 3:将整数转换为字符串


我们可以创建一个将整数转换为字符串的存储函数。

DELIMITER //  CREATE FUNCTION int_to_string(num INT) RETURNS VARCHAR(255)  BEGIN      RETURN CAST(num AS CHAR);  END //  DELIMITER ;    -- 调用存储函数  SELECT int_to_string(123);  -- 返回 '123'
复制代码


2.4 示例 4:判断是否为闰年


我们可以创建一个存储函数来判断一个给定的年份是否为闰年。

DELIMITER //  CREATE FUNCTION is_leap_year(year_value INT) RETURNS BOOLEAN  BEGIN      IF (year_value % 4 = 0 AND year_value % 100 != 0) OR (year_value % 400 = 0) THEN          RETURN TRUE;      ELSE          RETURN FALSE;      END IF;  END //  DELIMITER ;    -- 调用存储函数  SELECT is_leap_year(2020);  -- 返回 1(TRUE)  SELECT is_leap_year(2021);  -- 返回 0(FALSE)
复制代码


请注意,MySQL 的 BOOLEAN 类型实际上是 TINYINT(1)的一个别名,所以 TRUE 返回 1,FALSE 返回 0。


2.5 示例 5:计算字符串中某个字符的出现次数


这个示例展示了如何计算一个字符串中某个字符的出现次数。

DELIMITER //  CREATE FUNCTION char_count(input_string VARCHAR(255), search_char CHAR(1)) RETURNS INT  BEGIN      DECLARE char_count_result INT DEFAULT 0;      DECLARE str_len INT DEFAULT LENGTH(input_string);      DECLARE i INT DEFAULT 1;      WHILE i <= str_len DO          IF SUBSTRING(input_string, i, 1) = search_char THEN              SET char_count_result = char_count_result + 1;          END IF;          SET i = i + 1;      END WHILE;      RETURN char_count_result;  END //  DELIMITER ;    -- 调用存储函数  SELECT char_count('hello world', 'o');  -- 返回 2
复制代码


这些示例涵盖了从简单到稍微复杂的存储函数功能,并且应该能够帮助我们理解如何在 MySQL 中创建和使用存储函数。


3.如何在 MySQL 中创建存储函数


在 MySQL 中创建存储函数,我们需要使用CREATE FUNCTION语句。以下是一个基本的步骤和示例,说明如何创建一个简单的存储函数。


3.1 创建存储函数的步骤


(1)选择数据库(如果尚未选择):

sql复制代码
USE your_database_name;
复制代码


(2)编写存储函数:使用CREATE FUNCTION语句,指定函数名、参数、返回类型和函数体。


(3)执行创建语句:在我们的 MySQL 客户端(如 MySQL 命令行、MySQL Workbench 等)中执行上述 SQL 语句。


(3)测试存储函数:在 SELECT 语句或其他 SQL 语句中调用存储函数,以验证其是否按预期工作。


3.2 创建存储函数的示例


以下是一个简单的示例,展示如何创建一个存储函数来计算一个整数的平方:

DELIMITER //  CREATE FUNCTION square(num INT) RETURNS INT  BEGIN      RETURN num * num;  END //  DELIMITER ;
复制代码


在这个示例中:


(1)DELIMITER // 和 DELIMITER ; 用于更改默认的语句结束符,以便在函数定义中使用分号(;)。


(2)CREATE FUNCTION square(num INT) RETURNS INT 定义了一个名为square的函数,它接受一个整数参数num,并返回一个整数。


(3)在BEGIN ... END块中,我们定义了函数的主体,它返回num的平方。


3.3 调用存储函数


一旦存储函数被创建,我们就可以在 SQL 查询中像调用内置函数一样调用它:

sql复制代码
SELECT square(5); -- 返回 25
复制代码


3.4 删除存储函数


如果我们不再需要某个存储函数,可以使用DROP FUNCTION语句来删除它:

sql复制代码
DROP FUNCTION square;
复制代码


这个语句将删除名为square的存储函数。请注意,在删除存储函数之前,确保没有其他的数据库对象(如表或视图)依赖于该函数,否则可能会导致错误。


4.存储函数和函数有什么区别


在 MySQL 的上下文中,当我们提到“存储函数”(Stored Function)时,我们通常指的是在数据库中定义的、可以像内置函数一样在 SQL 查询中调用的特殊函数。这些存储函数通常用于封装复杂的 SQL 逻辑,以便在多个地方重复使用。


然而,当我们提到“函数”(Function)时,这个术语可能具有更广泛的意义,它取决于上下文。在编程和数据库领域,函数通常指的是一个可以接收输入(参数)并返回输出的代码块。这个术语可以应用于多种不同的环境和语言,包括编程语言(如 C、Java、Python 等)、数据库(如 MySQL、PostgreSQL 等)以及数学和逻辑运算。


在 MySQL 的特定上下文中,我们可以将“函数”分为两种主要类型:


(1)内置函数(Built-in Functions):这些是 MySQL 数据库管理系统提供的预定义函数,用于执行各种常见的数据库操作,如字符串操作、日期和时间计算、数值计算等。例如,CONCAT()函数用于连接两个或多个字符串,NOW()函数返回当前日期和时间。


(2)存储函数(Stored Functions):这些是用户定义的函数,它们使用CREATE FUNCTION语句在数据库中创建,并像内置函数一样在 SQL 查询中调用。存储函数封装了复杂的 SQL 逻辑,可以在多个地方重复使用,从而提高了代码的可维护性和重用性。


(3)存储函数和内置函数的区别


  • 定义者:内置函数是由数据库管理系统提供的,而存储函数是由数据库用户定义的。

  • 可定制性:内置函数的功能是固定的,用户无法更改它们的行为。而存储函数允许用户定义自己的逻辑,并根据需要进行修改。

  • 使用场景:内置函数通常用于执行常见的、简单的数据库操作。而存储函数则更适用于封装复杂的 SQL 逻辑,以便在多个地方重复使用。


(4)存储函数和存储过程(Stored Procedures)的区别


  • 返回值:存储函数必须返回一个值,而存储过程可以返回多个值或结果集,也可以不返回任何值。

  • 调用方式:存储函数可以在 SELECT 语句中直接调用,并像其他列一样在结果集中显示。而存储过程通常需要使用 CALL 语句来调用,并且它们的输出通常需要通过输出参数或结果集来获取。

  • 语法和用途:存储函数的语法类似于其他编程语言中的函数,而存储过程则更类似于一组为了完成特定功能的 SQL 语句集合。在用途上,存储函数通常用于计算或转换数据,而存储过程则更适用于执行复杂的数据库操作或事务处理。


5.触发器和函数的区别


触发器和函数在 MySQL 中有显著的不同,主要体现在以下几个方面:


(1)用途:

  • 函数(Function):通常用于封装可重复使用的逻辑,这些逻辑可以在多个地方被其他过程或触发器主动调用。函数体现的是一个独立的功能,它们封装了用于执行特定任务的代码,并返回一个值。

  • 触发器(Trigger):通常用于在数据库发生特定事件时自动执行一些逻辑。触发器与表相关联,并在表的数据发生变化(如 INSERT、UPDATE、DELETE 操作)时触发执行。它们主要用于实现数据完整性、审计、日志记录等自动化操作。


(2)触发方式:

  • 函数:可以由其他过程主动调用,是显式的执行方式。

  • 触发器:只能在特定条件下自动触发执行,是隐式的执行方式。它们不需要手动调用,而是在满足定义的条件时自动执行。


(3)执行时机:

  • 函数:在具体需要时被调用执行。

  • 触发器:在触发条件满足时自动执行。它们可以在数据库操作之前或之后触发,具体取决于触发器的定义。


(4)返回值:

  • 函数:必须返回一个值,这个值可以是任何数据类型,取决于函数的定义。

  • 触发器:不直接返回值。它们通过执行一系列的动作来影响数据库的状态,如修改表中的数据、插入数据到其他表、调用其他存储过程等。


(5)应用场景:

  • 函数:常用于封装复杂的逻辑运算、数据转换等任务,以提高代码的复用性和可维护性。

  • 触发器:常用于实现数据完整性约束、日志记录和审计、数据的备份和同步等自动化操作。它们可以在数据发生变化时自动执行相应的逻辑,以确保数据的准确性和一致性。


触发器和函数在 MySQL 中各有其独特的作用和应用场景。函数主要用于封装可重复使用的逻辑并返回结果值,而触发器则用于在数据库发生特定事件时自动执行相应的逻辑。


6.如何在 MySQL 中创建触发器


在 MySQL 中,触发器(Trigger)是一种特殊的存储过程,它会在指定的表上进行 INSERT、UPDATE 或 DELETE 操作之前或之后自动执行。要使用触发器,我们需要使用CREATE TRIGGER语句。


以下是创建触发器的基本步骤和示例:


6.1 创建触发器的步骤


(1)选择数据库(如果尚未选择):

sql复制代码
USE your_database_name;
复制代码


(2)编写触发器:使用CREATE TRIGGER语句,指定触发器的名称、触发时间(BEFORE 或 AFTER)、触发事件(INSERT、UPDATE 或 DELETE)、关联的表和要执行的 SQL 语句。


(3)执行创建语句:在我们的 MySQL 客户端(如 MySQL 命令行、MySQL Workbench 等)中执行上述 SQL 语句。


6.2 创建触发器的示例


假设我们有一个名为orders的表,每当有新订单(即插入新行)时,我们想要记录一条日志到另一个名为order_logs的表中。


首先,确保我们有order_logs表,其结构可能如下:

CREATE TABLE order_logs (      log_id INT AUTO_INCREMENT PRIMARY KEY,      order_id INT,      log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,      action VARCHAR(50) NOT NULL  );
复制代码


然后,我们可以创建一个触发器,当在orders表中插入新行时,向order_logs表中插入一条日志记录:

DELIMITER //  CREATE TRIGGER after_order_insert  AFTER INSERT ON orders  FOR EACH ROW  BEGIN      INSERT INTO order_logs (order_id, action) VALUES (NEW.order_id, 'Order inserted');  END;  //  DELIMITER ;
复制代码


在这个示例中:

  • after_order_insert 是触发器的名称。

  • AFTER INSERT ON orders 指定触发器在orders表上插入新行之后触发。

  • FOR EACH ROW 表示触发器会对受影响的每一行执行一次。

  • BEGIN ... END块中,我们定义了触发器要执行的 SQL 语句,即向order_logs表中插入一条记录。注意,我们使用NEW.order_id来引用新插入的orders表中的order_id值。


6.3 注意事项


  • 触发器中的 SQL 语句通常是对数据表进行增删改查的操作,但不能调用存储过程。

  • 触发器中的 SQL 语句可以引用被触发表中的列,但只能引用被触发行的列(使用NEWOLD关键字,具体取决于触发时间)。

  • 触发器是基于表的,不是基于整个数据库的。每个触发器只能和一个表关联。

  • 触发器不能通过直接调用执行,只能通过触发事件隐式触发。

  • 如果触发器中的 SQL 语句执行失败,那么原始操作(INSERT、UPDATE 或 DELETE)也会被回滚。

  • 触发器可以嵌套使用,即一个触发器可以调用另一个触发器,但嵌套的最大层数取决于 MySQL 服务器的配置。


6.4 删除触发器


如果我们不再需要某个触发器,可以使用DROP TRIGGER语句来删除它:

sql复制代码
DROP TRIGGER after_order_insert;
复制代码


这个语句将删除名为after_order_insert的触发器。请注意,删除触发器时不需要指定数据库名称,只需要指定触发器的名称。


文章转载自:TechSynapse

原文链接:https://www.cnblogs.com/TS86/p/18240719

体验地址:http://www.jnpfsoft.com/?from=infoq

用户头像

EquatorCoco

关注

还未添加个人签名 2023-06-19 加入

还未添加个人简介

评论

发布
暂无评论
MySQL 存储函数及调用_MySQL_EquatorCoco_InfoQ写作社区