23、MySQL存储过程(Stored Procedure)

一、概念

我们把若干条sql封装起来，起个名字 — 过程

把此过程经编译后存储在数据库中— 存储过程

存储过程（procedure）类似于C语言中的函数。
用来执行复杂的业务规则。

存储过程可以带参数，也可以返回结果。
存储过程可以包含数据DML语句、变量、逻辑控制语句等。

二、为什么要使用存储过程

1、优点

提供程序的性能。调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行
当创建，存储过程被编译之后，就存储在数据库中。
但是，MySQL实现的存储过程略有不同。 MySQL存储过程按需编译。
在编译存储过程之后，MySQL将其放入缓存中。MySQL为每个连接维护自己的存储过程高速缓存。
重用性
存储过程使用名字即可使用，也就是传说中的“一次编写，随便调用”。这样不仅提高了重用性，还减少了出错的几率，也会加快开发速度，可以说是一件非常好的事情。
提高开发效率
当业务复杂的时候，存储过程会减少工作量，为什么呢，原因很简单，如果我们不适用存储过程，那么就会导致我们先从数据库中取出来数据，然后经过计算，再放入到数据库中，这个开销还是蛮大的，这中间的开销包括我们的Java程序连接数据库获取结果集等若干操作，如果我们使用了存储过程，那么就没有那么多事了，直接在mysql内就搞定了。
提高数据库的安全性
由于存储过程也可以使用权限控制，而且参数化的存储过程可以防止SQL注入攻击，也在一定程度上保证了安全性。而且在一定程度上保证的了表的结构不暴露

2、缺点

增加服务器的负担
如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将会大大增加
开发和维护存储过程并不容易
开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能。
这可能会导致应用程序开发和维护阶段的问题。
很难调试存储过程
只有少数数据库管理系统允许您调试存储过程。而且，MySQL不提供调试存储过程的功能。
无法适应数据库的切割（水平或垂直切割）
数据库切割之后，存储过程并不清楚数据存储在哪个数据库中

三、基础语法

1、创建存储过程

基础语法格式

CREATE PROCEDURE 存储过程名字()
BEGIN
SQL语句/逻辑
END；

说明
- CREATE PROCEDURE
  创建存储过程的关键字
- BEGIN … END
  编写SQL逻辑,注意END后面的分号 ;

示例代码

CREATE PROCEDURE pd_add()
begin
  select 1+1 ;
end;

2、调用存储过程

语法格式
```
CALL 存储过程名()
```
示例代码
```
CALL pd_add()
```
注意
不管有没有参数,都必须加()

3、删除存储过程

语法格式

DROP  PROCEDURE  if exists  存储过程名字 [,存储过程名字2…]；

注意
- 这里存储过程名字后面没有（）
示例代码
```
DROP  PROCEDURE  if exists  pd_add
```

四、详解

1、变量

说明
存储过程是可以编程的，意味着可以使用变量，表达式，控制结构来完成复杂的功能，常用的两种类型的变量:
- 存储过程变量(局部变量)，在存储过程中，用declare声明变量,主要用在存储过程中或者是给存储传参数中,
- 会话变量(用户变量)，使用set或select直接赋值，变量名以@开头，在客户端链接到数据库实例整个过程中用户变量都是有效的
两者的区别是：
首先在作用域上的区别
1. 用户变量可以在会话中，在会话中任何位置定义，
2. 但局部变量只能定义在begin end 之中。
  另外在语法结构上有细小的差别
3. 用户变量必须加@符号，不用限定类型。
4. 局部变量一般不用加@符号，需要限定类型

语法格式

-- 存储过程变量
DECLARE 变量名 变量类型 [ default 默认值 ]
-- 赋值 此处不需要符号@
set 变量=值

-- 会话变量
-- 主要考虑到=符号用于比较运算符，所以为了区分加个冒号。
set @变量名= 表达式  或 set @变量:= 表达式
--  或者
select @num:=1; 或 select @num:=字段名 from 表名 where ……，
-- 注意上面两种赋值符号，
-- 1. 使用set时可以用“=”或“:=”，
-- 2. 但是使用select时必须用“:=赋值”

示例代码(用户变量)

-- 1. 创建存储过程
CREATE PROCEDURE pd_base_var_select()
BEGIN
    --  3 使用select
    SELECT @goods_name := goods_name FROM goods WHERE id = 1;
END;
 --  2. 调用存储过程
 CALL  pd_base_var_select()

-- 1. 创建存储过程
CREATE PROCEDURE pd_base_var_set()
BEGIN
     -- 3 声明会话变量
    SET @age = 1;
    SET @v1:= 2;
