存储过程
含义
Store Procedure,是一组经过预先编译
的SQL语句的封装。
执行过程
存储过程预先存储在 MySQL 服务器上,需要执行的时候,客户端只需要向服务器端发出调用存储过程的命令,服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。
好处
- 简化操作,提高了sql语句的重用性,减少了开发程序员的压力
- 一次编译,多次使用。
- 减少网络传输量(客户端不需要把所有的 SQL 语句通过网络发给服务器)
- 减少了 SQL 语句暴露在网上的风险,也提高了数据查询的安全性
缺点
- 可移植性差:不能跨数据库存储
- 调试困难
- 不适合高并发的场景
分类
存储过程的参数类型可以是IN(入参)、OUT(出参)和INOUT(出入参)
- 没有参数(无参数无返回)
- 仅仅带 IN 类型(有参数无返回)
- 仅仅带 OUT 类型(无参数有返回)
- 既带 IN 又带 OUT(有参数有返回)
- 带 INOUT(有参数有返回)
创建存储过程
DELIMITER //
CREATE PROCEDURE 存储过程名(IN id int, OUT name VARCHAR(50), INOUT age int)
[characteristics]
BEGIN
SELECT emp_name into name, emp_age into age FROM t1 where emp_id = id and emp_age = age;
END //
DELIMITER;
DELIMITER
结束表记,因为存储过程提里面的sql结束标记;和存储过程结束符;会有冲突,所以会重新定义存储过程的结束标记;
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 SQL | NO 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 SECURITY { DEFINER | INVOKER }
:执行当前存储过程的权限,即指明哪些用户能够执行当前存储过程。- DEFINER 表示只有当前存储过程的创建者或者定义者才能执行当前存储过程
- INVOKER 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程
-
COMMENT
: "string"注释信息
调用存储过程
CALL 存储过程名(实参列表)
格式
in模式
CALL p1(10);
out模式
SET @name;
CALL P1(@name);
SELECT @name;
inout模式
SET @name;
CALL P1(@name);
SELECT @name;
存储函数
和之前学过的单行函数、聚合函数是一个意思
创建函数
DELIMITER //
CREATE FUNCTION 函数名(参数名 参数类型, ...)
RETURNS 返回值类型
[characteristics ...]
BEGIN
带有RETURN的函数体
END //
调用存储函数
SELECT 函数名(实体列表)
### 举例
DELIMITER //
CREATE FUNCTION name_by_id(id int)
returns varchar(50)
DETERMINISTIC
CONTAINS SQL
BEGIN
RETURN (SELECT name FROM user where id = id);
END //
DELIMITER ;
注意
若在创建存储函数中报错you might want to use the less safelog_bin_trust_function_creators variable
,有两种处理方法:
- 加上必要的函数特性
[NOT] DETERMINISTIC
和{CONTAINS SQL | NO SQL | READS SQL DATA |MODIFIES SQL DATA}
- 执行
SET GLOBAL log_bin_trust_function_creators = 1
存储函数和存储过程
关键字 | 调用语法 | 返回值 | 应用场景 | |
---|---|---|---|---|
存储过程 | PROCEDURE | CALL 存储过程名() | 可以当成0个或者多个 | 一般用于更新 |
存储函数 | FUNCTION | SELECT 函数() | 只能是一个 | 一般用于查询且结果为一个值并返回时 |
查看存储过程或存储函数
show create {procedure | function} 存储过程名或者函数名
查看存储函数或存储过程的信息
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern'];
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程名或者函数的名'
[AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];
修改存储函数或者存储过程
只能修改characteristics
部分
ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...];
删除存储过程或者存储函数
DROP {PROCEDURE | FUNCTION} 存储过程名或者函数名