什么是存储过程?
经笔者考虑,高深莫测的解释就暂且放一放,用笔者略知一二的知识,先用最简单直白的理解挑明MySQL的存储过程是什么?从事运维的盆友肯定会敲linux命令,那么将一堆linux命令集合在一起,再加上逻辑等语句后,它就成了shell脚本,也就是说shell脚本是linux命令的集合。再回到数据库,要查询一条记录,需要用select,那需求复杂一点的话,假设需要对多个表进行关联查询,且查询某段时间范围内的记录,且要对查询到的结果进行判断是否满足条件,如果不满足又怎样怎样,如果满足又怎样怎样(比如满足后就做去重,过滤,再计算,再将结果更新到另外一张表)。看似一个简单需求,如果要实现起来,估计要一条条的敲sql语句了,那能不能将这些sql语句都写好、测试好,加上判断逻辑、循环逻辑等等编程的基础能力,再保存起来,方便以后调用呢,而且还可以入参出参。那么存储过程就可以做到了,所谓的存储过程,说白了就是sql语句的集合。那单从字面上的角度来理解的话,又是存储,又是过程,到底是什么鬼?笔者对这个字面上的意思简单粗暴的理解是:存储即是保存的意思,过程即是一条一条sql语句的执行顺序(比如刚才的需求举例,先执行哪条,后执行哪条,这就是过程)。所以,简而言之就叫存储过程。好了,这是笔者的理解,如有不对之处,望各大牛们批评指正,相互学习,共同进步。最后再啰嗦一句,存储过程不仅可以手动调用,还可以被上层应用的代码直接进行调用哦!
存储过程简要
创建语法 | 调用语法 | 返回值 | 应用场景 |
create procedure 存储过程() | CALL 存储过程() | 理解为有0个或多个 | 一般用于更新 |
语法和参数说明
可以使用 CREATE PROCEDURE 语句创建存储过程,语法格式如下:
CREATE PROCEDURE 存储过程名称(IN|OUT|INOUT 参数名 参数类型,...) [characteristics ...] BEGIN 存储过程体 END
- 存储过程名称
默认在当前数据库中创建(use [数据库]),如果要在指定的数据库创建存储过程,则:数据库名.存储过程名。注意:名称一定要避免与MySQL内置的存储过程或函数相同的名称,否则会发生意料之外的错误。
- IN|OUT|INOUT
IN:入参(将参数传递给存储过程),OUT:出参(存储过程执行后将结果保存至该参数并返回),INOUT:既入参和出参(该参数用来做入参也用来做结果返回)
- 参数名和参数类型
参数名可自定义取名字,参数的类型可以是任何有效的MySQL数据类型,当有多个参数时,用逗号","分隔即可。
- characteristics(特征或者说特性)
存储过程自身的特性,后面会讲到哈!
- 过程体
存储过程体,也就是一条一条执行的SQL语句,以BEGIN开始,END结束。
关于结束符
在 MySQL 中,默认的结束符是“;”,如果在存储过程体中编写的sql最后也用“;”做结束符,那就悲剧了,估计就不执行了。那么怎么办?
解决办法是:使用MySQL自带DELIMITER命令改变结束符,比如改成“//”。
语法格式:
DELIMITER //
语法说明如下:
- // 是定义的结束符,也可以是其他,比如“??”
- 注意:避免使用反斜杠“\”字符,因为它是MySQL的转义字符。
小实践一下
mysql > DELIMITER // Empty set (0.00 sec) mysql>
成功执行这条 SQL 语句后,结束符就变成“//”了
如果要将其设置回默认的,执行如下即可:
mysql > DELIMITER ;
再来个小案例:将结束符修改为”??”符号
mysql> delimiter ?? mysql> select * from users?? Empty set (0.00 sec) mysql>
存储过程创建案例
案例:创建存储过程,名为showuser,用途是查询users表的所有字段
# 先修改结束符为“//”符号 DELIMITER // # 创建存储过程 CREATE PROCEDURE zabbix.showuser() BEGIN SELECT * FROM users; END //
注意:默认在当前数据库中创建。若需要在特定数据库中创建存储过程,则要在名称前面加上数据库的名称,即zabbix.showuser(),数据库名称是zabbix,存储过程名称是showuser
案例:存储过程用途,通过主机名查询IP地址
# 先修改结束符为“//”符号 DELIMITER // # 创建存储过程 CREATE PROCEDURE zabbix.getHostIp(IN hostname VARCHAR(64)) BEGIN select h.name,i.ip from zabbix.hosts h join zabbix.interface i on h.name=hostname and h.hostid=i.hostid; END //
查看存储过程
1. 查看存储过程的状态
精确查看
mysql> show procedure status like 'getHostIp' \G *************************** 1. row *************************** Db: zabbix Name: getHostIp Type: PROCEDURE Definer: root@% Modified: 2022-04-08 15:58:31 Created: 2022-04-08 15:58:31 Security_type: DEFINER Comment: character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8_bin 1 row in set (0.00 sec)
模糊查看
mysql> show procedure status like '%get%' \G *************************** 1. row *************************** Db: zabbix Name: getHostIp Type: PROCEDURE Definer: root@% Modified: 2022-04-08 15:58:31 Created: 2022-04-08 15:58:31 Security_type: DEFINER Comment: character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8_bin 1 row in set (0.00 sec) mysql> mysql> show procedure status like 'show%' \G *************************** 1. row *************************** Db: zabbix Name: showuser Type: PROCEDURE Definer: root@% Modified: 2022-04-08 15:47:32 Created: 2022-04-08 15:47:32 Security_type: DEFINER Comment: character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8_bin 1 row in set (0.00 sec) mysql>
2. 查看存储过程的定义
语法:
SHOW CREATE PROCEDURE 存储过程名;
查看zabbix数据库下的getHostIp存储过程的定义语句
mysql> show create procedure zabbix.getHostIp\G *************************** 1. row *************************** Procedure: getHostIp sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `getHostIp`(IN hostname VARCHAR(64)) BEGIN select h.name,i.ip from zabbix.hosts h join zabbix.interface i on h.name=hostname and h.hostid=i.hostid; END character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8_bin 1 row in set (0.00 sec) mysql>
3. 在information_schema.Routines中查看
存储过程的信息都存储在 information_schema 数据库下的 Routines 表中,可以通过查询该表的记录来查询存储过程的信息
案例1
SELECT SPECIFIC_NAME FROM information_schema.Routines;
案例2
mysql> SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME="getHostIp"\G *************************** 1. row *************************** SPECIFIC_NAME: getHostIp ROUTINE_CATALOG: def ROUTINE_SCHEMA: zabbix ROUTINE_NAME: getHostIp ROUTINE_TYPE: PROCEDURE DATA_TYPE: CHARACTER_MAXIMUM_LENGTH: NULL CHARACTER_OCTET_LENGTH: NULL NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: NULL CHARACTER_SET_NAME: NULL COLLATION_NAME: NULL DTD_IDENTIFIER: NULL ROUTINE_BODY: SQL ROUTINE_DEFINITION: BEGIN select h.name,i.ip from zabbix.hosts h join zabbix.interface i on h.name=hostname and h.hostid=i.hostid; END EXTERNAL_NAME: NULL EXTERNAL_LANGUAGE: SQL PARAMETER_STYLE: SQL IS_DETERMINISTIC: NO SQL_DATA_ACCESS: CONTAINS SQL SQL_PATH: NULL SECURITY_TYPE: DEFINER CREATED: 2022-04-08 15:58:31 LAST_ALTERED: 2022-04-08 15:58:31 SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT: DEFINER: root@% CHARACTER_SET_CLIENT: utf8mb4 COLLATION_CONNECTION: utf8mb4_0900_ai_ci DATABASE_COLLATION: utf8_bin 1 row in set (0.00 sec) mysql>
在 information_schema 数据库下的 routines 表中,存储着所有存储过程的定义。所以,使用 SELECT 语句查询 routines 表中的存储过程和函数的定义时,一定要使用 routine_name 字段指定存储过程的名称,否则,将查询出所有的存储过程的定义。
mysql> select SPECIFIC_NAME from information_schema.Routines;
调用存储过程
MySQL中使用CALL语句来调用存储过程
语法如下:
CALL storate_name([parameter[...]]);
其中,storate_name 表示存储过程的名称,parameter 表示存储过程的参数。
案例:调用zabbix数据库的存储过程getHostIp,并传入参数
# 将结束符修改回默认的“;”分号 mysql> delimiter ; # 开始调用 mysql> call zabbix.getHostIp('zbxproxy02'); +------------+----------------+ | name | ip | +------------+----------------+ | zbxproxy02 | 192.168.11.156 | +------------+----------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql>
修改存储过程
语法格式如下:
ALTER PROCEDURE 存储过程名 [ 特征 ... ]
特征指定了存储过程的特性,笔者从官方文档整理了常用的,常用的有:
- CONTAINS SQL 表示子程序包含 SQL 语句,但不包含读或写数据的语句。
- NO SQL 表示子程序中不包含 SQL 语句。
- READS SQL DATA 表示子程序中包含读数据的语句。
- MODIFIES SQL DATA 表示子程序中包含写数据的语句。
- SQL SECURITYDEFINERINVOKER指明谁有权限来执行。
- DEFINER 表示只有定义者自己才能够执行。
- INVOKER 表示调用者可以执行。
- COMMENT 'string' 表示注释信息。
案例:修改存储过程 getHostIp 的定义,将读写权限改为 MODIFIES SQL DATA,并指明调用者可以执行,代码如下:
# 修改 mysql> ALTER PROCEDURE zabbix.getHostIp MODIFIES SQL DATA SQL SECURITY INVOKER; Query OK, 0 rows affected (0.01 sec) mysql> # 查看定义 mysql> show create procedure zabbix.getHostIp \G; *************************** 1. row *************************** Procedure: getHostIp sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `getHostIp`(IN hostname VARCHAR(64)) MODIFIES SQL DATA # 看这里 SQL SECURITY INVOKER # 看这里 BEGIN select h.name,i.ip from zabbix.hosts h join zabbix.interface i on h.name=hostname and h.hostid=i.hostid; END character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8_bin 1 row in set (0.00 sec) ERROR: No query specified mysql>
结果显示:存储过程修改成功。从运行结果可以看到,访问数据的权限已经变成了 MODIFIES SQL DATA,安全类型也变成了 INVOKE。
案例:那么如何修改存储过程的内容?
重要提示:ALTER PROCEDURE 语句用于修改存储过程的某些特征。如果要修改存储过程的内容,可以先删除原存储过程,再以相同的命名创建新的存储过程;如果要修改存储过程的名称,可以先删除原存储过程,再以不同的命名创建新的存储过程。
删除存储过程
MySQL 中使用 DROP PROCEDURE 语句来删除数据库中已经存在的存储过程。语法格式如下:
DROP PROCEDURE [ IF EXISTS ] <过程名>
语法格式:
- 过程名:指定要删除的存储过程的名称。
- IF EXISTS:指定这个关键字,用于防止因删除不存在的存储过程而引发的错误。
注意:存储过程名称后面没有参数列表,也没有括号,在删除之前,必须确认该存储过程没有任何依赖关系,否则会导致其他与之关联的存储过程无法运行。
案例:删除ttrdb数据库下的存储过程showUserList
mysql> drop procedure ttrdb.showUserList; Query OK, 0 rows affected (0.06 sec) mysql>
再次深度剖析存储过程语法格式
语法:
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...) [characteristics ...] BEGIN存储过程体 END
1. 关于IN|OUT|INOUT
- IN:当前参数为输入参数,也就是表示入参,存储过程只是读取这个参数的值。如果没有定义参数种类, 默认就是IN,表示输入参数。
- OUT:当前参数为输出参数,也就是表示出参,执行完成之后,调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
- INOUT:当前参数既可以为输入参数,也可以为输出参数。
2. 形参类型可以是MySQL数据库中的任意类型
3. characteristics表示创建存储过程时指定的对存储过程的约束条件,其取值信息如下:
LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string
- LANGUAGE SQL :说明存储过程执行体是由SQL语句组成的,当前系统支持的语言为SQL。
- [NOT] DETERMINISTIC :指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时,相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的,相同的输入可能得到不同的输出。如果没有指定任意一个值,默认为NOT DETERMINISTIC。
- CONTAINS SQLNO SQL | READS SQL DATA | MODIFIES SQL DATA:指明子程序使用SQL语句的限制,每个限制的意义如下:
CONTAINS SQL表示当前存储过程的子程序包含SQL语句,但是并不包含读写数据的SQL语句; NO SQL表示当前存储过程的子程序中不包含任何SQL语句; READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句; MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。 默认情况下,系统会指定为CONTAINS SQL。
- SQL SECURITYDEFINERINVOKER:执行当前存储过程的权限,即指明哪些用户能够执行当前存储过程,权限意义如下:
DEFINER 表示只有当前存储过程的创建者或者定义者才能执行当前存储过程; INVOKER 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
4. 存储过程体中可以有多条SQL语句,如果仅仅一条SQL语句,则可以省略BEGIN和END。
1. BEGIN…END:BEGIN…END 中间包含了多个语句,每个语句都以(;)号为结束符。 2. DECLARE:DECLARE 用来声明变量,使用的位置在于 BEGIN…END 语句中间,而且需要在其他语句使用之前进 行变量的声明。 3. SET:赋值语句,用于对变量进行赋值。 4. SELECT… INTO:把从数据表中查询的结果存放到变量中,也就是为变量赋值。
5. 需要设置新的结束标记
DELIMITER 新的结束标记
因为MySQL默认的语句结束符号为分号‘;’。为了避免与存储过程中SQL语句结束符相冲突,需要使用DELIMITER改变存储过程的结束符。
比如:“DELIMITER //”语句的作用是将MySQL的结束符设置为//,并以“END //”结束存储过程。存储过程定义完毕之后再使用“DELIMITER ;”恢复默认结束符。DELIMITER也可以指定其他符号作为结束符。
当使用DELIMITER命令时,应该避免使用反斜杠(‘\’)字符,因为反斜线是MySQL的转义字符。
示例
DELIMITER $ CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...) [characteristics ...] BEGIN sql语句1; sql语句2; END $
实践:储过程的参数IN、OUT和INOUT
存储过程有多种调用方法。存储过程必须使用CALL语句调用,并且存储过程和数据库相关,如果要执行其他数据库中的存储过程,需要指定数据库名称,例如CALL dbname.procname。
1. OUT参数
案例:在zabbix数据库中创建存储过程show_max_historyValue(),查看“history”表的最大值。并将最大值通过OUT参数“ms”输出
创建存储过程
# 修改结束符 delimiter // # 创建存储过程 CREATE PROCEDURE zabbix.show_max_historyValue(OUT ms DOUBLE) begin select max(value) into ms from zabbix.history; end // # 将结束符修改为默认 delimiter ;
调用存储过程
# 调用存储过程,并直接声明@maxValue变量用于接收出参 mysql> call zabbix.show_max_historyvalue(@maxValue); Query OK, 1 row affected (2.26 sec) mysql> select @maxValue; +-------------------+ | @maxValue | +-------------------+ | 2032.179710050258 | +-------------------+ 1 row in set (0.00 sec) mysql> # 查询maxValue变量的值 mysql> select @maxValue; +-------------------+ | @maxValue | +-------------------+ | 2032.179710050258 | +-------------------+ 1 row in set (0.00 sec) mysql>
2. IN参数
案例:传入hostname,查询主机名对应的IP地址
创建存储过程
delimiter // create procedure zabbix.by_hostname_get_ip(IN hostname varchar(64)) begin select h.name,i.ip from zabbix.hosts h join zabbix.interface i on h.name=hostname and h.hostid=i.hostid; end // delimiter ;
调用存储过程
mysql> call zabbix.by_hostname_get_ip('zbxproxy03'); +------------+----------------+ | name | ip | +------------+----------------+ | zbxproxy01 | 192.168.11.155 | +------------+----------------+ 1 row in set (0.01 sec) Query OK, 0 rows affected (0.01 sec)
3. IN和OUT同时存在
案例:hostname形参为入参,将查询到的ip地址通过ipaddr形参来进行出参
创建存储过程
delimiter // create procedure zabbix.by_hostname_query_ip(IN hostname varchar(64), OUT ipaddr varchar(128)) begin select i.ip into ipaddr from zabbix.hosts h join zabbix.interface i on h.name=hostname and h.hostid=i.hostid; end // delimiter ;
调用存储过程,直接声明一个自定义变量@ip来接收出参
mysql> call zabbix.by_hostname_query_ip("zbxproxy02",@ip); Query OK, 1 row affected (0.00 sec) mysql> select @ip; +----------------+ | @ip | +----------------+ | 192.168.11.156 | +----------------+ 1 row in set (0.00 sec) mysql>
4. INOUT参数
案例:入参和出参都用同一个形参nameip,入参就输入主机名,出参就输出ip地址
创建存储过程
delimiter // create procedure zabbix.by_hostname_query_ip(INOUT nameip varchar(64)) begin select i.ip into nameip from zabbix.hosts h join zabbix.interface i on h.name=nameip and h.hostid=i.hostid; end // delimiter ;
调用存储过程,将需要查询的主机名赋值给@ipaddr变量,调用存储过程的时候引用这个变量作为入参,执行完后,同时使用这个变量接收输出的值
mysql> set @ipaddr="zbxproxy02"; Query OK, 0 rows affected (0.00 sec) mysql> call zabbix.by_hostname_query_ip(@ipaddr); Query OK, 1 row affected (0.00 sec) mysql> select @ipaddr; +----------------+ | @ipaddr | +----------------+ | 192.168.11.156 | +----------------+ 1 row in set (0.00 sec) mysql>
存储过程体中定义变量和使用变量
- 在存储过程体中定义的变量称为局部变量;
- 可以使用 DECLARE 语句在存储过程体中定义一个局部变量;
- 局部变量的作用域:仅仅在存储过程中定义它的 BEGIN ... END 中有效;
- 局部变量的位置:只能放在存储过程中的 BEGIN ... END 中,而且只能放在第一句。
语法格式:
CREATE PROCEDURE [存储过程名称](IN|OUT|INOUT 参数名 参数类型,...) BEGIN #声明局部变量 DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值]; DECLARE 变量名2, 变量名3,... 变量数据类型 [DEFAULT 变量默认值]; #为局部变量赋值 SET 变量名1=值; SELECT 值 INTO 变量名2 [FROM 子句]; #查看局部变量的值 SELECT 变量1,变量2,变量3; END
案例创建存储过程,传入的数字都和100相加,然后再查看变量的值
delimiter // create procedure zabbix.add_num(IN n INT) begin declare i INT; declare sum INT; set i=100; set sum=0; set sum=i+n; select sum; end // delimiter ;
调用存储过程
mysql> call zabbix.add_num(200); +------+ | sum | +------+ | 300 | +------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.01 sec) mysql> call zabbix.add_num(10); +------+ | sum | +------+ | 110 | +------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
存储过程中使用while循环
案例:创建存储过程,实现累加运算,计算 1+2+…+n 等于多少。具体的代码如下:
创建存储过程add_num
delimiter // create procedure zabbix.add_num(IN n INT) begin declare i INT; declare num INT; set i=1; set num=0; while i<=n do set num=num+i; set i=i+1; end while; select num; end // delimiter ;
调用存储过程
mysql> call zabbix.add_num(10); +------+ | num | +------+ | 55 | +------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql>
对存储过程优缺点的简单之析:
存储过程有很多优点,但同时也有缺点,如果滥用,过多的使用存储过程不见得是个好事情。
笔者结合自身使用经验、以及参照官网和各种科学上网谷歌之后,得出下面的优缺之点。
优点如下:
- 存储过程一次写好就可以多次使用(其实是在创建时进行编译),之后的使用都不需要重新编译了,直接调用即可,提升执行效率;
- 上层应用可以减少开发量,可以调用存储过程;
- 安全性强,可以设置对用户的使用权限;
- 减少网络传输,每次使用只需要调用存储过程即可,减少网络传输量;
- 不错的封装性,在解决复杂的需求进行操作时,要使用一条一条的SQL语句去实现,需要多次才能完成,而有了存储过程,一次连接调用即可。
缺点如下:
- 可移植性差,存储过程不能跨数据库移植;
- 调试困难;
- 存储过程版本管理不方便,改了需求不能按版本迭代。
写在最后的“总而言之”
存储过程既方便但又有局限性,是对于运维、和开发人员来说,掌握存储过程是必备技能。