⑩⑤【DB】详解MySQL存储过程：变量、游标、存储函数、循环，判断语句、参数传递..

悠悠 2024-04-17 10:18 17阅读 0赞

![在这里插入图片描述][8d8e0d8a542946af858489a536162608.gif_pic_center]

> 个人简介：Java领域新星创作者；阿里云技术博主、星级博主、专家博主；正在Java学习的路上摸爬滚打，记录学习的过程~  
> 个人主页：[.29.的博客][.29.]  
> 学习社区：[进去逛一逛~][Link 1]

> [![在这里插入图片描述][ff95d349de5a4059b4211448c7f4551d.png_pic_center]][ff95d349de5a4059b4211448c7f4551d.png_pic_center 1]

#### MySQL存储过程 ####

*  
 *   *  1. 介绍
     *  2. 使用
     *  3. 变量
     *   *  ①系统变量
         *  ② 用户定义变量
         *  ③ 局部变量
     *  4. 条件判断语句IF
     *  5. 参数传递
     *  6. CASE语句
     *  7. 循环语句
     *   *  ①while循环
         *  ②repeat循环
         *  ③loop循环
     *  8. 游标 和 条件处理程序
     *   *  ①游标 cursor
         *  ②条件处理程序 Handler
         *  ③使用案例
     *  9. 存储函数

--------------------

##  ##

### 1. 介绍 ###

`存储过程`：

*  ?**什么是存储过程？**
 *   *  `存储过程`是事先**经过编译并存储在数据库中的一段SQL语句的集合**，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，**对于提高数据处理的效率是有好处的**。
     *  存储过程思想上很简单，**就是数据库SQL语言层面的代码封装与重用。**

*  ?**存储过程的特点？**
 *   *  代码的**封装，复用**。
     *  可以**接收参数**，也可以**返回数据**。
     *  减少网络交互，**提升效率**。

--------------------

### 2. 使用 ###

`存储过程的使用`：

*  ?**创建存储过程：**
 *   *      -- DELIMITER关键字将SQL语句结束符号改为‘$$’，在创建存储过程后再改回‘;’
            -- 这是为了避免SQL语句的结束符号与END结束符号冲突，导致1064异常
            DELIMITER $$
            
            CREATE PROCEDURE 存储过程名称([参数列表])
            BEGIN
            	-- SQL语句
            END$$
            
            DELIMITER ;
     *

*  ?**存储过程的调用：**
 *   *      CALL 存储过程名称([参数]);
     *

*  ?**查看存储过程的信息：**
 *   *      -- 查询指定数据库的存储过程及状态信息
            SELECT * FROM INFORMATION_SCHEMA.`ROUTINES` WHERE ROUTINE_SCHEMA = '数据库名称';
            
            -- 查询某个存储过程的定义语句
            SHOW CREATE PROCEDURE 存储过程名称;
     *

*  ?**删除存储过程：**
 *   *      DROP PROCEDURE [IF EXISTS] 存储过程名称;
     *

--------------------

### 3. 变量 ###

#### ①系统变量 ####

`系统变量`：

*  ⚪`系统变量`：是MySQL服务器提供，不是用户定义的，属于服务器层面。分为`全局变量(GLOBAL)`、`会话变量(SESSION)`。

*  ?查看系统变量：
 *   *      #SESSION 或 GLOBAL 如果不指定，默认为SESSION
            -- 查看所有系统变量
            SHOW [SESSION | GLOBAL] VARIABLES;
            
            -- 通过LIKE模糊匹配方式查看系统变量
            SHOW [SESSION | GLOBAL] VARIABLES LIKE '....';
            
            -- 查看指定系统变量的值
            SELECT @@[SESSION | GLOBAL].系统变量名;
     *   *      -- 演示
                
                -- 查看所有session级别系统变量
                SHOW VARIABLES;
                #或
                SHOW SESSION VARIABLES;
                
                -- 模糊匹配AUTO开头的系统变量
                SHOW SESSION VARIABLES LIKE 'AUTO%';
                SHOW GLOBAL VARIABLES LIKE 'AUTO%';
                
                -- 查看名为AUTOCOMMIT的系统变量
                SELECT @@GLOBAL.AUTOCOMMIT;
                SELECT @@SESSION.AUTOCOMMIT;
         *

*  ?设置系统变量
 *   *      #SESSION 或 GLOBAL 如果不指定，默认为SESSION
            SET [SESSION | GLOBAL] 系统变量名 = 自定义值;
            SET @@[SESSION | GLOBAL].系统变量名 = 自定义值;
     *

`注意`：

*  设置或查看系统变量时，SESSION 或 GLOBAL 如果不指定，\*\*默认为SESSION \*\*。
 *  MySQL服务重新启动后，所设置的全局变量都会重置，想要不失效，可以在配置文件`/etc/my.cnf文件`中配置。

