MySQL触发器和存储过程；MySQL触发器简介、触发器创建与删除；MySQL存储过程的创建和调用、创建和调用存储函数、触发器和存储过程和存储函数的示例；

最新推荐文章于 2024-03-03 23:20:30 发布

小陈陈一

最新推荐文章于 2024-03-03 23:20:30 发布

阅读量806

点赞数 2

分类专栏： MySQL数据库文章标签： mysql 数据库 database 运维

本文链接：https://blog.csdn.net/milu_nff/article/details/124678690

版权

MySQL数据库专栏收录该内容

13 篇文章 0 订阅

订阅专栏

MySQL——触发器

1、触发器简介

触发器（trigger）是一个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，
比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。

例如，当学生表中增加了一个学生的信息时，学生的总数就应该同时改变。因此可以针对学生表创建一个触发器，每次增加一个学生记录时，就执行一次学生总数的计算操作，从而保证学生总数与记录数的一致性。

触发器（trigger）是个特殊的存储过程，不同的是执行存储过程要使用CALL语句来调用，
而触发器的执行不需要使用CALL语句来调用，也不需要手工启动，
只要当一个预定义的事件发生的时候，就会被MySQL自动调用。
触发程序的优点如下：
（1）触发程序的执行是自动的。当对触发程序相关表的数据做出相应的修改后立即执行。
（2）触发程序可以通过数据库中相关的表进行层叠修改另外的表。
（3）触发程序可以实施比FOREIGN KEY约束、CHECK约束更为复杂的检查和操作。

2、创建触发器

语法：
   CREATE TRIGGER 触发器名称 BEFORE|AFTER 触发事件
   ON 表名 FOR EACH ROW
   BEGIN
		  触发器程序体;
   END

 <触发器名称>  	最多64个字符，它和MySQL中其他对象的命名方式一样
 { BEFORE | AFTER } 	      触发器时机
 { INSERT | UPDATE | DELETE } 触发的事件
 ON <表名称>  	标识建立触发器的表名，即在哪张表上建立触发器
 FOR EACH ROW 触发器的执行间隔：
 FOR EACH ROW子句通知触发器 每隔一行执行一次动作，而不是对整个表执行一次
 <触发器程序体>  要触发的SQL语句：可用顺序，判断，循环等语句实现一般程序需要的逻辑功能

创建触发器的示例

示例1：
1. 创建表
mysql> create table student(
    	-> id int unsigned auto_increment primary key not null,
    	-> name varchar(50)
    	-> );
mysql> insert into student(name) values('jack');

mysql> create table student_total(total int);
mysql> insert into student_total values(1);

2. 创建触发器student_insert_trigger
mysql> delimiter $$
mysql> create trigger student_insert_trigger after insert
    -> on student for each row
    -> BEGIN
    ->     update student_total set total=total+1;
    -> END$$
mysql> delimiter ;

3. 创建触发器student_delete_trigger
mysql> delimiter $$
mysql> create trigger student_delete_trigger after delete
    -> on student for each row
    -> BEGIN
    ->     update student_total set total=total-1;
    -> END$$
mysql> delimiter ;

查看触发器
1. 通过SHOW TRIGGERS语句查看
SHOW TRIGGERS\G

2. 通过系统表triggers查看
USE information_schema
SELECT * FROM triggers\G
SELECT * FROM triggers WHERE TRIGGER_NAME='触发器名称'\G

删除触发器

通过DROP TRIGGERS语句删除
DROP TRIGGER 解发器名称

示例二：

创建表tab1
   DROP TABLE IF EXISTS tab1;
   CREATE TABLE tab1(
    	id int primary key auto_increment,
    	name varchar(50),
    	sex enum('m','f'),
    	age int
   );

创建表tab2
   DROP TABLE IF EXISTS tab2;
   CREATE TABLE tab2(
    	id int primary key auto_increment,
    	name varchar(50),
    	salary double(10,2)
   );

