@9 存储过程&存储函数

基本概述

存储过程

含义：存储过程的英文是 Stored Procedure。它的思想很简单，就是一组经过 预先编译 的 SQL 语句的封装。

可以把存储过程看成是一些 SQL 语句的集合，中间加了点逻辑控制语句。存储过程在业务比较复杂的时候是非常实用的，比如很多时候我们完成一个操作可能需要写一大串 SQL 语句，这时候我们就可以写有一个存储过程，这样也方便了我们下一次的调用。存储过程一旦调试完成通过后就能稳定运行，另外，使用存储过程比单纯 SQL 语句执行要快，因为存储过程是预编译过的。

执行过程：存储过程先存储在 MySQL 服务器上。客户端发出指令后，服务器直接执行存储过程。

存储过程 vs 视图：

• 都简化操作、降低失误、减少网络传播量、提高安全性
• 视图一般只用于查看数据，存储过程一般直接操作数据库表

参数类型：可以是 IN(传入,默认) 、 OUT 、 INOUT(传入&传出) ，或者 无参数。存储过程可以包含多个参数类型。

阿里不推荐使用存储过程。
存储过程在互联网公司应用不多，因为存储过程难以调试和扩展，而且没有移植性，还会消耗数据库资源。

存储函数

含义：类似于 Java 中自定义函数。

参数类型：参数类型一定是 IN(传入)，或者 无参数。

存储过程

存储过程 - 创建

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
	存储过程体
END

概念

• 存储过程体 存储一条或多条 SQL 语句。
  • 仅仅一条 SQL 语句，则可以省略 BEGIN 和 END。
  • 复杂编写可以查看后续文章。
• 结束标记 放在 End 后标记结束，需要重新设置
  • MySQL 默认的语句结束符号为分号 ; 。为了避免与存储过程中 SQL 语句结束符相冲突，需要使用 DELIMITER 改变存储过程的结束符。
  • DELIMITER 新的结束标记 ，如 DELIMITER // 对应 END // 结束存储过程。
  • 存储过程定义完毕之后再使用 DELIMITER ; 恢复默认结束符。
• 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 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
    • 如果没有设置相关的值，则 MySQL 默认指定值为 DEFINER。
  • COMMENT 'string' ：注释信息，可以用来描述存储过程。

存储过程 - 调用

CALL 数据库.存储过程名(实参列表)  # 调用其他数据库的存储过程
CALL 存储过程名(实参列表)        # 调用当前数据库的存储过程

存储过程 - 使用例子

创建存储过程

DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
	SELECT * FROM emps;
END $
DELIMITER ;
 
 
DELIMITER $
CREATE PROCEDURE show_max_salary(IN uid int)
	LANGUAGE SQL
	NOT DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT '查看最高薪资'
	BEGIN
		SELECT MAX(salary) FROM emps where id = uid;
	END $
DELIMITER ;
 
 
DELIMITER //
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DOUBLE)
	BEGIN
		SELECT salary INTO empsalary FROM emps WHERE ename = empname;
	END //
DELIMITER ;

调用存储过程

# 调用in模式的参数
CALL sp1('值');
# 调用out模式的参数
SET @name;
CALL sp1(@name);
SELECT @name;
# 调用inout模式的参数
SET @name=值;
CALL sp1(@name);
SELECT @name;

存储函数

存储函数 - 创建

CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
[characteristics ...]
BEGIN
	函数体   #函数体中肯定有 RETURN 语句
END

概念

• 参数只有 IN 类型或无参类型。
• RETURNS type 语句表示函数返回数据的类型。
• characteristic 函数的约束，同存储过程。
• 函数体 同存储过程体，但是需要有 RETURN 语句。

存储函数 - 调用

SELECT 函数名(实参列表)

存储函数 - 使用例子

DELIMITER //
 
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGIN
	RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //
 
DELIMITER ;

调用：

SELECT email_by_name();

注意：

若在创建存储函数中报错“ you might want to use the less safe log_bin_trust_function_creators variable ”，有两种处理方法：

• 方式 1：加上必要的函数特性“ [NOT] DETERMINISTIC”和“{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} ”

• 方式 2：

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

存储函数 VS 存储过程

	关键字	调用语法	返回值	应用场景
存储过程	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'}]; # 从系统表中查看信息

mysql> SHOW PROCEDURE STATUS LIKE 'SELECT%' \G
*************************** 1. row ***************************
                  Db: test_db
                Name: SelectAllData
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2021-10-16 15:55:07
             Created: 2021-10-16 15:55:07
       Security_type: DEFINER
             Comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)

存储对象/函数 - 修改

使用 ALTER 语句实现修改相关特性。

ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]

其中，characteristic 指定存储过程或函数的特性，其取值信息与创建存储过程、函数时的取值信息略有不同。

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

• CONTAINS SQL，表示子程序包含 SQL 语句，但不包含读或写数据的语句。
• NO SQL，表示子程序中不包含 SQL 语句。
• READS SQL DATA，表示子程序中包含读数据的语句。
• MODIFIES SQL DATA，表示子程序中包含写数据的语句。
• SQL SECURITY { DEFINER | INVOKER }，指明谁有权限来执行。
  • DEFINER，表示只有定义者自己才能够执行。
  • INVOKER，表示调用者可以执行。
• COMMENT 'string'，表示注释信息。

存储对象/函数 - 删除

DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名

存储过程的争议

优点

**1、存储过程可以一次编译多次使用。**存储过程只在创建时进行编译。

2、可以重复使用，减少开发工作量。

3、设置权限，存储过程的安全性强。

4、只需要调用存储过程，可以减少网络传输量。

5、多条语句封装在一起，封装性良好。

缺点

1、可移植性差，不可跨数据库移植。

2、调试困难，只有少数 DBMS 支持存储过程的调试。

3、存储过程的版本管理很困难。

**4、它不适合高并发的场景。**高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护，增加数据库的压力，显然就不适用了。