MySQL 存储过程和函数 触发器

存储过程和函数

存储过程和函数是事先经过编译并存储在数据库的一段SQL语句的集合,调用存储过程和函数可以简化应用开发人员的工作,减少数据在数据库和应用服务器之间的传输,对提高数据处理的效率有好处。
存储过程和函数的区别:

  1. 函数必须有返回值,存储过程没有。
  2. 存储过程的参数使用IN、OUT、INOUT类型,而函数参数只能是IN类型。

存储过程和函数的相关操作

创建、修改存储过程或函数

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
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

CREATE FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body

proc_parameter:
[ IN | OUT | INOUT ] param_name type

func_parameter:
param_name type

type:
Any valid MySQL data type

characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

routine_body:
Valid SQL procedure statement or statements

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]

characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string

调用过程:

1
CALL sp_name([parameter[,......]])

MySQL存储过程和函数允许包含DDL语句,允许在存储过程中执行提交或回滚,但存储过程和函数中不允许执行LOAD DATA INFILE语句。存储过程和函数可以调用其他的过程或函数。
在执行创建过程和函数之前,通过DELIMITER语句将语句的结束符从”;”修改成其他符号。在创建过程和函数后通过”DELIMITER ;”语句再将结束符改回”;”
存储过程的好处在于处理逻辑封装在数据库端,调用者不需要了解中间的处理逻辑,一旦处理逻辑发生变化,只需要修改存储过程即可,而对调用者的程序完全没有影响。

存储过程和函数的 CREATE 语法不支持使用 CREATE OR REPLACE 对存储过程和函数进行修改,如果需要对已有的存储过程或者函数进行修改,需要执行 ALTER 语法。

characteristic特征值部分:

  1. LANGUAGE SQL:说明下面过程的 BODY 是使用 SQL 语言编写,这条是系统默认的,为今后 MySQL 会支持的除 SQL 外的其他语言支持的存储过程而准备。
  2. [NOT] DETERMINISTIC:DETERMINISTIC 确定的,即每次输入一样输出也一样的程序,NOT DETERMINISTIC 非确定的,默认是非确定的。当前,这个特征值还没有被优化程序使用。
  3. { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }:这些特征值提供子程序使用数据的内在信息,这些特征值目前只是提供给服务器,并没有根据这些特征值来约束过程实际使用数据的情况。如果这些特征没有明确给定,默认使用的值是 CONTAINS SQL。
    1. CONTAINS SQL 表示子程序不包含读或写数据的语句。
    2. NO SQL 表示子程序不包含 SQL 语句。
    3. READS SQL DATA 表示子程序包含读数据的语句,但不包含写数据的语句。
    4. MODIFIES SQL DATA 表示子程序包含写数据的语句。
  4. SQL SECURITY { DEFINER | INVOKER }:可以用来指定子程序该用创建子程序者的许可来执行,还是使用调用者的许可来执行。默认值是 DEFINER。
  5. COMMENT ‘string’:存储过程或者函数的注释信息

删除存储过程或者函数

1
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

查看存储过程或函数

查看存储过程或函数的状态

SHOW {PROCEDURE|FUNCTION} STATUS [LIKE ‘pattern’];

查看存储过程或函数的定义

SHOW CREATE {PROCEDURE|FUNCTION} sp_name;

通过系统表查看存储过程或函数信息

查看information_schema.routines表

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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
MariaDB [test]> DELIMITER $$
MariaDB [test]> CREATE PROCEDURE select_test_int (IN p_a INT, IN p_b INT)
-> READS SQL DATA
-> BEGIN
-> SELECT a, b from test_int
-> WHERE a<p_a or b>p_b;
-> END $$
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> DELIMITER ;
MariaDB [test]> call select_test_int(1, 10);
+----------+-------------+
| a | b |
+----------+-------------+
| 56457789 | 04294967295 |
| 56457789 | 04294967295 |
+----------+-------------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> call select_test_int(10, 10);
+----------+-------------+
| a | b |
+----------+-------------+
| 00001 | 00000000001 |
| 56457789 | 04294967295 |
| 00001 | 00000000001 |
| 56457789 | 04294967295 |
+----------+-------------+
4 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SHOW PROCEDURE STATUS;
+------+-----------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+-----------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| test | select_test_int | PROCEDURE | root@localhost | 2018-03-30 22:58:38 | 2018-03-30 22:58:38 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
+------+-----------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