触发器tab1_after_delete_trigger   
作用：tab1表删除记录后，自动将tab2表中对应记录删除
mysql> \d $$
mysql> create trigger tab1_after_delete_trigger
    ->     after delete on tab1 
    ->         for each row
    -> BEGIN
    ->     delete from tab2 where name=old.name;
    -> END$$

触发器tab1_after_update_trigger   
作用：当tab1更新后，自动更新tab2
mysql> create trigger tab1_after_update_trigger
    ->      after update on tab1
    ->           for each row
    -> BEGIN
    ->      update tab2 set name=new.name
    ->           where name=old.name;
    -> END$$
Query OK, 0 rows affected (0.19 sec)  

触发器tab1_after_insert_trigger   
作用：当tab1增加记录后，自动增加到tab2
mysql> create trigger tab1_after_insert_trigger
    ->      after insert on tab1
    ->           for each row
    -> BEGIN
    ->      insert  into tab2(name,salary) values(new.name,5000);
    -> END$$
Query OK, 0 rows affected (0.19 sec)

存储过程和函数

概述：
    存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合。
    存储过程和函数的区别：
        • 函数必须有返回值，而存储过程没有。
        • 存储过程的参数可以是IN、OUT、INOUT类型，函数的参数只能是IN
    优点：
        • 存储过程只在创建时进行编译；
       	• 而SQL语句每执行一次就编译一次，所以使用存储过程可以提高数据库执行速度。
        • 简化复杂操作，结合事务一起封装。
        • 复用性好
        • 安全性高，可指定存储过程的使用权。
    说明：
        并发量少的情况下，很少使用存储过程。
        并发量高的情况下，为了提高效率，用存储过程比较多。

1、存储过程创建与调用

  创建存储过程语法 ：
    create procedure sp_name(参数列表)
        [特性...]过程体

    存储过程的参数形式：[IN | OUT | INOUT]参数名 类型
        IN        输入参数
        OUT       输出参数
        INOUT    输入输出参数

    delimiter $$
    create procedure 过程名(参数列表)
    begin
          SQL语句
    end $$
    delimiter ;

    调用：
    call 存储过程名(实参列表)

存储过程三种参数类型：IN, OUT, INOUT：

2、查看存储过程

存储过程创建好以后，用户可以通过SHOW PROCEDURE STATUS语句
或SHOW CREATE PROCEDURE 语句来查看存储过程的状态信息，
也可以通过information_schema数据库中进行查询，下面介绍这三种方法。
(1)使用SHOW PROCEDURE STATUS语句查看存储过程的状态，语法格式如下：
SHOW PROCEDURE  STATUS [LIKE 'pattern']
(2)SHOW CREATE PROCEDURE查看存储过程的信息，语法格式为：
SHOW CREATE PROCEDURE  sp_name
(3)通过INFORMATION_SCHEMA.ROUTINES查看存储过程的信息。

3、存储函数创建和调用

MySQL存储函数（自定义函数），函数一般用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数。

1> 创建存储函数

在MySQL中，创建存储函数使用CREATE FUNCTION关键字，其基本形式如下：

CREATE FUNCTION func_name ([param_name type[,...]])
RETURNS type
[characteristic ...] 
BEGIN
	routine_body
END;
参数说明：
（1）func_name ：存储函数的名称。
（2）param_name type：可选项，指定存储函数的参数。type参数用于指定存储函数的参数类型，该类型可以是MySQL数据库中所有支持的类型。
（3）RETURNS type：指定返回值的类型。
（4）characteristic：可选项，指定存储函数的特性。
（5）routine_body：SQL代码内容。

2> 调用存储函数

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法基本相同。用户自定义的存储函数与MySQL内部函数性质相同。区别在于，存储函数是用户自定义的。而内部函数由MySQL自带。其语法结构如下：

SELECT func_name([parameter[,…]]);

4、存储函数示例

重点提示