#### ② 用户定义变量 ####

`用户定义变量`：

*  ⚪`用户定义变量`：是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“`@变量名`”使用就可以。其`作用域为当前连接`。

*  ?用户定义变量的赋值\*\*（4种方式）\*\* ：
 *   *      -- var_name:用户定义变量名，由用户自定义
            -- expr:用户定义变量的值，由用户自定义
            #方式一：
            SET @var_name = expr [,@var_name = expr]...;
            #方式二:
            SET @var_name := expr [,@var_name := expr]...;
            #方式三：
            SELECT @var_name := expr [,@var_name := expr]...;
            #方式四(将查询结果赋值给变量)：
            SELECT 字段名 INTO @var_name FROM 表名;
     *

*  ?用户定义变量的使用：
 *   *      -- var_name:用户定义变量名，由用户自定义
            SELECT @var_name [,@var_name...];
     *

`注意`：用户定义的变量无需对其进行声明或初始化，不声明或初始化获取到的值为NULL。

#### ③ 局部变量 ####

`局部变量`：

*  ⚪`局部变量`：是根据需要定义的在局部生效的变量，访问之前，需要`DECLARE`声明。可用作存储过程内的局部变量和输入参数，局部变量的`范围`是在其内声明的`BEGIN...END`块。

*  ?声明局部变量：
 *   *      DECLARE 变量名 变量类型 [DEFAULT...];
     *  `变量类型`：就是数据库字段类型：INT,BIGINT,CHAR,VARCHAR,DATE,TIME等。

*  ?为局部变量赋值：
 *   *      SET 变量名 = 值;
            SET 变量名 := 值;
            SELECT 字段名 INTO 变量名 FROM 表名 WHERE 查询条件...;
     *

--------------------

### 4. 条件判断语句IF ###

*  ? **语法** ：
 *   *      IF 条件1 THEN
            	-- 执行逻辑
            ELSEIF 条件2 THEN   -- 可选
            	-- 执行逻辑
            ELSE               -- 可选
            	-- 执行逻辑
            END IF;
     *  
     *   *      -- 演示
                /*
                给成绩打分
                分数score >= 80 结果result就是优秀
                分数80 > score >= 60 结果result就是优秀
                分数score < 60 结果result就是不及格
                */
                DELIMITER $$
                
                CREATE PROCEDURE p3()
                BEGIN
                	DECLARE score INT DEFAULT 58;
                	DECLARE result VARCHAR(10);
                	
                	IF score >= 80 THEN
                		SET result = '优秀';
                	ELSEIF score >= 60 THEN
                		SET result = '合格';
                	ELSE
                		SET result = '不合格';
                	END IF;
                	SELECT result;
                END$$
                
                DELIMITER ;
         *

--------------------

### 5. 参数传递 ###

`参数`：

![在这里插入图片描述][b3dfd145c8a941239e5f518da28d2175.png]

*  ?参数传递的用法：
 *   *      DELIMITER $$
            
            CREATE PROCEDURE 存储过程名称([IN | OUT | INOUT 参数名 参数类型])
            BEGIN
            	-- SQL语句
            END$$
            
            DELIMITER ;
     *  
     *   *      -- 演示
                /*
                1. 根据传入(in)参数score，判定当前分数对应的分数等级，并返回(out)
                分数score >= 80 结果result就是优秀
                分数80 > score >= 60 结果result就是优秀
                分数score < 60 结果result就是不及格
                */
                DELIMITER $$
                
                CREATE PROCEDURE p4(IN score INT,OUT result VARCHAR(10))
                BEGIN
                	IF score >= 80 THEN
                		SET result = '优秀';
                	ELSEIF score >= 60 THEN
                		SET result = '合格';
                	ELSE
                		SET result = '不合格';
                	END IF;
                END$$
                
                DELIMITER ;
                
                -- 调用存储过程，查看返回结果。
                CALL p4(100,@result);
                SELECT @result;
                
                
                -- 2. 传入两百分制数，转换为一百分制数输出(inout)
                DELIMITER $$
                
                CREATE PROCEDURE p5(INOUT score DOUBLE)
                BEGIN
                	SET score = score * 0.5;
                END$$
                
                DELIMITER ;
                
                -- 设置自定参数传入，获取转换后的参数。
                SET @score = 180;
                CALL p5(@score);
                SELECT @score;
         *

--------------------

### 6. CASE语句 ###

*  ?CASE语法一：
 *  如果`when_value = case_value`，就会执行对应THEN后面的`statement_list`逻辑
 *   *      -- 存储过程中使用CASE
            DELIMITER $$
            
            CREATE PROCEDURE 存储过程名称([IN | OUT | INOUT 参数名 参数类型])
            BEGIN
                CASE case_value
                    WHEN when_value1 THEN statement_list1
                    [WHEN when_value2 THEN statement_list2]
                    [ELSE statement_list]
                END CASE;
            END$$
            
            DELIMITER ;
     *

