MYSQL 存储过程

参考：https://www.cnblogs.com/mark-chan/p/5384139.html

参考：https://www.runoob.com/w3cnote/mysql-stored-procedure.html

参考：https://blog.csdn.net/Servlet905/article/details/73087819

参考：https://blog.csdn.net/pan_junbiao/article/details/85912910?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase

一、定义

MySQL 5.0 版本开始支持存储过程。

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

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

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

二、优点

• 存储过程可封装，并隐藏复杂的商业逻辑。
• 存储过程保存在数据库，而不是本地，可以共享，方便协作。
• 存储过程因为SQL语句已经预编绎过了，因此运行的速度比较快。
• 存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值。可以向程序返回错误原因。
• 使用了存过程，很多相似性的删除，更新，新增等操作就变得轻松了，并且以后也便于管理。
• 存储过程运行比较稳定，不会有太多的错误。只要一次成功，以后都会按这个程序运行。
• 存储过程主要是在服务器上运行，减少对客户机的压力。
• 存储过程可以包含程序流、逻辑以及对数据库的查询。
• 存储过程可以在单个存储过程中执行一系列SQL语句。存储过程可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。

三、创建

CREATE DEFINER = CURRENT_USER PROCEDURE `sp_name`()
BEGIN
    #Routine body goes here...

END;

CURRENT_USER：比如我的账号名是：“sql” .那么 CURRENT_USER就是：`sql`@`%`;如果账号名是“root”,

那么 CURRENT_USER就是：`root`@`%`

sp_name:存储过程的名称。

(): 里面需要书写参数，输入、输出参数或者输入输出参数。
比如：

CREATE DEFINER=`sql`@`%` PROCEDURE `演示`(IN `start_time` date,OUT `end_time` date, INOUT `day` INT)
BEGIN
    #Routine body goes here...    
    
END

IN `start_time` date: IN:输入类型（OUT:输出类型，INOUT输入输出类型）；`start_time`：参数   date:参数类型

四、调用

call sp_name()

注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递

五、删除存储过程

DROP PROCEDURE sp_name

六、更改存储过程

有专门的语句。但是我更喜欢进入到sql存储过程中更改。

七、查询

SHOW PROCEDURE STATUS WHERE db='db_name' -- db_name 数据库名

八、定义变量与IN、OUT、INOUT

搞清楚DECLARE；SELECT；SET就行了。

使用SET或者SELECT直接赋值，变量名以@开头

可以在会话的任何地方声明，作用域是整个会话，称为会话变量，也是全局变量。

以DECLARE关键字声明的变量，只能在存储过程中使用，称为存储过程变量。 这种变量需要设置变量类型，而且只存在  begin....end  这段内

两者区别：

在调用存储过程时，DECLARE声明的变量都会别初始化为null，而会话变量（以@开头的变量）则不会再被初始化，在一个会话内只被初始化一次，之后是对上一次会话的计算结果，相当于这个会话的全局变量。

下面是四个同样结果的存储过程：

8.1 DECLARE

CREATE DEFINER=`sql`@`%` PROCEDURE `date_circle`()
BEGIN
    declare begin_date date ;
    declare end_date  date ;    
    set end_date = DATE_FORMAT('2020-06-10','%Y-%m-%d');
    set begin_date = date_sub(end_date,interval 10 day);
    while begin_date <= end_date do
        insert into text.`循环测试`  values (begin_date);
        set begin_date = date_add(begin_date,interval 1 day);
    end while;
END

8.2  SET

CREATE DEFINER=`sql`@`%` PROCEDURE `date_circle_set`()
BEGIN
    set @end_date = DATE_FORMAT('2020-06-11','%Y-%m-%d');
    set @begin_date = date_sub(@end_date,interval 10 day);
    while @begin_date <= @end_date do
        insert into text.`循环测试`  values (@begin_date);
        set @begin_date = date_add(@begin_date,interval 1 day);
    end while;
END

8.3 SELECT

注意select赋值方式由两种。

CREATE DEFINER=`sql`@`%` PROCEDURE `date_circle_select`()
BEGIN
    SELECT DATE_FORMAT('2020-06-10','%Y-%m-%d') INTO @end_date;
    SELECT date_sub(@end_date,interval 10 day) INTO @begin_date; 
    SELECT count(*) into @cout_row  from text.`循环测试`;     -- 第一种从表格取数的方法，不会在调用时进行输出
    while @begin_date <= @end_date do
        insert into text.`循环测试`  values (@begin_date);
        set @begin_date = date_add(@begin_date,interval 1 day);
    end while;
    SELECT @cout_row:= count(*) from text.`循环测试`;        -- 第二种从表格取数的方法,会在调用时进行输出
END

8.4 说明

个人比较喜欢declare方式，但是大部分情况下是混用。

 8.5  IN、OUT、 INOUT

IN为输入，OUT为输出、INOUT为输入输出。

下面没有INOUT的例子。这里写一个INOUT的例子。主要在调用的时候。

8.5.1 存储过程创建

