一、存储过程
1. 介绍
存储过程英文是stored procedure,就是一组经过预先编译的sql语句的封装。
存储过程预先存储在mysql服务器上,需要执行时,客户端只需要向服务端发出调用的命令,服务端就可以把预先存储好的这一系列sql语句全部执行。
2.优点
- 简化操作,提高sql语句的重用性。
- 减少网络传输,客户端不需要把所有的sql语句通过网络发给服务器。
- 减少了sql语句暴露在网上的风险,提高数据查询的安全。
3. 存储过程参数分类
存储过程的参数类型可以是in、out、inout。
- in表示输入参数。不管存储过程里面对参数怎么改变, 都不会影响外部的变量。
- out表示输出参数。不管参数传入之前定义的是什么, 在存储过程中都为NULL。存储过程里面对参数改变, 都会影响外部的变量。
- inout表示又是输入又是输出参数。参数在外部定义后, 会将定义的变量传入。存储过程里面对参数改变, 都会影响外部的变量。
4. 使用存储过程
DELIMITER $$
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,....)
[characteristics ...]
BEGIN
存储过程体
END $$
DELIMITER ;
- characteristics 表示创建存储过程时指定的对存储过程的约束条件,其取值信息如下:
- 存储过程体中可以有多条sql语句,如果只有一条sql语句,则可以省略bgin和end。
- DELIMITER表示设置新的结束标记,应为mysql默认的语句结束符号位分号,为避免与存储过程中sql语句结束符相冲突。
call 存储过程名()
调用
二、存储函数
1. 语法分析
DELIMITER $$
CREATE FUNCTION 存储函数名(IN|OUT|INOUT 参数名 参数类型,....)
RETURNS 返回值类型
[characteristics ...]
BEGIN
存储函数体
END $$
DELIMITER ;
- 参数列表funtion总是默认为in参数
- 函数体必须包含一个return value
- 采用
select 存储函数()
调用
若在创建存储过程中报错‘you might want to use the less safe log_bin_trust_function_creators variabke’有两种处理方法:
- 方式1:加上必要的函数特性,[not] deterministic 和{contains sql | no sql | reads sql data | modifies sql data}
- 方式2: set global log_bin_trust_function_creators = 1;
三、存储过程和函数的查看、修改、删除
1. 查看
使用show create查看创建信息
show create {procedure | function} 存储过程名或函数名
使用show status查看状态信息
show {procedure | function} status [like '存储过程名或函数名']
该语句返回子程序的特征,如数据库、名字、类型、创建者、创建修改时间等。like默认匹配名子,如果省略会列出所有的存储过程或函数。
从information_schema.Routines表中查看存储过程和函数信息
select * from information_schema.Routines where routine_name='存储过程和函数名'
2. 修改
修改只能修改约束信息,如果要对存储内容体进行修改只能删除新增
alter procedure 存储过程名 SQL SECURITY INVOKER COMMENT '111'
3. 删除
drop function if exists 存储函数名;
四、存储过程的争议
不同的公司对存储过程的使用需求差别很大,主要有如下缺点:
- 可移植性性差。存储过程不能跨数据库移植。
- 调试困难,只有少数dbms支持存储过程的调试。对于复杂的存储过程开发和维护都不容易。
- 存储过程的版本管理很困难。比如数据表索引发生变化了,可能导致存储过程失效。
- 不适合高并发场景。分库分表场景下,存储过程变得难以维护。