存储过程应用，MySQL中各种语句的使用（if、case，while、repeat、loop）

悄悄绽放的海棠花

一枚程序员小白

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

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

1、基本语法

 CREATE PROCEDURE 存储过程名称([参数列表])
 BEGIIN
     --SQL语句
 END ;
 -- 例1、创建一个存储过程p1()：记录tb_user表有多少条数据
 create procedure p1()
 BEGIN
  SELECT count(*) FROM tb_user;
 END;
 -- 调用存储过程 使用 call
 mysql> call p1();
 +----------+
 | count(*) |
 +----------+
 |       12 |
 +----------+
 1 row in set (0.00 sec)
 Query OK, 0 rows affected (0.00 sec)
 -- 查看存储过程
 SHOW create PROCEDURE p1;
 -- 删除存储过程
 drop PROCEDURE if EXISTS p1;
 -- 注意:在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符。
 -- 利用delimiter 设置结束符
 delimiter $$
 create procedure p1()
 BEGIN
  SELECT count(*) FROM tb_user;
 END $$
 delimiter ;

2、变量

MySQL中变量有 系统变量 、 用户自定义变量 和 局部变量

系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL)、会话变量(SESSION)。
用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。用户自定义变量其作用域为当前会话连接。

 -- 变量
 -- 查看系统变量，默认session
 SHOW VARIABLES;
 -- 查看以auto开头的会话变量
 SHOW SESSION VARIABLES LIKE 'auto%';
 mysql> SHOW SESSION VARIABLES LIKE 'auto%';
 +--------------------------+-------+
 | Variable_name            | Value |
 +--------------------------+-------+
 | auto_generate_certs      | ON    |
 | auto_increment_increment | 1     |
 | auto_increment_offset    | 1     |
 | autocommit               | ON    |
 | automatic_sp_privileges  | ON    |
 +--------------------------+-------+
 5 rows in set, 1 warning (0.00 sec)
 -- 查看以auto揩油的全局变量
 SHOW GLOBAL VARIABLES  LIKE 'auto%';
 mysql> SHOW GLOBAL VARIABLES  LIKE 'auto%';
 +--------------------------+-------+
 | Variable_name            | Value |
 +--------------------------+-------+
 | auto_generate_certs      | ON    |
 | auto_increment_increment | 1     |
 | auto_increment_offset    | 1     |
 | autocommit               | ON    |
 | automatic_sp_privileges  | ON    |
 +--------------------------+-------+
 5 rows in set, 1 warning (0.00 sec)
 -- 查看系统变量是否开启，结果为 1 表示启动，结果为 0 表示未启动
 select @@session.autocommit;
 mysql> select @@session.autocommit;
 +----------------------+
 | @@session.autocommit |
 +----------------------+
 |                    1 |
 +----------------------+
 1 row in set (0.00 sec)
 -- 设置系统变量，关闭事务自动提交功能
 -- 设置系统变量方法1 使用 @@
 set @@autocommit = 0;
 -- 开启使用自动提交
 -- 设置系统变量方法2
 set SESSION autocommit = 1;
 -------------------------------------------------------------------------------------------
 -------------------------------------------------------------------------------------------
 -- 用户自定义变量
 -- 设置用户自定义变量
 -- 语法1，使用 = 
 set @myname = 'zjj';
 set @myage = 20;
 -- 语法2，使用 :=
 set @mygender := '男',@myhobby := 'java';
 mysql> set @myname = 'zjj';set @myage = 20;set @mygender := '男',@myhobby := 'java';
 Query OK, 0 rows affected (0.00 sec)
 Query OK, 0 rows affected (0.00 sec)
 Query OK, 0 rows affected (0.00 sec)
 mysql> SELECT @myname,@myage;
 +---------+--------+
 | @myname | @myage |
 +---------+--------+
 | zjj     |     20 |
 +---------+--------+
 1 row in set (0.00 sec)
 ---------------------------------------------------------------------------------
 ---------------------------------------------------------------------------------
 -- 局部变量
 --局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN ... END块。
 -- 声明局部变量 使用 DECLARE
 delimiter $$
 create PROCEDURE p2()
 BEGIN
  DECLARE stu_count int DEFAULT 0;
  select count(*) into stu_count from tb_user;
  SELECT stu_count;
 END $$
 delimiter ;
  -- 调用
  mysql> call p2();
 +-----------+
 | stu_count |
 +-----------+
 |        12 |
 +-----------+
 1 row in set (0.00 sec)
 Query OK, 0 rows affected (0.00 sec)

