03-变量&流程控制

1. 变量

1.1 系统变量

变量由系统提供，不是用户定义，属于服务器层面。

• 分类
  • 全局变量：服务器每次启动将为所有的全局变量赋初始值，针对于本次连接的所有会话有效；
  • 会话变量
• 查看变量（默认 SESSION）
  • 查看所有的系统变量：SHOW [GLOBAL|SESSION] VARIABLES
  • 查看满足条件的部分系统变量：SHOW [GLOBAL|SESSION] VARIABLES LIKE '%条件%'
  • 查看指定的某个系统变量的值：SELECT @@[GLOBAL|SESSION].系统变量名;
• 为某个系统变量赋值（默认 SESSION）
  • SET [GLOBAL|SESSION] 系统变量名 = 值;
  • SET @@[GLOBAL|SESSION].系统变量名 = 值;

1.2 自定义变量

变量是用户自定义的，不是系统定义。

使用步骤：声明 → 赋值 → 使用(查看、比较、运算等)

a. 用户变量

• 作用域：针对于当前会话(连接) 有效，同于会话变量的作用域
• 声明并初始化

  SET @用户变量名=值;
  SET @用户变量名:=值;
  SELECT @用户变量名:=值;
  

• 赋值(更新用户变量的值)
  • 同声明初始化方式相同 ↑
  • 使用 SELECT INTO

    SELECT 字段 INTO 变量名
    FROM 表;
    -------------------------
    SELECT COUNT(*) INTO @empCount
    FROM employees;
    

• e.g. 声明 2 个变量并赋初始值，求和并打印

  SET @m=1;
  SET @n=2;
  SET @sum = @m + @n;
  SELECT @sum;
  

b. 局部变量

• 作用域：仅仅在定义它的 BEGIN..END 中有效（还必须是其中的第 1 句话）
• 声明

  DECLARE 变量名 类型;
  DECLARE 变量名 类型 DEFAULT 值;
  

• 赋值

  SET 局部变量名=值;
  SET 局部变量名:=值;
  SELECT @局部变量名:=值;
  

• 使用

  SELECT 局部变量名;
  

c. 小结

\	作用域	定义和使用的位置	语法
用户变量	当前会话	会话的任何地方	必须加 @ 符号，不用限定类型
局部变量	在 BEGIN...END 中	只能在 BEGIN...END 中，声明必须在第 1 行	一般不用加 @ 符号，需要限定类型

2. 流程控制

2.1 分支结构

a. IF

• IF 函数：IF(表达式1, 表达式2, 表达式3)
• IF 结构 // 只能应用在 BEGIN...END 中

  IF 条件1 THEN 语句1;
  ELSEIF 条件2 THEN 语句2;
  ...
  [else 语句n;]
  END IF;
  

• e.g. 创建函数实现根据传入的成绩返回对应的等级

  CREATE FUNCTION getScoreLevel(score INT) RETURNS CHAR
  BEGIN
      IF score >= 90 AND score <= 100 THEN RETURN 'A';
      ELSEIF score >= 80 THEN RETURN 'B';
      ELSEIF score >= 60 THEN RETURN 'C';
      ELSE RETURN 'D';
      END IF;
  END $
  

b. CASE

• 语法
  • 类似于 Java 中的 switch 语句，一般用于实现等值判断

    CASE 变量|表达式|字段
        WHEN 判断的值 THEN 返回值1/语句1
        WHEN 判断的值 THEN 返回值2/语句2
        ...
        ELSE 返回值n/语句n
    END
    

  • 类似于 Java 中的多重 if 语句，一般用于实现区间判断

    CASE
        WHEN 判断条件1 THEN 返回值1/语句1
        WHEN 判断条件2 THEN 返回值2/语句2
        ...
        ELSE 返回值n/语句n
    END
    

• 特点
  • 作为表达式，嵌套在其它语句中使用，可以放在任何地方；若作为独立的语句去使用，只能放在 BEGIN...END 中；
  • 如果 WHEN 中的值满足或条件成立，则执行对应的 THEN 后面的语句，并且结束 CASE；如果都不满足，则执行 ELSE 中的语句或值；
  • ELSE 可以省略，如果 ELSE 省略且所有 WHEN 条件都不满足，则返回 NULL；
• e.g. 创建过程实现根据传入的成绩显示对应的等级

  CREATE PROCEDURE getLevel(IN score INT)
  BEGIN
      CASE
      WHEN score >= 90 AND score <= 100 THEN SELECT 'A';
      WHEN score >= 80 THEN SELECT 'B';
      WHEN score >= 60 THEN SELECT 'C';
      ELSE SELECT 'D';
      END CASE;
  END $
  

2.2 循环结构

• 分类（仅能放在 BEGIN...END 中）：while、loop、repeat
• 循环控制
  • iterate 类似于 continue 继续，即结束本次循环，继续下一次
  • leave 类似于 break 跳出，即结束当前所在的循环

a. 语法

1. while // 先判断后执行

   [标签:] WHILE 循环条件 do
       循环体;
   END WHILE [标签];
   

2. loop // 可实现没有条件的死循环

   [标签:] loop
       循环体;
   END LOOP [标签];
   

3. repeat // 先执行后判断

   [标签:] repeat
       循环体;
   UNTIL 结束循环的条件
   END REPEAT [标签];
   

b. 演示

1. 批量插入，根据形参值插入到 admin 表指定多条记录；

   CREATE PROCEDURE whileTest1(IN count INT)
   BEGIN
       DECLARE i INT DEFAULT 1;
       WHILE i < count DO
           INSERT INTO admin VALUES(null, CONCAT('rose', i), '666');
           SET i = i+1;
       END WHILE;
   END $
   

2. [使用标签和 leave] 批量插入，根据形参值插入到 admin 表指定多条记录，如果次数大于 20 则停止；

   CREATE PROCEDURE whileTest2(IN count INT)
   BEGIN
       DECLARE i INT DEFAULT 1;
       labelW:WHILE i < count DO
           IF i > 20 THEN LEAVE labelW;
           END IF;
           INSERT INTO admin VALUES(null, CONCAT('jack', i), '888');
           SET i = i+1;
       END WHILE labelW;
   END $
   

3. [使用 iterate] 批量插入，根据形参值插入到 admin 表指定多条记录，只插入偶数次；

   CREATE PROCEDURE whileTest3(IN count INT)
       BEGIN
       DECLARE i INT DEFAULT 0;
       labelW:WHILE i < count DO
           SET i = i+1;
           IF MOD(i, 2) != 0 THEN ITERATE labelW;
           END IF;
           INSERT INTO admin VALUES(null, CONCAT('hana', i), '888');
       END WHILE labelW;
   END $
   

4. 向 stringcontent 表中插入指定个数的随机字符串；

   CREATE TABLE stringcontent (
       id INT PRIMARY KEY AUTO_INCREMENT,
       content VARCHAR(30)
   )$
   CREATE PROCEDURE randStrInsert(IN insertCount INT)
   BEGIN
       DECLARE i INT DEFAULT 1; # 定义一个循环变量i，表示插入次数
       DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
       DECLARE startIndex INT DEFAULT 1;
       DECLARE len INT DEFAULT 1;
       WHILE i <= insertCount DO
           SET startIndex = FLOOR(RAND()*26+1); # 产生一个随机数[1~26]
           SET len = FLOOR(RAND()*(26-startIndex+1)+1); # 截取的字符串长度
           INSERT INTO stringcontent VALUES(null, SUBSTR(str, startIndex, len));
           SET i = i+1;
       END WHILE;
   END $