MySQL开启bin-log后，调用存储过程或者函数以及触发器时，会出现错误号为1418的错误:
在MySQL中创建函数时出现这种错误的解决方法：
方法1：第一种是在创建子程序(存储过程、函数、触发器)时，声明为DETERMINISTIC或NO SQL与READS SQL DATA中的一个，例如: CREATE DEFINER = CURRENT_USER PROCEDURE `NewProc `() DETERMINISTIC BEGIN #Routine body goes here… END;;
方法2：第二种是信任子程序的创建者，禁止创建、修改子程序时对SUPER权限的要求，设置log_bin_trust_routine_creators全局系统变量为1。
（1）在客户端上执行 SET GLOBAL log_bin_trust_function_creators = 1。
（2）MySQL启动时，加上–log-bin-trust-function-creators选贤，参数设置为1。
（3）在MySQL配置文件my.ini或my.cnf中的[mysqld]段上加log-bin-trust-function-creators=1。

示例：

1、无参有返回值
# 统计emp表中员工个数
mysql> \d $
mysql> CREATE FUNCTION myf1()
    -> RETURNS int
    -> BEGIN
    -> DECLARE c INT DEFAULT 0;
    -> SELECT COUNT(1) INTO c FROM emp;
    -> RETURN c;
    -> END $
Query OK, 0 rows affected (0.00 sec)

mysql> \d;
mysql> select myf1();
+--------+
| myf1() |
+--------+
|     15 |
+--------+
1 row in set (0.05 sec)

2、有参有返回值
示例1：根据员工名返回工资
mysql> \d $
mysql> CREATE FUNCTION myf2(empName varchar(20))
    -> RETURNS INT
    -> BEGIN
    -> DECLARE sal INT;
    -> SELECT sai INTO sal FROM emp
    -> WHERE ename=empName;
    -> RETURN sal;
    -> END $
Query OK, 0 rows affected (0.00 sec)

mysql> \d;
mysql> select myf2('刘备');
+----------------+
| myf2('刘备')   |
+----------------+
|          29750 |
+----------------+
1 row in set (0.00 sec)

示例2：根据部门编号，返回平均工资
mysql> \d $
mysql> CREATE FUNCTION myf3(d_No int)
    -> RETURNS DOUBLE
    -> BEGIN
    -> DECLARE avg_sal DOUBLE;
    -> SELECT AVG(sai) INTO avg_sal FROM emp
    -> WHERE deptno=d_No;
    -> RETURN avg_sal;
    -> END $
Query OK, 0 rows affected (0.00 sec)
mysql> \d ;
mysql> select myf3(20);
+----------+
| myf3(20) |
+----------+
|    21750 |
+----------+
1 row in set (0.00 sec)

5、修改存储函数

MySQL中，通过ALTER FUNCTION 语句来修改存储函数，其语法格式如下：

ALTER FUNCTION func_name [characteristic ...]
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

上面这个语法结构是MySQL官方给出的，修改的内容可以包SQL语句也可以不包含

6、删除存储函数

查看存储函数

MySQL存储了存储函数的状态信息，
用户可以使用SHOW STATUS语句或SHOW CREATE语句来查看，
也可使直接从系统的information_schema数据库中查询。
SHOW STATUS语句可以查看存储过程和函数的状态，其基本语法结构如下：
SHOW FUNCTION STATUS [LIKE ‘pattern’]
这个语句是一个MySQL的扩展。它返回子程序的特征，如数据库，名字，类型，创建者及创建和修改日期。

MySQL中使用DROP FUNCTION语句来删除存储函数。

**示例：**删除存储函数。

DROP FUNCTION IF EXISTS func_user;

小陈陈一

关注

2
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
MySQL触发器和存储过程；MySQL触发器简介、触发器创建与删除；MySQL存储过程的创建和调用、创建和调用存储函数、触发器和存储过程和存储函数的示例；

目录MySQL——触发器1、触发器简介2、创建触发器创建触发器的示例删除触发器存储过程和函数1、存储过程创建与调用2、查看存储过程3、存储函数创建和调用1> 创建存储函数2> 调用存储函数4、存储函数示例5、修改存储函数6、删除存储函数MySQL——触发器1、触发器简介触发器（trigger）是一个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整
复制链接

扫一扫