MYSQL函数、存储过程、触发器

以你之姓@ 2022-02-28 09:46 494阅读 0赞

MySQL自定义函数（CREATE FUNCTION）

自定义函数是一种与存储过程十分相似的过程式数据库对象。它与存储过程一样，都是由 SQL 语句和过程式语句组成的代码片段，并且可以被应用程序和其他 SQL 语句调用。

自定义函数与存储过程之间存在几点区别：

  • 自定义函数不能拥有输出参数，这是因为自定义函数自身就是输出参数；而存储过程可以拥有输出参数。
  • 自定义函数中必须包含一条 RETURN 语句，而这条特殊的 SQL 语句不允许包含于存储过程中。
  • 可以直接对自定义函数进行调用而不需要使用 CALL 语句，而对存储过程的调用需要使用 CALL 语句。

创建并使用自定义函数

可以使用 CREATE FUNCTION 语句创建自定义函数。

语法格式如下：

  1. CREATE FUNCTION <函数名> ( [ <参数1> <类型1> [ , <参数2> <类型2>] ] … )
  2.   RETURNS <类型>
  3.   <函数主体>

语法说明如下：

  • <函数名>：指定自定义函数的名称。注意，自定义函数不能与存储过程具有相同的名称。
  • <参数><类型>：用于指定自定义函数的参数。这里的参数只有名称和类型，不能指定关键字 IN、OUT 和 INOUT。
  • RETURNS<类型>：用于声明自定义函数返回值的数据类型。其中，<类型>用于指定返回值的数据类型。
  • <函数主体>：自定义函数的主体部分，也称函数体。所有在存储过程中使用的 SQL 语句在自定义函数中同样适用，包括前面所介绍的局部变量、SET 语句、流程控制语句、游标等。除此之外，自定义函数体还必须包含一个 RETURN<值> 语句，其中<值>用于指定自定义函数的返回值。

在 RETURN VALUE 语句中包含 SELECT 语句时，SELECT 语句的返回结果只能是一行且只能有一列值。

  1. mysql> CREATE FUNCTION StuNameById()
  2.     -> RETURNS VARCHAR(45)
  3.     -> RETURN
  4.     -> (SELECT name FROM tb_students_info
  5.     -> WHERE id=1);
  6. Query OK， 0 rows affected (0.09 sec)

【实例 2】调用自定义函数 StuNameById，查看函数的运行结果，如下所示。

  1. mysql> SELECT StuNameById();

修改自定义函数

可以使用 ALTER FUNCTION 语句来修改自定义函数的某些相关特征。若要修改自定义函数的内容，则需要先删除该自定义函数，然后重新创建。

删除自定义函数

自定义函数被创建后，一直保存在数据库服务器上以供使用，直至被删除。删除自定义函数的方法与删除存储过程的方法基本一样，可以使用 DROP FUNCTION 语句来实现。

语法格式如下：

DROP FUNCTION [ IF EXISTS ] <自定义函数名>

在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的。然而，在创建存储过程时，存储过程体可能包含有多条 SQL 语句，这些 SQL 语句如果仍以分号作为语句结束符，那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的 SQL 语句，这样显然不行。为解决这个问题，通常可使用 DELIMITER 命令将结束命令修改为其他字符。

  1. mysql> DELIMITER //

MySQL存储过程简介

创建不带参数的存储过程

  1. mysql> DELIMITER //
  2. mysql> CREATE PROCEDURE ShowStuScore()
  3.     -> BEGIN
  4.     -> SELECT * FROM tb_students_score;
  5.     -> END //
  6. Query OK， 0 rows affected (0.09 sec)

创建带参数的存储过程

  1. mysql> DELIMITER //
  2. mysql> CREATE PROCEDURE GetScoreByStu
  3.     -> (IN name VARCHAR(30))
  4.     -> BEGIN
  5.     -> SELECT student_score FROM tb_students_score
  6.     -> WHERE student_name=name;
  7.     -> END //
  8. Query OK, 0 rows affected (0.01 sec)

创建存储过程 GetScoreByStu 后，通过 CALL 语句调用该存储过程的 SQL 语句和执行结果如下所示。

  1. mysql> DELIMITER ;
  2. mysql> CALL GetScoreByStu('Green');
  3. +---------------+
  4. | student_score |
  5. +---------------+
  6. |            99 |
  7. +---------------+
  8. 1 row in set (0.03 sec)
  9. Query OK, 0 rows affected (0.03 sec)

修改存储过程的内容和名称

修改存储过程的内容可以通过删除原存储过程，再以相同的命名创建新的存储过程。

MySQL删除存储过程（DROP PROCEDURE）

  1. DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <过程名>

MySQL触发器简介

MySQL 数据库中触发器是一个特殊的存储过程，不同的是执行存储过程要使用 CALL 语句来调用，而触发器的执行不需要使用 CALL 语句来调用，也不需要手工启动，只要一个预定义的事件发生就会被 MySQL自动调用。

在 MySQL 中，只有执行 INSERT、UPDATE 和 DELETE 操作时才能激活触发器。

在实际使用中，MySQL 所支持的触发器有三种：INSERT 触发器、UPDATE 触发器和 DELETE 触发器。

MySQL创建触发器（CREATE TRIGGER）

  1. mysql> CREATE TRIGGER SumOfSalary
  2.     -> BEFORE INSERT ON tb_emp8
  3.     -> FOR EACH ROW
  4.     -> SET @sum=@sum+NEW.salary;
  5. Query OK, 0 rows affected (0.35 sec)
  7. SET @sum=0;
  8. Query OK, 0 rows affected (0.05 sec)
  9. mysql> INSERT INTO tb_emp8
  10.     -> VALUES(1,'A',1,1000),(2,'B',1,500);
  11. Query OK, 2 rows affected (0.09 sec)
  12. Records: 2  Duplicates: 0  Warnings: 0
  13. mysql> SELECT @sum;
  14. +------+
  15. | @sum |
  16. +------+
  17. | 1500 |
  18. +------+
  19. 1 row in set (0.03 sec)

注意：删除一个表的同时，也会自动删除该表上的触发器。另外，触发器不能更新或覆盖，为了修改一个触发器，必须先删除它，再重新创建。

删除触发器

使用 DROP TRIGGER 语句可以删除 MySQL 中已经定义的触发器。

【实例】删除 double_salary 触发器，输入的 SQL 语句和执行过程如下所示。

  1. mysql> DROP TRIGGER double_salary;
  2. Query OK, 0 rows affected (0.03 sec)

发表评论

表情：
评论列表 （有 0 条评论，494人围观）

还没有评论，来说两句吧...

相关阅读

    相关 mysql触发器 存储过程

    一、触发器 MYSQL包含对触发器的支持，触发器是一种与表操作有关的数据库对象，当触发器在表上出现指定事件时，调用该对象，也就是说表的操作事件触发表上的触发器执行。 创

    小灰灰小灰灰/ 0 赞/ 445 阅读