注意：在会话窗口修改了系统全局变量，MySQL服务重启之后会恢复成默认值。若要其永久有效需要修改MySQL的配置文件

3、if条件的使用

 -- if语句语法
 IF 条件1 THEN
 ELSEIF  条件2 THEN   --可选
     ......
 ELSE   --可选
     ......
 END IF;
 -- 例如
 -- 根据定义的分数score变量，判定当前分数对应的分数等级。
 -- 1. score >=85分，等级为优秀。
 -- 2.score >=60分且score <85分，等级为及格。
 -- 3.score <60分，等级为不及格。
 delimiter $$
 create  PROCEDURE p3()
 BEGIN
  DECLARE score int DEFAULT 58;
  DECLARE result VARCHAR(10);
  if score >= 85 THEN
     SET result := '优秀';
     ELSEIF score >= 60 THEN
       SET result := '及格';
       SET result := '不及格';
  end IF;
  SELECT result;
 END $$
 delimiter ;
 -- 调用
 mysql> call p3();
 +-----------+
 | result    |
 +-----------+
 | 不及格    |
 +-----------+
 1 row in set (0.00 sec)
 Query OK, 0 rows affected (0.00 sec)

4、存储过程中参数的使用

类型	含义	备注
IN	该类参数作为输入，也就是需要调用时传入值	默认
OUT	该类参数作为输出，也就是该参数可以作为返回值
INOUT	既可以作为输入参数，也可以作为输出参数

 -- 举例
 -- 根握传入(in)参数score，判定当前分数对应的分致等级，并返回(out).
 -- score >= 85分，等级为优秀。
 -- score >= 60分 score < 85分，等级为及格。
 -- score < 60分，等级为不及格。
 -- 语句
 delimiter $$
 create  PROCEDURE p4(in score int,out result VARCHAR(20))
 BEGIN
  if score >= 85 THEN
     SET result := '优秀';
     ELSEIF score >= 60 THEN
       SET result := '及格';
       SET result := '不及格';
  end IF;
 END $$
 delimiter ;
 -- 调用，下面两条语句要在同一个会话窗口才有效，因为 result 是用户自定义变量
 call p4(85,@result);
 select @result;
 mysql> select @result;
 +---------+
 | @result |
 +---------+
 | 优秀    |
 +---------+
 1 row in set (0.00 sec)
 ----------------------------------------------------------------------------------------
 ----------------------------------------------------------------------------------------
 -- inout的使用
 -- 将传入的200分制换算成百分比，然后返回
 delimiter $$
 create PROCEDURE p5(INOUT score DOUBLE)
 BEGIN
   SET score := score * 0.5;
 END $$
 delimiter ;
 -- 调用
 SET @score = 150;
 call p5(@score);
 select @score;
 -- 结果
 mysql> SET @score = 150;call p5(@score);select @score;
 Query OK, 0 rows affected (0.00 sec)
 Query OK, 0 rows affected (0.00 sec)
 +--------+
 | @score |
 +--------+
 |     75 |
 +--------+
 1 row in set (0.00 sec)
 mysql>