MariaDB [test]> SHOW CREATE PROCEDURE select_test_int\G
*************************** 1. row ***************************
Procedure: select_test_int
sql_mode:
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `select_test_int`(IN p_a INT, IN p_b INT)
READS SQL DATA
BEGIN
SELECT a, b from test_int
WHERE a<p_a or b>p_b;
END
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

MariaDB [test]> DROP PROCEDURE IF EXISTS select_test_int\G
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> DELIMITER $$
MariaDB [test]> CREATE FUNCTION select_test_int_f (p_a INT, p_b INT)
-> RETURNS INT
-> BEGIN
-> DECLARE p_c INT;
-> SELECT count(1) INTO p_c from test_int
-> WHERE a<p_a or b>p_b;
-> RETURN(p_c);
-> END $$
Query OK, 0 rows affected (0.05 sec)

MariaDB [test]> DELIMITER ;
MariaDB [test]> select select_test_int_f(1, 2);
+-------------------------+
| select_test_int_f(1, 2) |
+-------------------------+
| 2 |
+-------------------------+
1 row in set (0.00 sec)

MariaDB [test]> SHOW FUNCTION STATUS;
+------+-------------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+-------------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| test | select_test_int_f | FUNCTION | root@localhost | 2018-03-30 23:14:19 | 2018-03-30 23:14:19 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
+------+-------------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

MariaDB [test]> SHOW CREATE FUNCTION select_test_int_f\G
*************************** 1. row ***************************
Function: select_test_int_f
sql_mode:
Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `select_test_int_f`(p_a INT, p_b INT) RETURNS int(11)
BEGIN
DECLARE p_c INT;
SELECT count(1) INTO p_c from test_int
WHERE a<p_a or b>p_b;
RETURN(p_c);
END
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

MariaDB [test]> select * from information_schema.routines where SPECIFIC_NAME='select_test_int_f'\G
*************************** 1. row ***************************
SPECIFIC_NAME: select_test_int_f
ROUTINE_CATALOG: def
ROUTINE_SCHEMA: test
ROUTINE_NAME: select_test_int_f
ROUTINE_TYPE: FUNCTION
DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: 10
NUMERIC_SCALE: 0
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
DTD_IDENTIFIER: int(11)
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: BEGIN
DECLARE p_c INT;
SELECT count(1) INTO p_c from test_int
WHERE a<p_a or b>p_b;
RETURN(p_c);
END
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE: SQL
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: CONTAINS SQL
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2018-03-30 23:14:19
LAST_ALTERED: 2018-03-30 23:14:19
SQL_MODE:
ROUTINE_COMMENT:
DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.01 sec)

MariaDB [test]> drop function if exists select_test_int_f;
Query OK, 0 rows affected (0.00 sec)

变量

存储过程和函数都可以使用变量,变量不区分大小写。

变量定义

通过 DECLARE 可以定义一个局部变量,该变量的作用范围只能在 BEGIN……END 块中,可以用在嵌套的块中。变量的定义必须写在复合语句的开头,并且在任何其他语句的前面。可以一次声明多个相同类型的变量。如果需要,可以使用 DEFAULT 赋默认值。
定义一个变量的语法如下:

1
DECLARE var_name[,...] type [DEFAULT value]

变量赋值

变量可以直接赋值,或者通过查询赋值。
直接赋值使用 SET,可以赋常量或者赋表达式,具体语法如下:

1
SET var_name = expr [, var_name = expr] ...

通过查询赋值:

1
SELECT col_name[,...] INTO var_name[,...] table_expr

定义条件和处理

条件定义

1
2
3
4
5
DECLARE condition_name CONDITION FOR condition_value

condition_value:
SQLSTATE [VALUE] sqlstate_value
| mysql_error_code

条件处理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

handler_type:
CONTINUE
| EXIT
| UNDO

condition_value:
SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
| mysql_error_code

