【MySQL进阶之路 | 基础篇】存储过程

1. 存储过程概述

(1). 含义 : 存储过程就是一组经过预先编译的SQL语句的封装.

(2). 执行过程 : 存储过程预先存储在MySQL服务器上，需要执行的时候，客户端只需要向服务器端发送调用存储过程的命令，服务器端就可以把预先存储好的这一系列SQL语句全部执行.

(3). 好处 :

• 简化操作，提高了SQL语句的重用性，减少了程序员的压力.
• 减少操作过程中的失误，提高效率.
• 减少网络传输量(客户端不需要把所有的SQL语句通过网络发送给服务器).
• 减少了SQL语句暴露在网上的风险，也提高了数据查询的安全性.

(4). 和视图，函数的对比

• 它和视图有着相同的优点，清晰，安全，还可以减少网络传输量.不过它和视图不同，视图是虚拟表，通常不对底层数据表直接操作.而存储过程是程序化的SQL，可以直接操作底层数据表.相比面相集合的操作方式，能够实现一些更复杂的数据处理.
• 一旦存储过程被创建，它就像使用函数一样简单，直接调用存储过程名即可.相较于函数，存储过程是没有返回值的.

(5). 存储过程的分类

存储过程的参数类型可以是IN/OUT/INOUT

• 没有参数(无参数无返回).
• 仅仅带IN(有参数无返回).
• 仅仅带OUT(无参数有返回值).
• 既带IN又带OUT(有参数又有返回值).
• 带INOUT(有参数又有返回值).

IN/OUT/INOUT都可以在一个存储过程中带多个.

2. 创建存储过程

(1). 创建存储过程代码实例 :

# 在navicat中, 可以省略DELIMITER
DELIMITER $
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
        sql语句1;
        sql语句2;
END $
DELIMITER ;

(2). 参数列表只有IN类型参数.

(3). 参数列表只有OUT类型参数

(4). 参数列表既有IN又有OUT参数.

• IN : 表示参数为输入参数.存储过程只是读取这个参数的值.如果没有定义参数种类，默认是IN.表示输入参数.
• OUT : 当前参数为输出参数，表示出参.执行完后，调用这个存储过程的客户端或者应用程序就可以读取这个参数的返回值了.
• INOUT : 当前参数既可以是输入参数，也可以是输出参数.
• 形参类型可以的MySQL数据库中的任意类型.

3. 约束条件

characteristics 表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：

LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }| COMMENT 'string'

(1). LANGUAGE SQL ：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL.

(2). [NOT] DETERMINISTIC ：指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC.

(3). { 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.

(4). SQL SECURITY { DEFINER | INVOKER } ：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程.

• DEFINER 表示只有当前存储过程的创建者或者定义者才能执行当前存储过程.
• INVOKER 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程.

4. 说明

• BEGIN ... END : 中间包含了多条语句.每个语句以分号结尾.
• DECLARE : 用来声明局部变量.使用的位置在BEGIN ... END之间，而且需要在其他语句之前进行变量的声明.
• SET : 用于对变量的赋值.如会话用户变量，局部变量.
• SELECT ... INTO : 把从数据表中查询的结果放到变量中，即为变量赋值.
• DELIMITER 新的结束标记.MySQL默认的语句结束的符号为分号.为了避免存储过程中SQL语句的结束符相冲突(不然执行到第一个SQL语句遇到结尾的分号结束，其后的SQL语句也就不再作用了)，需要使用DELIMITER改变存储过程的结束符.
• 但navicat已经帮我们完成了这项工作，其使用了其他结束标记，所以上例我并没有使用DELIMITER.
• CALL 存储过程名(IN 变量名 变量类型 ...).我们使用CALL语句来调用存储过程.并且存储过程要与数据库相关.如果要执行其他数据库的存储过程，需要指定数据库名称，例如CALL

atguigudb.myprocedure(参数列表).

5. 关于DELIMITER的说明

• DELIMITER 关键字在MySQL中用于临时更改SQL语句的结束标志。默认情况下，MySQL使用分号(`;`)作为SQL语句的结束符，每当解析器遇到分号时，就会执行前面的SQL语句。然而，在编写复杂的数据库对象，如存储过程、函数、触发器等时，这些对象的定义中通常会包含多条SQL语句，每条语句间也使用分号分隔。如果不改变默认的分隔符，MySQL会在遇到第一个分号时尝试执行语句，执行残缺的创建存储过程语句当然是错的，所以导致编译错误。
• 通过使用DELIMITER命令，你可以设置一个新的结束符，使得MySQL能够在遇到这个新结束符时才认为整个复杂对象的定义完成并执行它，而不是在内部语句的分号处中断。这样就可以在定义中自由地使用分号，而不用担心它会提前终止命令.