5、case语句

 -- 需求
 --根据传入的月份，判定月份所属的季节（要求采用case结构公。
 --1. 1-3月份，为第一季度
 --2. 4-6月份，为第二季度
 --3. 7-9月份，为第三季度
 --4. 10-12月份，为第四季度
 -- 使用case实现如下：
 delimiter $$
 create PROCEDURE p6(in month int)
 BEGIN
   DECLARE result VARCHAR(10);
         WHEN month >= 1 and month <= 3 THEN
               SET result := '第一季度';
             WHEN month >= 4 and month <= 6 THEN
               SET result := '第二季度';
             WHEN month >= 7 and month <= 9 THEN
               SET result := '第三季度';
             WHEN month >= 10 and month <= 12 THEN
               SET result := '第四季度';
               SET result := '参数错误';
         end case;
         SELECT CONCAT('您输入的月份为：',month,'季度为：',result);
 END $$
 delimiter ;
 -- 调用，传入合法参数 5
 mysql> CALL p6(5);
 +----------------------------------------------------------------+
 | CONCAT('您输入的月份为：',month,'季度为：',result)             |
 +----------------------------------------------------------------+
 | 您输入的月份为：5季度为：第二季度                              |
 +----------------------------------------------------------------+
 1 row in set (0.00 sec)
 Query OK, 0 rows affected (0.00 sec)
 -- 调用，传入不合法参数 15
 mysql> CALL p6(15);
 +----------------------------------------------------------------+
 | CONCAT('您输入的月份为：',month,'季度为：',result)             |
 +----------------------------------------------------------------+
 | 您输入的月份为：15季度为：参数错误                             |
 +----------------------------------------------------------------+
 1 row in set (0.00 sec)
 Query OK, 0 rows affected (0.00 sec)

6、while语句

 
 -- while 循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为:
 -- 先判定条件，如果条件为true，则执行逻辑，否则,不执行逻辑
 WHILE 条件 DO
         SQL逻辑...
 END WHILE;
 -- 举例，计算从1累加到n的值
 delimiter $$
 create PROCEDURE p7(in n int)
 BEGIN
   DECLARE total int DEFAULT 0;
     WHILE n>0 do
         set total := total + n;
         set n := n - 1;
     end WHILE;
     SELECT total;
 END $$
 delimiter ;
 -- 调用，计算 1 - 100的和
 mysql> call p7(100);
 +-------+
 | total |
 +-------+
 |  5050 |
 +-------+
 1 row in set (0.00 sec)
 Query OK, 0 rows affected (0.00 sec)

7、repeat语句

 -- repeat是有条件的循环控制语句,当满足条件的时候退出循环。具体语法为:
 -- #先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环
 REPEAT
    5QL逻辑...  UNTIL条件
 END REPEAT;
 -- 例如，使用repeat语句实现 1-n的和
 delimiter $$
 create PROCEDURE p8(in n int)
 BEGIN
   DECLARE total int DEFAULT 0;
     REPEAT
       SET total := total + n;
         set n := n -1;
     UNTIL n <= 0
     END REPEAT;
     SELECT total;
 END $$
 delimiter ;
 -- 调用，计算1-20的值
 mysql> call p8(20);
 +-------+
 | total |
 +-------+
 |   210 |
 +-------+
 1 row in set (0.00 sec)
 Query OK, 0 rows affected (0.01 sec)

8、loop语句

LOOP实现简单的循环，如果不在sql逻辑中增加退出循环的条件，可以用其来实现简单的死循环。loop可以配合一下两个语句使用:.

LEAVE :配合循环使用，退出循环。

ITERATE:必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

具体语法如下：

 -- 1.计算从1累加到n的值，n为传入的参数值。
 -- 从1到n的累加
 delimiter $$
 create PROCEDURE p9(in n int)
 BEGIN
  DECLARE total int DEFAULT 0;
  sum:loop
      if n <= 0 THEN
              LEAVE sum;
          END if;
          SET total := total + n;
          SET n := n -1;
  end LOOP sum;
  select total;
 END $$
 delimiter $$
 -- 调用
 mysql> call p9(10);
 +-------+
 | total |
 +-------+
 |    55 |
 +-------+
 1 row in set (0.00 sec)
 Query OK, 0 rows affected (0.01 sec)
 -- 2.计算从1到n之间的偶数累加的值，n为传入的参数值。
 delimiter $$
 create PROCEDURE p10(in n int)
 BEGIN
  DECLARE total int DEFAULT 0;
  sum:loop
      if n <= 0 THEN
              LEAVE sum;
          END if;
          if n%2 = 1 THEN
                  SET n := n - 1;
                  ITERATE sum;
          END IF;
          SET total := total + n;
          SET n := n -1;
  end LOOP sum;
  select total;
 END $$
 delimiter $$
 -- 调用，计算1-100中间所有偶数的值
 mysql> call p10(100);
 +-------+
 | total |