存储过程概述

含义: 存储过程的英文是stored procedure,它的思想很简单,就是一组经过预先编译的SQL语句的封装

执行过程
- 存储过程预先存储在MySQL服务器上,需要执行的时候,客户端只需要向服务器端发出调用存储过程的命令,服务器端就可以把预先存储好的这一系列的SQL语句全部执行
好处
- 简化操作,提高sql语句的重用性,减少了开发程序员的压力
- 减少操作过程的失误,提高效率
- 减少网络传输量(客户端不需要把所有的sql语句通过网络发给客户端)
- 减少了sql语句暴露在网上的风险,也提高了数据查询的安全性
分类

存储过程的参数类型可以是IN,OUT和INOUT
- 没有参数(无参无返回值)
- 仅仅带 IN 类型 (有参无返回值)
- 仅仅带 OUT 类型(无参有返回值)
- 有 IN 类型也有 OUT 类型 (有参有返回值)
- 带 INOUT 类型(有参有返回值)

创建存储过程

创建语法

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

参数解析

IN:表示入参
OUT:表示出参
INOUT: 表示当前参数可是出参,也可以是入参

形参类型可以是mysql数据库中的任意类型

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默认指定为DERFINER
COMMENT 'STRING':注释信息,可以用来描述存储过程

存储过程体中可以有多条sql语句,如果仅仅一条sql语句,则可以省略 begin 和 end

编写存储过程并不是一件简单的事,可能存储过程中需要复杂的sql语句

begin ... end : begin ... end 中间包含多个语句,每个语句都以(;)号为结束符
declare: 用来声明变量,使用的位置位于 begin ..end 语句中间,而且需要在其它语句使用之前进行变量的声明
set:赋值语句,用于对变量进行赋值
select ... into : 把从数据表中查询的结果存放到变量中,也就是为变量赋值

需要设置新的结束标记

DELIMITER 新的结束标记
-- 因为mysql默认的语句结束符号是";",为了避免与存储过程中sql语句结束符冲突,需要使用 delimiter 改变存储过程的结束符

-- 比如: "delimiter // "语句的作用就是将mysql的结束符设置为 // ,并以 "end //"结束存储过程
-- 当使用DELIMITER命令时，应该避免使用反斜杠（‘\’）字符，因为反斜线是MySQL的转义字符

-- 实例
DELIMTER //
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型, ...)
[characteristic]
BEGIN
	SQL 语句1;
	SQL 语句2;
END //

调用存储过程

调用格式

-- 存储过程有多种调用方法.存储过程必须使用 CALL 语句调用,并且存储过程和数据库相关.如果要执行其他数据库中的存储过程,需要指定数据库名称;如: CALL dbname.procname.

CALL 存储过程名(实参列表)

方式1: IN方法

CALL SP1('值')

方式2: OUT方法

SET @name;
CALL sp2(@name);
SELECT @name;

方式3: INOUT方法

SET @name=值;
CALL sp3(@name);
SELECT @name;

调试

在 MySQL 中，存储过程不像普通的编程语言（比如 VC++、Java 等）那样有专门的集成开发环境。因

此，你可以通过 SELECT 语句，把程序执行的中间结果查询出来，来调试一个 SQL 语句的正确性。调试

成功之后，把 SELECT 语句后移到下一个 SQL 语句之后，再调试下一个 SQL 语句。这样逐步推进，就可

以完成对存储过程中所有操作的调试了。当然，你也可以把存储过程中的 SQL 语句复制出来，逐段单独

调试。

存储函数

语法解析

-- 语法格式
CREATE FUNCTION 函数名 (参数名 参数类型 , ...)
RETURNS 返回值类型
[characteristics ...]
BEGIN
	函数体 -- 函数体中肯定得有 return 语句
END

说明:

1, 参数列表: 指定参数为 IN,OUT,INOUT只对procedure 是合法的, function 中总是默认为 in参数

2, returns type 语句表示函数返回数据的类型

3, characteristics 创建函数时指定的对函数的约束,取值与创建存储过程时相同

4, 函数体也可以用 begin .. end 来表示sql代码的开始和结束.如果函数体只有一条语句,可以省略 begin …end

调用函数

在MySQL中,存储函数的使用方法与MySQL的内部函数的使用方式是一样的.换言之,用户自己定义的存储函数与MySQL内部函数是一个性质的.区别在于,存储函数是用户自己定义的,而内部函数是MySQL的开发者定义的

SELECT 函数名(实参列表)

注意: 如果在创建存储函数中报错 “you might want to use the less safe log_bin_trust_funtion_creators variable” 有两种处理方式

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

方式二: 在服务端执行命令行 “SET GLOBAL log_bin_trust_funtion_creators = 1”

对比存储过程和存储函数

	关键字	调用语法	返回值	应用场景
存储过程	PROCEDURE	CALL 存储过程()	理解为有0个或者多个	一般用于更新
存储函数	FUNCTION	SELECT 存储函数()	只能是一个	一般用于查询结果为一个值并返回时

