摘要：上篇文章介绍了数据库操作中的触发器，本章将详细介绍数据库操作中的存储过程和函数，存储过程和函数在某些复杂业务场景下还是有很大作用的。定义和作用存储过程和函数是数据库中预先编译好的一个为了完成特定功能的语句集。
上篇文章介绍了MySQL数据库DDL操作中的触发器，本章将详细介绍MySQL数据库DDL操作中的存储过程和函数，存储过程和函数在某些复杂业务场景下还是有很大作用的。
1、定义和作用：
存储过程和函数是数据库中预先编译好的一个为了完成特定功能的SQL语句集。通过存储过程和函数，可以完成一些具有负责处理逻辑的数据库操作，同时可以减少应用程序和数据库服务器之间的数据传输，提升数据库的数据处理效率。
2、使用存储过程和函数的前提：
（1）创建存储过程和函数时，需要用户具备"CREATE ROUTINE"的权限；
（2）在删除和修改存储过程和函数时，需要用户具备"ALTER ROUTINE"的权限；
（3）在执行存储过程和函数时，需要用户具备"EXECUTE"的权限；
3、储存过程的创建和修改：
（1）存储过程创建的语法：
CREATE PROCEDURE p_name ([procedure_parameter[,...]])
BEGIN
[characteristic ...]
...procedure_statement... #存储过程的逻辑处理语句
END
参数解释：
procedure_name：存储过程的名称
procedure_parameter：存储过程的参数，可以包含多个参数，procedure_parameter中又包含了存储过程参数
类型、参数名称和参数的具体数据类型，它的定义格式为：[IN|OUT|INOUT] parameter_name field_type
characteristic:表示要修改存储过程的哪个部分，该参数的取值包括以下几种：
a. CONTAINS SQL，表示子程序包含SQL语句，但是，不包含读或写数据的语句
b. NO SQL，表示子程序中，不包含SQL语句
c. READS SQL DATA，表示子程序中，包含读数据的语句
d. MODIFIES DATA，表示子程序中，包含写数据的语句
e. SQL SECURITY {DEFINER | INVOKER}，指明谁有权限来执行
DEFINER，表示只有定义者，自己才能够执行
INVOKER，表示调用者可以执行
f. COMMENT "conte"，表示注释信息
g. LANGUAGE SQL，表示存储过程使用SQL语句来实现[默认]，为后期引入其他语言实现做准备
存储过程参数类型[IN|OUT|INOUT]说明：
IN：表示该参数为输入参数，即：调用存储过程时所要接收的输入参数，为一个具体的值
OUT：表示该参数为输出参数，即：存储过程在被调用时，需要接收的一个外部变量，通常使用@加变量名定义，比如："@a"。在存储过程处理完结果之后，可以将结果放在该外部变量中，供外部查看；
INOUT：表示该参数即可作为输入参数，接收一个具体的值；同时也可以作为输出参数，通过传入外部变量，完成在存储过程外部查看变量的值；
（2）示例应用：
示例1：创建一个存储过程，查询表出指定表中的记录数，并可以在存储过程外部查看：
修改默认定界符为$，也可使用DELIMITER命令完成
mysql> d $
创建存储过程：
mysql> CREATE PROCEDURE p_count(OUT param INT)
BEGIN
SELECT COUNT(*) FROM t_user INTO param FROM t_user;
END $
将修改的定界符恢复至默认定界符
mysql> d ;
使用CALL命令调用存储过程，传入@a变量，该变量在存储过程中被赋值：
mysql> CALL p_count(@a);
查看通过存储过程所查询到的表中记录数量：
注：查询自定义变量使用"@<变量名>"，如果查询系统中的变量，可以使用"@@<变量名>"，后期系统参数调优时介绍，此处可以先了解
mysql> SELECT @a;
+--------+
| @a |
+--------+
| 3 |
+--------+
1 row in set (0.00 sec)
示例2：创建一个存储过程，封装MySQL的LIMIT函数，实现分页：
创建存储过程：
mysql> d $
mysql> CREATE PROCEDURE p_page(IN pageNo INT,IN pageSize INT)
BEGIN
SELECT * FROM t_user LIMIT pageNo,pageSize;
END $
mysql> d ;
调用存储过程，根据具体传入的值查询记录：
mysql> CALL p_page(1,10);
注意：
（1）如果使用的是普通用户登录MySQL来执行存储过程，则需要注意权限问题。如果用户对某个表没有查询权限，则该用户如果调用了某个包含对该表有查询操作的存储过程，会调用失败；
（2）在一个存储过程中可以调用其他的函数或者存储过程，如上示例2，可以调用系统函数LIMIT；
（3）在存储过程中可以完成事务的操作，比如："提交事务(COMMIT)和回滚事务(ROLLBACK)"，但是不能完成"LOAD DATA INFILE"操作；
（3）存储过程的修改：
目前，存储过程暂时不支持直接修改其逻辑语句，只支持修改一些存储过程的特征，也就是对characteristic进行修改。
语法：
ALTER PROCEDURE procedure_name [characteristic ...];
示例：对上述的p_page存储过程进行修改，指明调用者可以执行该存储过程：
mysql> ALTER PROCEDURE p_page SQL SECURITY INVOKER;
4、函数的创建和修改：
（1）函数创建的语法：
CREATE FUNCTION function_name([function_parameter[,...]])
RETURNS type
BEGIN
[chracteristic ...]
...function statement... #函数中的实现逻辑
END
参数解释：
function_name：函数名称
function_parameter：函数的参数，它的定义格式为："param_name field_type"
[characteristic]：表示要修改的函数的哪个部分，包括的内容和存储过程相同
RETURNS type：表示返回值的声明
（2）示例应用：
示例1：创建一个函数，对于输入的内容，会在内容之前拼接"hello"字符串，在结束位置拼接"!"符号，实现如下：
创建函数：
mysql> CREATE FUNCTION function_hello(str CHAR(20))
RETURNS CHAR(50) DETERMINISTIC
RETURN CONCAT("Hello ",str,"!");
调用函数，传入字符串"Tomcat"，输出为："Hello ,Tomcat!"，如下：
mysql> SELECT function_hello("Tomcat");
+--------------------------+
| function_hello("Tomcat") |
+--------------------------+
| Hello Tomcat! |
+--------------------------+
1 row in set (0.00 sec)
说明：
RETURNS CHAR(50)：表示返回值类型为CHAR(50)
RETURN CONCAT("Hello ",str,"!")：表示具体的返回值是使用CONCAT函数拼接得到的
DETERMINISTIC：指明存储过程执行的结果是否正确。DETERMINISTIC 表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。
示例2：实现一个加法功能的函数，输入两个INT类型的值，计算这两个数的和：
定义函数：
mysql> CREATE FUNCTION function_add1(num1 VARCHAR(20),num2 VARCHAR(20))
RETURNS INT DETERMINISTIC
RETURN (IF(num1 REGEXP "[0-9]{1,}",num1,0) + IF(num2 REGEXP "[0-9]{1,}",num2,0));
调用，传入10和20，计算出这两个数的和为30，如下：
mysql> SELECT function_add(10,20);
+---------------------+
| function_add(10,20) |
+---------------------+
| 30 |
+---------------------+
1 row in set (0.00 sec)
注意：该函数中对入参做了简单判断，判断是否为数值，如果不为数值，默认当做0处理；
（3）函数的修改：
上面修改存储过程中介绍了使用ALTER完成的方法，此处直接通过修改系统中的mysql表中的数据来修改函数，对应的表为：mysql库中的proc
示例：将function_hello函数的定义者修改为"tomcat"@"localhost"。要修改的用户必须提前存在，修改方式如下：
选库：
mysql> USE mysql;
修改函数的定义者：
mysql> UPDATE proc SET definer = "tomcat@localhost" WHERE name = "function_hello";
刷新权限，如果不执行该操作，修改的结果不会生效：
mysql> FLUSH PRIVILEGES;
5、存储过程和函数的查看和删除：
（1）查看已经创建的存储过程或者函数：
语法：
SHOW STATUS LIKE "pattern";
参数解释：
pattern：表示存储过程或者函数名称的匹配串，支持模糊匹配
示例1：查看创建的p_page存储过程：
mysql> SHOW PROCEDURE STATUS LIKE "p_page" G
*************************** 1. row ***************************
Db: test
Name: p_page
Type: PROCEDURE
Definer: root@127.0.0.1
Modified: 2018-05-16 11:56:56
Created: 2018-05-16 11:21:45
Security_type: INVOKER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
示例2：查看创建的函数"function_hello"：
mysql> SHOW FUNCTION STATUS LIKE "%hello" G
*************************** 1. row ***************************
Db: test
Name: function_hello
Type: FUNCTION
Definer: root@127.0.0.1
Modified: 2018-05-16 12:09:17
Created: 2018-05-16 12:09:17
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.01 sec)
（2）查看存储过程或者函数的定义：
语法：
SHOW CREATE ;
示例1：查看触发器p_page的创建过程：
选库：
mysql> USE test;
查看创建过程：
mysql> SHOW CREATE PROCEDURE p_page G
*************************** 1. row ***************************
Procedure: p_page
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `p_page`(IN pageNo INT,IN pageSize INT)
SQL SECURITY INVOKER
begin
select * from t_user limit pageNo,pageSize ;
end
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
示例2：查看函数function_hello的创建过程：
选库：
mysql> USE test;
查看创建过程：
mysql> SHOW CREATE FUNCTION function_hello G
*************************** 1. row ***************************
Function: function_hello
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Create Function: CREATE DEFINER=`root`@`127.0.0.1` FUNCTION `function_hello`(str CHAR(20)) RETURNS char(50) CHARSET utf8
DETERMINISTIC
RETURN CONCAT("Hello ",str,"!")
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
注意：
查看存储过程和函数的定义还可以通过系统库information_schema中的routines表来查看，主要包括的字段有：ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_BODY,ROUTINE_COMMENT,DEFINER
mysql> SELECT ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_BODY,ROUTINE_COMMENT,DEFINER FROM information_schema.routines;
解释：
ROUTINE_SCHEMA:存储过程或者函数所在的库
ROUTINE_NAME:存储过程或者函数名称
ROUTINE_BODY:存储过程或者函数体
ROUTINE_COMMENT:注释信息
DEFINER:存储过程或者函数的定义者
6、删除存储过程和函数：
语法：
DROP ;
示例1：删除存储过程p_page：
mysql> USE test;
mysql> DROP PROCEDURE p_page;
示例2：删除函数function_hello：
mysql> USE test;
mysql> DROP FUNCTION function_hello;
7、变量的定义和赋值：
（1）变量的定义：
语法：
DECLARE var_name type ;
参数解释：
var_name:表示参数名称
type:表示参数类型
var_value:表示参数的默认初始值
示例1：定义一个INT类型的变量SUM，默认值为0：
DECLARE SUM INT DEFAULT 0;
示例2：定义一个VARCHAR(20)类型的变量STR，无初始值：
DECLARE STR VARCHAR(20);
（2）变量的赋值：
语法：
a.直接通过SET赋值：
SET var_name = var_value;
b.通过SELECT查询到结果之后再赋值：
SELECT INTO var_name ;
示例1：给VARCHAR(20)类型的变量STR赋一个初始值为""
mysql> SET STR = "";
示例2：查询表t_user中的记录数量，并将结果赋值给INT类型的STU_COUNT变量
mysql> SELECT COUNT(*) INTO STU_COUNT FROM t_user;
8、条件定义和处理：
语法：
a.条件的定义：
DECLARE condition_name CONDITION FOR condition_value;
condition_value:
SQLSTATE sql_state_value
参数解释：
condition_name:表示条件的名称
condition_value:表示条件中所关注的执行结果，通常为SQL的执行状态
b.条件的处理：
DECLARE handler_name HANDLER FOR condition_value do_statement;
参数解释：
handler_name:表示处理器的名称，常用的有："CONTINUE","SQLWARNING","NOT FOUND","SQLEXCEPTION"
condition_value:表示执行的结果，当处理器匹配到该结果时，会执行后面的do_statement
示例：在一个存储过程中连续给t_user表中插入相同的数据，如果插入失败，则继续向下执行，而不退出
mysql> d $
mysql> CREATE PROCEDURE p_insert()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE "23000" SET @state = 1;
INSERT INTO t_user(id,name,age) VALUES(1,"bing",23);
INSERT INTO t_user(id,name,age) VALUES(1,"bing",23);
SET @val = 666;
END $
mysql> d ;
mysql> SELECT @state,@val;
+--------+------+
| @state | @val |
+--------+------+
| 1 | 666 |
+--------+------+
1 row in set (0.00 sec)
结果分析：在上述的存储过程中，第二次插入t_user表中的记录由于主键冲突，故会插入失败。如果不定义条件处理的话。"SET @val=666"不会被执行，最后查出来的@val也不会为666，同样，@state也不会为1，现在查出来的结果分别为1和666，表示处理器起作用了。主键冲突的时候会提示"23000"状态码.
9、游标的使用：
（1）定义：
简单的理解，游标就是一个查询结果集的出口。在这个出口，可以完成对结果的筛选和其他操作。
（2）语法：
a.声明：
DECLARE cursor_name CURSOR FOR select_statement;
b.OPEN:打开游标
OPEN cursor_name;
c.FETCH：将游标的结果保存到某些中间变量中
FETCH cursor_name INTO var_name,...;
d.CLOSE：关闭游标
CLOSE cursor_name;
（3）示例：
使用游标统计出学生表t_student中男生和女生的总人数：
创建测试表结构：
CREATE TABLE t_student(
id INT PRIMARY KEY AUTO_INCREMENT,
gender VARCHAR(1) DEFAULT "0" COMMENT "0-男,1-女",
name VARCHAR(50) DEFAULT ""
) ENGINE = InnoDB DEFAULT CHARSET = UTF8;
测试数据：
INSERT INTO t_student(gender,name) VALUES("1","name01");
INSERT INTO t_student(gender,name) VALUES("0","name03");
INSERT INTO t_student(gender,name) VALUES("0","name06");
INSERT INTO t_student(gender,name) VALUES("0","name08");
创建存储过程：
mysql> d $
mysql> CREATE PROCEDURE student_count()
BEGIN
DECLARE str VARCHAR(1);
DECLARE gender_str CURSOR FOR SELECT gender FROM t_student;
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE gender_str;
SET @m_count = 0;
SET @f_count = 0;
OPEN gender_str;
REPEAT
FETCH gender_str INTO str;
IF str = "1" THEN
SET @m_count = @m_count + 1;
ELSE
SET @f_count = @f_count + 1;
END IF;
UNTIL 0 END REPEAT;
CLOSE gender_str;
END $
mysql> d ;
调用存储过程：
mysql> CALL student_count();
Query OK, 0 rows affected (0.00 sec)
查看统计结果，M表示女生数量，F表示男生数量，可见，已经使用游标统计完毕：
mysql> SELECT @m_count AS "M",@f_count AS "F" FROM DUAL;
+------+------+
| M | F |
+------+------+
| 1 | 3 |
+------+------+
1 row in set (0.00 sec)
10、存储过程和函数中的流程控制，主要介绍一下常用的IF，CASE，LOOP，REPEAT，WHILE流程控制：
（1）IF
语法：
IF search_conditoin THEN statement
elseif search_condition THEN state
END IF
示例：已经在上述的游标中使用到了，可自行查看。
（2）CASE
语法：
CASE case_value
WHEN when_value THEN statement
WHEN when_value THEN statement
... #可有多个判断语句
ELSE statement
END CASE
示例：将上述判断学生性别中使用的IF改为CASE如下：
CASE str
WHEN "1" THEN
SET @m_count = @m_count + 1;
ELSE
SET @f_count = @m_count + 1;
END CASE;
（3）LOOP
语法：
[loop_label:] LOOP
statement
END LOOP [loop_label];
示例：
LOOP通常用在循环语句中，处于BEGIN...END之间，如果没有退出语句的话，会一直循环下去，造成死循环，可以和LEAVE一块使用，如下，求1+2+...+100的和：
创建存储过程：
mysql> d $
mysql> CREATE PROCEDURE p_getsum()
BEGIN
SET @sum = 0;
SET @i = 0;
label:LOOP
SET @i = @i + 1;
SET @sum = @sum + @i;
IF @i = 100 THEN
LEAVE label;
END IF;
END LOOP label;
END $
mysql> d ;
调用存储过程并查看结果：
mysql> CALL p_getsum();
mysql> SELECT @sum;
+------+
| @sum |
+------+
| 5050 |
+------+
1 row in set (0.00 sec)
说明：
LEAVE：通常用在循环结构中，用来退出指定标记的循环，如上"LEAVE label"，表示跳出名称为label的循环，即：退出LOOP循环。
（4）REPEAT
语法：
[label:]REPEAT
statement
UNTIL search_condition
END REPEAT [label]
示例：求1+2+...+100的和：
创建存储过程：
mysql> d $
mysql> CREATE PROCEDURE p_getsum1()
BEGIN
SET @i = 0;
SET @SUM = 0;
REPEAT
SET @i = @i + 1;
SET @sum = @sum + @i;
UNTIL @i > 99
END REPEAT;
SELECT @sum FROM DUAL;
END $
mysql> d ;
调用存储过程，显示1+2+...+100的值：
mysql> CALL p_getsum1();
+------+
| @sum |
+------+
| 5050 |
+------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
（5）WHILE
语法：
[label:]WHILE search_condition DO
statement
END WHILE [label];
示例：求1+2+...+100的和：
创建存储过程：
mysql> d $
mysql> CREATE PROCEDURE p_getsum()
BEGIN
SET @i = 0;
SET @sum = 0;
WHILE @i < 100 DO
SET @i = @i + 1;
SET @sum = @sum + @i;
END WHILE;
SELECT @sum FROM DUAL;
END $
d ;
调用存储过程，查看结果：
CALL p_getsum();
+------+
| @sum |
+------+
| 5050 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
此处举了一个简单的求和的例子，说明了一下存储过程中的流程控制，负责的流程控制也是由这些简单的组合而成的。简单的掌握了，就可以在这个基础上写出更复杂的存储过程。
11、存储过程的优缺点：
优点：
（1）可以完成实时的复杂报表数据处理及统计；
（2）可以很好的解决某些业务系统和数据库之间的耦合度，比如政府、银行或者金融系统中，一旦存储过程调试完毕，会稳定运行，减少很大一部分不必要的系统间交互；
缺点：
（1）互联网行业大并发场景，存储过程会由于访问量大，而且同时操作多张表，有可能会造成死锁，不好排查，导致数据库出现瓶颈。所以应该慎用，最好别用；
（2）迁移会比较麻烦，如果存储过程中用到了多张表，必须先保证表结构迁移正确，否则存储过程迁移时会出现错误；
（3）如果数据库中的某些表结构变化了，可能需要重新删除并创建存储过程，可扩展性较差；
（4）存储过程多数情况下都是由DBA编写，普通开发人员不容易掌握；
至此，存储过程和函数相关的内容介绍完毕，文中举的示例都是特别简单的，能够说明问题，有错误请大家指出。欢迎评论，转发，一块学习~
后续文章将更新在个人小站上，欢迎查看。
