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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
语法：
   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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
示例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 TRIGGERSG

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
创建表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)  

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
  创建存储过程语法 ：
    create procedure sp_name(参数列表)
        [特性...]过程体

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

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

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

1
2
3
4
5
6
7
8
9
存储过程创建好以后，用户可以通过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查看存储过程的信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
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代码内容。

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
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)

1
2
3
4
5
6
7
ALTER FUNCTION func_name [characteristic ...]
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

1
2
DROP FUNCTION IF EXISTS func_user;