此外,存储函数可以放在查询语句中使用,存储过程不行.反之,存储过程的功能更加强大,包括能够执行对表的操作(比如创建表,删除表)和事务操作,这些功能是存储函数不具备的

存储过程和函数的查看,修改,删除

查看

创建完成过后,如何知道存储函数和存储过程是否成功了呢

用户可以使用 SHOW STATUS 语句或者 SHOW CREATE 语句来查看,也可以直接从系统的 information_schema 数据库中查询

使用SHOW CREATE 语句查看存储过程和函数的创建信息

-- 语句结构
SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或者函数名

-- 举例
SHOW CREATE FUNCATION test_function \G;

使用SHOW STATUS 语句查看存储过程和函数的状态信息

-- 语法结构
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
-- 这个语句返回子程序的特征,如数据库,名字,类型,创建者以及创建和修改时间
-- [LIKE 'pattern']: 匹配存储过程或函数的名称,可以省略.当省略不写时,会列出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)

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

--Mysql中存储过程和函数的信息都存储在 information_schema 数据库下的 Routines 表中.可以通过查询该表的记录来查询存储过程和函数的信息
-- 语法结构
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME = '存储过程或函数名' [AND ROUTINE_TYPE = {'PROCEDURE | FUNCTION'}];
-- 注意: 如果在MySQL数据库中存在存储过程和函数名称相同的情况,最好指定routine_type查询条件来说明查询的是存储过程还是函数

-- 举例
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME ='count_by_id' AND ROUTINE_TYPE = 'FUNCTION' \G

修改

修改存储过程或者函数,不影响存储过程或者函数功能,只是修改相关特性,使用ALTER 语句实现

-- 语法结构
ALTER {PROCEDURE | FUNCTION} 存储过程名或者存储函数名 [characteristic ...]

-- 其中 characteristic 指定存储过程或者函数的特性,其读取信息与创建存储过程,函数时的取值信息略有不同
{ CONSTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
| SQL SECURITY { DEFINER | INVOKER}
| COMMENT 'string'

-- 修改存储过程使用`ALTER PROCEDURE`语句,修改存储函数使用`ALTER FUNCTION`语句.但是,这两个语句的结构是一样的,语句中的所有参数也是一样的

-- 举例
ALTER PROCEDURE countProc
MODIFIES SQL DATA
SQL SECURITY INVOKER;

-- 修改后查询
SELECT specific_name, sql_data_access, security_type
FROM information_schema.'ROUTINES'
WHERE routine_name = 'countProc' ADN routine_type = 'PROCEDURE';

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

删除

删除存储过程和函数,可以使用 drop 语句

-- 语法结构
DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名

IF EXISTS: 如果程序或者函数不存在,它可以防止发生错误,产生一个用SHOW WARNINGS查看的警告

存储过程的优缺点

存储过程的使用,一直存在很多争议.比如有些公司对于大型项目要求使用存储过程,而有些公司是明确禁止使用存储过程的.

优点

存储过程可以一次编译多次使用.存储过程只在创建时进行编译,之后的使用都不需要重新编译,可以提升sql的执行效率
可以减少开发工作量将代码封装成模块,实际上是编程的核心思想之一,这样可以把复杂的问题拆解成不同的模块,然后模块之间可以重复使用,在减少开发工作量的同时,还能保证代码的结构清晰
存储过程的安全性强.我们在设定存储过程的时候可以设置对用户的使用权限,这样就和视图一样具有较强的安全性
可以减少网络传输量.因为代码封装到存储过程中,每次使用只需要调用存储过程即可,这样就可以减少网络传输量
良好的封装性.在进行相对复杂的数据库操作时,原本需要一条一条的sql语句,可能要连接多次数据库才能完成的操作,现在变成了一次存储过程,只需要连接一次即可

缺点

基于上面这些优点，不少大公司都要求大型项目使用存储过程，比如微软、IBM 等公司。但是国内的阿里并不推荐开发人员使用存储过程，这是为什么呢？

阿里开发规范

【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。

可移植性差 存储过程不能跨数据库移植,比如在 MySQL,Oracle 和 SQL Server 里编写的存储过程,再换成其他数据库时就需要重新编写
调试困难,只有少数的DBMS支持存储过程的调试,对于复杂的存储过程来说,开发和维护都不容易,虽然也有一些第三方工具可以对存储过程进行调试,但是收费
存储过程的版本管理很困难.比如数据表索引发生变化了,可能会导致存储过程失效,我们在开发软件的时候往往需要进行版本管理,但是存储过程本身没有版本控制,版本迭代更新很麻烦
它不适合高并发的场景.高并发的场景需要减少数据库的压力,有时数据库会采用分库分表的方式,而且对可扩展性要求很高,在这种情况下,存储过程会变得难以维护,增加数据库的压力,显然就不适用了

MySQL存储过程(PROCEDURE)