MySQL数据库DDL操作之存储过程和函数
lsg00 2019-06-27
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 <PROCEDURE|FUNCTION> 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: [email protected]
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: [email protected]
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 <PROCEDURE|FUNCTION> <procedure_name|function_name>;
示例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 <PROCEDURE|FUNCTION> <procedure_name|function_name>;
示例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 <DEFAULT var_value>;
参数解释：
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 <result> INTO var_name <FROM table_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编写，普通开发人员不容易掌握；
至此，存储过程和函数相关的内容介绍完毕，文中举的示例都是特别简单的，能够说明问题，有错误请大家指出。欢迎评论，转发，一块学习~