*  ?CASE语法二：
 *  如果`search_condition`的结果为TRUE，就会执行对应THEN后面的`statement_list`逻辑
 *   *      -- 存储过程中使用CASE
            DELIMITER $$
            
            CREATE PROCEDURE 存储过程名称([IN | OUT | INOUT 参数名 参数类型])
            BEGIN
                CASE
                    WHEN search_condition1 THEN statement_list1
                    [WHEN search_condition2 THEN statement_list2]
                    [ELSE statement_list]
                END CASE;
            END$$
            
            DELIMITER ;
     *

--------------------

### 7. 循环语句 ###

#### ①while循环 ####

`while`：

*  while循环是有条件的循环控制语句。**满足条件后，再执行**循环体中的SQL语句。具体语法为：
 *   *      -- 先判定条件，条件结果为TRUE则执行逻辑，否则不执行语句。
            WHILE 条件 DO
            	#SQL逻辑
            END WHILE;
     *      -- 存储过程中使用WHILE
            DELIMITER $$
            
            CREATE PROCEDURE 存储过程名称([IN | OUT | INOUT 参数名 参数类型])
            BEGIN
                WHILE 条件 DO
                    #SQL逻辑
                END WHILE;
            END$$
            
            DELIMITER ;
     *

#### ②repeat循环 ####

`repeat`:

*  repeat是有条件的循环控制语句，当**满足条件的时候退出循环** 。具体语法为：
 *   *      -- 先执行一次逻辑，然后判定逻辑是否为True，如果True则退出循环，不满足判定则继续循环。
            REPEAT
            	#SQL逻辑
            	UNTIL 条件
            END REPEAT;
     *      -- 存储过程中使用REPEAT
            DELIMITER $$
            
            CREATE PROCEDURE 存储过程名称([IN | OUT | INOUT 参数名 参数类型])
            BEGIN
                REPEAT
                    #SQL逻辑
                    UNTIL 条件
                END REPEAT;
            END$$
            
            DELIMITER ;
     *

#### ③loop循环 ####

`loop`：

*  `LOOP`实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP可以**配合一下两个语句使用**：
 *   *  `LEVEL`：配合循环使用，退出循环。
     *  `ITERATE`：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

*  ?loop循环语法：
 *   *      -- begin_label: 是标记
            -- end_label 是结束标记
            [begin_label:] LOOP
            	#SQL逻辑
            END LOOP [end_label];
     *      -- 退出指定label标记的循环体
            LEVEL label;
            
            -- 跳过本次循环，直接进入下一次循环
            ITERATE label;
     *

--------------------

### 8. 游标 和 条件处理程序 ###

#### ①游标 cursor ####

`cursor`：

*  `游标(CURSOR)`：
 *  是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的`声明`、`OPEN`、`FETCH`和`CLOSE`。

*  ?游标的声明：
 *   *      DECLARE 游标名称 CURSOR FOR 查询语句;
     *

*  ?打开游标：
 *   *      OPEN 游标名称;
     *

*  ?获取游标记录：
 *   *      FETCH 游标名称 INTO 变量1[,变量2,...];
     *

*  ?关闭游标：
 *   *      CLOSE 游标名称;
     *

#### ②条件处理程序 Handler ####

`Handler`：

*  `条件处理程序(Handler)`：
 *  可以用来**定义在流程控制结构执行过程中遇到问题时相应的处理步骤** 。具体语法为：
 *   *      -- 语法
            DECLARE handler_action HANDLER FOR
            condition_value [,condition_value,...] 
            statement;
            
            -- handler_action的解释：
            CONTINUE #继续执行当前程序
            EXIT     #终止执行当前程序
            
            -- condition_value的解释：
            SQLSTATE 'lstate_value' #状态码，如02000
            SQLWARNING		#所有01开头的SQLSTATE代码的简写
            NOT FOUND		#所有02开头的SQLSTATE代码的简写
            SQLEXCEPTION	#所有没有被SQLWARNING或NOT FOUND捕获的代码的简写
            
            -- statement解释
            /*
            程序满足condition_value就会执行handler_action，
            执行完handler_action操作后就会执行statement的操作
            */
     *

#### ③使用案例 ####