光标的使用

在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、
OPEN、FETCH 和 CLOSE,其语法分别如下。

声明光标:

1
DECLARE cursor_name CURSOR FOR select_statement

OPEN 光标:

1
OPEN cursor_name

FETCH 光标:

1
FETCH cursor_name INTO var_name [, var_name] ...

CLOSE 光标:

1
CLOSE cursor_name

变量、条件、处理程序、光标都是通过 DECLARE 定义的,它们之间是有先后顺序的要求的。变量和条件必须在最前面声明,然后才能是光标的声明,最后才可以是处理程序的声明

流程控制

IF语句

IF 实现条件判断,满足不同的条件执行不同的语句列表,具体语法如下:

1
2
3
4
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF

CASE语句

CASE 实现比 IF 更复杂一些的条件构造,具体语法如下:

1
2
3
4
5
6
7
8
9
10
11
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
Or:
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE

LOOP语句

LOOP 实现简单的循环,退出循环的条件需要使用其他的语句定义,通常可以使用 LEAVE 语句实现,具体语法如下:

1
2
3
[begin_label:] LOOP
statement_list
END LOOP [end_label]

如果不在 statement_list 中增加退出循环的语句,那么 LOOP 语句可以用来实现简单的死循环

LEAVE语句

用来从标注的流程构造中退出,通常和 BEGIN … END 或者循环一起使用。

INTRATE语句

ITERATE 语句必须用在循环中,作用是跳过当前循环的剩下的语句,直接进入下一轮循环

REPEAT语句

有条件的循环控制语句,当满足条件的时候退出循环,具体语法如下:

1
2
3
4
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]

WHERE 语句

WHILE 语句实现的也是有条件的循环控制语句,即当满足条件时执行循环的内容,具体语法如下:

1
2
3
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]

WHILE 循环和 REPEAT 循环的区别在于:WHILE 是满足条件才执行循环,REPEAT 是满足条件退出循环;WHILE 在首次循环执行之前就判断条件,所以循环最少执行 0 次,而 REPEAT 是在首次执行循环之后才判断条件,所以循环最少执行 1 次。

存储过程和函数的优势是可以将数据的处理放在数据库服务器上进行,避免将大量的结果集传输给客户端,减少数据的传输,但是在数据库服务器上进行大量的复杂运算也会占用服务器的 CPU,造成数据库服务器的压力,所以不要在存储过程和函数中进行大量的复杂运算,应尽量将这些运算操作分摊到应用服务器上执行

触发器

触发器创建

1
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt

触发器只能创建在永久表上,不能对临时表创建触发器。
trigger_time触发器的触发时间,可以是BEFORE或AFTER,BEFORE的含义指在检查约束前出发,而AFTER在检查约束后触发。
trigger_event触发器触发的事件,INSERT、UPDATE或者DELETE。
对同一个表相同触发时间的相同触发事件,只能定义一个触发器。
使用别名OLD和NEW来引用触发器中发生变化的记录内容。

删除触发器

1
DROP TRIGGER [schema_name.]trigger_name;

查看触发器

SHOW TRIGGERS

1
SHOW TRIGGERS;

系统triggers表

1
select * from information_schema.triggers;

触发器使用

触发器执行的语句的限制:

  1. 触发程序不能调用将数据返回客户端的存储程序,也不能使用采用 CALL 语句的动态 SQL 语句,但是允许存储程序通过参数将数据返回触发程序。也就是存储过程或者函数通过 OUT 或者 INOUT 类型的参数将数据返回触发器是可以的,但是不能调用直接返回数据的过程。
  2. 不能在触发器中使用以显式或隐式方式开始或结束事务的语句,如 START TRANSACTION、COMMIT 或 ROLLBACK。

MySQL 的触发器是按照 BEFORE 触发器、行操作、AFTER 触发器的顺序执行的,其中任何一步操作发生错误都不会继续执行剩下的操作。如果是对事务表进行的操作,那么会整个作为一个事务被回滚(Rollback),但是如果是对非事务表进行的操作,那么已经更新的记录将无法回滚,这也是设计触发器的时候需要注意的问题.