END;
--  2. 调用存储过程
CALL pd_base_var_set();
-- 4. 显示数据
SELECT @age + @v1

2、if选择控制结构

语法格式

if 条件 then
   SQL语句/逻辑
elseif 条件  then
  SQL语句/逻辑
else
  SQL语句/逻辑
end if;

示例代码

-- 1. 创建存储过程
CREATE PROCEDURE pd_base_if()
BEGIN
    DECLARE sal decimal(10, 2) DEFAULT 0.00;
    SET sal = 18000.00;
    SET @level = 'p4';
    IF sal < 15000 THEN
        SET @level = 'p5';
    ELSEIF sal BETWEEN 15000.00 AND 25000.00 THEN
        SET @level = 'p6';
    ELSEIF sal BETWEEN 25001.00 AND 35000.00 THEN
        SET @level = 'p7';
    ELSEIF sal BETWEEN 35001.00 AND 45000.00 THEN
        SET @level = 'p8';
    ELSE
        SET @level = '你啥也不是';
    END IF;
    SELECT @level; -- 显示数据
END;
-- 2. 调用存储过程
CALL pd_base_if()

3、while循环结构

语法格式

WHILE 条件 DO
 SQL语句/逻辑
END WHILE;

示例代码

-- 1. 创建存储过程
CREATE PROCEDURE pd_base_while()
BEGIN
    DECLARE total int DEFAULT 0;
    DECLARE num int DEFAULT 0;
    WHILE num <= 100 DO
        SET total = total + num;
        SET num = num + 1;
    END WHILE;
    SELECT total;
END;
-- 2. 调用存储过程
CALL pd_base_while()

4、参数

说明
在定义存储过程的括号中，可以声明参数，有三种类型的参数:输入参数,输出参数,输入输出参数

语法格式

[IN | OUT | INOUT 参数名 参数类型]

参数
- IN 输入参数: 表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
- OUT 输出参数: 该值可在存储过程内部被改变，并可返回
- INOUT 输入输出参数: 调用时指定，并且可被改变和返回

输入参数

--
CREATE PROCEDURE pd_shop_by_id(IN gid int)
BEGIN
    SELECT * FROM goods g WHERE g.id = gid;
END;
DROP PROCEDURE pd_shop_by_id;
-- 调用存储过程
CALL pd_shop_by_id(1);

输出参数

-- 1. 创建存储过程,声明输出参数
CREATE PROCEDURE pd_shop_out(OUT max_price decimal(8,2), OUT min_price decimal(8,2))
BEGIN
     -- INTO 后面跟输出参数
    SELECT MAX(goods_price)  INTO max_price ,MIN(goods_price)  INTO min_price FROM goods;
END;
-- 2. 调用存储过程
CALL pd_shop_out(@max,@min);
-- 3. 显示查询的数据
SELECT @max max_price,@min min_price

区别
- in: 表示输入一个值，你需要一个值，我给你一个值
- out: 你往外输出一个值，你输出的那个值我就拿一个变量来接收你给我输出的那个值
总结
- 如果仅仅想把数据传给MySQL存储过程，那就用in类型参数；
- 如果仅仅从MySQL存储过程返回值，那就用out类型参数；
- 如果需要把数据传给MySQL存储过程经过计算再传回给我们，那就用inout类型参数。

五、附

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '商品ID',
  `goods_name` varchar(16) DEFAULT NULL COMMENT '商品名称',
  `goods_title` varchar(64) DEFAULT NULL COMMENT '商品标题',
  `goods_img` varchar(64) DEFAULT NULL COMMENT '商品的图片',
  `goods_detail` longtext COMMENT '商品的详情介绍',
  `goods_price` decimal(10,2) DEFAULT '0.00' COMMENT '商品单价',
  `goods_stock` int(11) DEFAULT '0' COMMENT '商品库存，-1表示没有限制',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
BEGIN;
INSERT INTO `goods` VALUES (1, 'iphoneX', 'Apple iPhone X(A1865) 64G 银色 移动联通电信4G手机', '/img/iphonex.png', 'Apple iPhone X(A1865) 64G 银色 移动联通电信4G手机', 8765.00, 10000);
INSERT INTO `goods` VALUES (2, '华为Mate9', '华为Mate9 4GB+32GB版 月光银 移动联通电信4G手机 双卡双待', '/img/meta10.png', 'Apple iPhone X(A1865) 64G 银色 移动联通电信4G手机', 3212.00, 1000);
COMMIT;