CREATE DEFINER=`sql`@`%` PROCEDURE `example_inout`( INOUT num int )
begin
    set num=num+1;
end

8.5.1 结果输出

SET @num=100;
CALL example_inout(@num);
SELECT @num;              -- 输出101

九、控制语句

相对于普通语句，实现复制逻辑的关键所在。

包括：IF语句、CASE语句、WHILE循环语句、LOOP循环语句、REPEAT循环语句、LEAVE语句和ITERATE语句，它们可以进行流程控制。

9.1 变量作用域。

内部变量在其作用域范围内享有更高的优先权，当执行到end时，内部变量消失，不再可见了，在存储
过程外再也找不到这个内部变量，但是可以通过out参数或者将其值指派给会话变量来保存其值。

9.2 IF语句 

9.2.1 存储过程创建

CREATE DEFINER=`sql`@`%` PROCEDURE `example_if`(IN x VARCHAR(50))
BEGIN
    IF x = 'IF' THEN
        SELECT 'IF';
    ELSEIF x = 'ELSEIF' THEN 
        SELECT 'ELSEIF';
    ELSE
        SELECT 'ELSE';
    END IF;    
END;

9.2.2 结果输出

CALL example_if("if");       -- 输出：IF
CALL example_if("ELSEIF") ;  -- 输出：ELSEIF
CALL example_if(5)           -- 输出：ELSE 

9.2 CASE语句 

CASE语句为多分支语句结构，该语句首先从WHEN后的VALUE中查找与CASE后的VALUE相等的值，如果查找到则执行该分支的内容，否则执行ELSE后的内容。CASE语句表示形式如下：

9.2.1 存储过程创建

有两种形式

CREATE DEFINER=`sql`@`%` PROCEDURE `example_case`(IN x INT)
BEGIN
    CASE x/ABS(X)
        WHEN 1 THEN SELECT 1;
        WHEN -1 THEN SELECT -1;
        ELSE SELECT 0;
    END CASE;
END

CREATE DEFINER=`sql`@`%` PROCEDURE `example_case`(IN x INT)
BEGIN
    CASE 
        WHEN x/ABS(X)=1 THEN SELECT 1;
        WHEN x/ABS(X)=-1 THEN SELECT -1;
        ELSE SELECT 0;
    END CASE;
END

9.2.2 结果输出

CALL example_case(10);       -- 输出：1
CALL example_case(-10) ;      -- 输出：-1
CALL example_case(0)   ;       -- 输出：0 

9.3 WHILE循环语句

9.3.1 存储过程创建

CREATE DEFINER=`sql`@`%` PROCEDURE `example_while`(IN `a` INT, IN `b` INT, OUT `c` INT)
BEGIN
    SET c = 0;
    WHILE a <= b DO
        set c = c + a;
        set a = a + 1;    
    END WHILE;
END

9.3.2 结果输出

CALL example_while(1,10,@a);
SELECT @a;                     -- 输出： 55

9.4 LOOP循环语句

LOOP循环没有内置的循环条件，但可以通过LEAVE语句退出循环。

示例：使用LOOP循环语句求前100的和。

9.4.1 存储过程创建

CREATE DEFINER=`sql`@`%` PROCEDURE `example_loop`(OUT sum INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE s INT DEFAULT 0;
    
    loop_label:LOOP
        SET s = s+i;
        SET i = i+1;
    
        IF i>100 THEN
            -- 退出LOOP循环
            LEAVE loop_label;  
        END IF;
    END LOOP;
 
    SET sum = s;
END

9.4.2 结果输出

CALL example_loop(@a);
SELECT @a;                  -- 输出5050

9.5 REPEAT循环语句

示例：使用REPEAT循环语句求前100的和。

9.5.1 存储过程创建

CREATE PROCEDURE example_repeat(OUT sum INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE s INT DEFAULT 0;
 
    REPEAT
        SET s = s+i;
        SET i = i+1;
        
        UNTIL i > 100
    END REPEAT;
    
    SET sum = s;
END;

9.5.2 结果输出

CALL example_repeat(@a);
SELECT @a;                  -- 输出5050

9.6 iterate语句

ITERATE语句可以出现在LOOP、REPEAT和WHILE语句内，其意为“再次循环”；

该语句的格式与LEAVE大同小异，区别在于：LEAVE语句是离开一个循环，而ITERATE语句是重新开始一个循环。

示例：求10以内奇数值的和。

9.6.1 存储过程创建

CREATE DEFINER=`sql`@`%` PROCEDURE `example_iterate`(OUT sum INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE s INT DEFAULT 0;
    
    loop_label:LOOP
 
        SET i = i+1;        
 
        IF i > 10 THEN
            -- 退出整个循环
            LEAVE loop_label;  
        END IF;
 
        IF (i mod 2) THEN
            SET s = s+i;
        ELSE
            -- 退出本次循环，继续下一个循环
            ITERATE  loop_label;
        END IF;
 
    END LOOP;
 
    SET sum = s;
END

9.6.2 结果输出

CALL example_iterate(@a);
SELECT @a;                     -- 输出： 25