测试环境

mysql-5.7.20 -log , win10

为什么需要存储过程

一组可编程的函数，是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

优势：

①将重复性很高的一些操作，封装到一个存储过程中，简化了对这些SQL的调用

②批量处理：SQL+循环，减少流量，也就是“跑批”

③统一接口，确保数据的安全

创建存储过程

CREATE [DEFINER = { user | CURRENT_USER }] 　PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type characteristic: COMMENT 'string' | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } routine_body: 　　Valid SQL routine statement [begin_label:] BEGIN 　　[statement_list] 　　　　…… END [end_label] 

欢迎大家来到IT世界,在知识的湖畔探索吧!

测试表

创建存储过程并执行

欢迎大家来到IT世界,在知识的湖畔探索吧!IN输入参数类型，OUT输出参数类型，而INOUT既是输入类型又是输出类型，所谓的IN输入参数类型就是把要传递的参数输入到存储过程的内部以便编写存储过程流程语句时可以使用。上述演示过的例子就是这种类型。关键字OUT则是指明相应参数用来从存储过程传出的一个值,也可以理解为存储过程的返回值，而对于INOUT则是两者结合体 

默认情况下，存储过程和默认数据库相关联，如果想指定存储过程创建在某个特定的数据库下，那么在过程名前面加数据库名做前缀；

在定义过程时，使用DELIMITER $ 命令将语句的结束符号从分号 ; 临时改为两个$，使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释。

下面演示OUT参数，创建一个存储过程，用来输出level的最大值，最小值和平均值

OUT参数示例

存储体过程

存储过程体包含了在过程调用时必须执行的语句，例如：dml、ddl语句，if-then-else和while-do语句、声明变量的declare语句等

过程体格式：以begin开始，以end结束(可嵌套)

BEGIN 　　BEGIN 　　　　BEGIN 　　　　　　statements; 　　　　END 　　END END 

每个嵌套块及其中的每条语句，必须以分号结束，表示过程体结束的begin-end块(又叫做复合语句compound statement)，则不需要分号。

为语句块贴标签

欢迎大家来到IT世界,在知识的湖畔探索吧![begin_label:] BEGIN 　　[statement_list] END [end_label]sh 示例 label1: BEGIN 　　label2: BEGIN 　　　　label3: BEGIN 　　　　　　statements; 　　　　END label3 ; 　　END label2; END label1 标签的作用： ①增强代码的可读性 ②在某些语句(例如:leave和iterate语句)，需要用到标签 

删除存储过程

DROP PROCEDURE [IF EXISTS] 存储过程名称;

查看存储过程的状态

SHOW PROCEDURE STATUS [LIKE ‘pattern’]

查看存储过程的创建语句

SHOW CREATE PROCEDURE 存储过程名;

存储过程的流程控制语句

1.IF 条件语句

IF 条件表达式1 THEN 条件表达式1为true执行 [ELSEIF 条件表达式2 THEN 条件表达式2为true执行 ] [ELSE 全部条件为false时执行] END IF； 

2.多分支条件语句

CASE 表达式1 WHEN 值1 THEN 表达式=值1时执行命令 [WHEN 值N THEN 表达式=值N时执行该语句] [ELSE 上述值以外执行该语句] END CASE 

3.repeat 循环控制语句

REPEAT 直至条件表达式为True时执行的语句 UNTIL 条件表达式 END REPEAT; 

4.while循环控制语句

WHILE 条件表达式 DO 系列语句 END WHILE 

使用DECLARE定义局部变量

DECLARE 变量名[,变量名2...] 数据类型(type) [DEFAULT value]; -- 定义变量num，数据类型为INT型，默认值为10 DECLARE num INT DEFAULT 10 ; 

DECLARE关键字是用来声明变量的；变量名即变量的名称，这里可以同时定义多个变量；type参数用来指定变量的类型；DEFAULT value子句将变量默认值设置为value，没有使用DEFAULT子句时，默认值为NULL。声明后，我们就可以在存储过程使用该变量，设置变量值可以使用以下语法：

SET 变量名1 = expr [, 变量名2 = expr] ... 

其中，SET关键字是用来为变量赋值的；expr参数是赋值表达式或某个值。一个SET语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。除了这种赋值方式，前面我们还提到过使用SELECT…INTO语句为变量赋值，那也是可行的。

其他变量定义

1.用户变量：以”@”开始，形式为”@变量名”，用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效，声明或者定义用户变量使用set语句，如 set @var 若没有指定GLOBAL 或SESSION ，那么默认将会定义用户变量。

mysql> SET @@global.sort_buffer_size=value 

2.全局变量：定义时，以如下两种形式出现，set GLOBAL 变量名 或者 set @@global.name，对所有客户端生效。只有具有super权限才可以设置全局变量。

mysql> SET GLOBAL sort_buffer_size=value; 

3.会话变量：只对连接的客户端有效。

mysql> SET SESSION sort_buffer_size=value;