*  `演示`：
 *   *  > – 要求：  
        > /\*  
        > 根据传入的参数uage,来查询用户表tb\_user中，  
        > 所有的用户年龄小于等于uage的用户姓名(name)和专业（profession),  
        > 并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。  
        > \*/
        > 
        > – 逻辑：  
        > – 1.声明游标，存储查询结果集  
        > – 2.准备：创建表结构  
        > – 3.开启游标  
        > – 4.获取游标中的记录  
        > – 5.插入数据到新表  
        > – 6.关闭游标
 *   *      #修改结束标志，避免代码无法正常执行完毕
            DELIMITER $$
            
            CREATE PROCEDURE p11(IN uage INT)
            BEGIN
            	#声明第4步使用的变量
            	DECLARE uname VARCHAR(50);
            	DECLARE uprofession VARCHAR(11);
            	
            	#1.声明游标，存储查询结果集
            	DECLARE u_cursor CURSOR FOR 
            	SELECT NAME,profession FROM tb_user 
            	WHERE age <= uage;
            	
            	#声明条件处理程序Handler,
            	#满足NOT FOUND就执行exit操作
            	#执行完后，关闭游标：CLOSE u_cursor。
            	-- 这个handler的目的是：若游标内没有数据，直接退出并关闭游标
            	DECLARE EXIT HANDLER FOR NOT FOUND CLOSE u_cursor;
            	
            	#2.准备：创建表结构
            	DROP TABLE IF EXISTS tb_user_pro;
            	
            	CREATE TABLE IF NOT EXISTS tb_user_pro(
            		id INT PRIMARY KEY AUTO_INCREMENT,
            		NAME VARCHAR(50) NOT NULL,
            		profession VARCHAR(11)
            	);
            	
            	#3.开启游标
            	OPEN u_cursor;
            	
            	#4.获取游标中的记录(循环)
            	WHILE TRUE DO
            		#获取游标数据存入变量
            		FETCH u_cursor INTO uname,uprofession;
            		#5.插入数据到新表
            		INSERT INTO tb_user_pro VALUES(NULL,uname,uprofession);
            		
            	END WHILE;
            	
            	#6.关闭游标
            	CLOSE u_cursor;
            END$$
            
            #恢复原本的结束标志
            DELIMITER ; 
            
            -- 调用存储过程
            CALL P11(60);
            -- 查看是新表否达到要求
            SELECT * FROM tb_user_pro;
     *

--------------------

### 9. 存储函数 ###

`存储函数`：

*  存储函数是有返回值的存储过程，存储函数的参数只能是IN类型。

*  ?**存储函数的使用：**
 *   *      -- 语法:
            DELIMITER $$
            
            CREATE FUNCTION 存储函数名称([参数列表])
            RETURNS type [characteristic...]
            BEGIN
            	-- SQL语句
            	RETURN...;
            END $$
            
            DELIMITER ;
            
            #######################################
            
            -- characteristic的解释：
            DETERMINISTIC  #相同的输入参数总是产生相同的结果
            NO SQL         #不包含SQL语句
            READS SQL DATA #包含读取数据的语句，但不包含写入数据的语句。
     *

*  `演示`：
 *   *  > 使用存储函数，实现从1到n的累加
 *   *      -- 使用存储函数，实现从1到n的累加
            DELIMITER $$
            
            CREATE FUNCTION f(n INT)
            RETURNS INT DETERMINISTIC
            BEGIN
            	-- SQL语句
            	DECLARE total INT DEFAULT 0;
            	
            	WHILE n > 0 DO
            		SET total = total + n;
            		SET n = n - 1;
            	END WHILE;
            	
            	-- 返回
            	RETURN total;
            END $$
            
            DELIMITER ;
            
            -- 验证结果
            SELECT f(100);
     *

--------------------

[![在这里插入图片描述][20a7f1b58dfb4660b75d7f021c157d57.png_pic_center]][20a7f1b58dfb4660b75d7f021c157d57.png_pic_center 1]

[8d8e0d8a542946af858489a536162608.gif_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/14/71ff615106ee41439591b568a1d0766d.gif
[.29.]: https://blog.csdn.net/ebb29bbe?spm=1018.2226.3001.5343
[Link 1]: https://bbs.csdn.net/forums/185241f773fb401b8ca7994dc3d02333?joinKey=cyru4ex9o7wq-mz6v1koj5x-1-add3ae8dc3827dbbd4360c79af92bbbe
[ff95d349de5a4059b4211448c7f4551d.png_pic_center]: https://img-blog.csdnimg.cn/ff95d349de5a4059b4211448c7f4551d.png#pic_center
[ff95d349de5a4059b4211448c7f4551d.png_pic_center 1]: https://blog.csdn.net/ebb29bbe/category_12107001.html?spm=1001.2014.3001.5482
[b3dfd145c8a941239e5f518da28d2175.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/14/16bcde56b10947e5854aa0aaf90693b0.png
[20a7f1b58dfb4660b75d7f021c157d57.png_pic_center]: https://img-blog.csdnimg.cn/20a7f1b58dfb4660b75d7f021c157d57.png#pic_center
[20a7f1b58dfb4660b75d7f021c157d57.png_pic_center 1]: https://blog.csdn.net/ebb29bbe?type=blog