MySql基础篇——存储过程和函数

一、存储过程

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 ;
1
2
3
4
5
6
7

• characteristics 表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：
  
  
• 存储过程体中可以有多条sql语句，如果只有一条sql语句，则可以省略bgin和end。
• DELIMITER表示设置新的结束标记，应为mysql默认的语句结束符号位分号，为避免与存储过程中sql语句结束符相冲突。
• call 存储过程名() 调用

二、存储函数

1. 语法分析

DELIMITER $$
CREATE FUNCTION 存储函数名(IN|OUT|INOUT 参数名 参数类型,....)
RETURNS 返回值类型
[characteristics ...]
BEGIN
	存储函数体
END $$
DELIMITER ;
1
2
3
4
5
6
7
8

• 参数列表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} 存储过程名或函数名
1

使用show status查看状态信息

show {procedure | function}  status [like '存储过程名或函数名']
1

该语句返回子程序的特征，如数据库、名字、类型、创建者、创建修改时间等。like默认匹配名子，如果省略会列出所有的存储过程或函数。

** 从information_schema.Routines表中查看存储过程和函数信息**

select * from information_schema.Routines where routine_name='存储过程和函数名'
1

2. 修改

修改只能修改约束信息，如果要对存储内容体进行修改只能删除新增

alter procedure 存储过程名 SQL SECURITY INVOKER COMMENT '111'
1

3. 删除

drop function if exists 存储函数名;
1

四、存储过程的争议

不同的公司对存储过程的使用需求差别很大，主要有如下缺点：

1. 可移植性性差。存储过程不能跨数据库移植。
2. 调试困难，只有少数dbms支持存储过程的调试。对于复杂的存储过程开发和维护都不容易。
3. 存储过程的版本管理很困难。比如数据表索引发生变化了，可能导致存储过程失效。
4. 不适合高并发场景。分库分表场景下，存储过程变得难以维护。