17. 流程控制和游标

流程控制
游标

流程控制

流程控制分为三大类：

顺序结构：程序从上往下依次执行
分支结构：程序按条件进行选择执行，从两条或多条路径中选择一条执行
循环结构：程序满足一定条件下，重复执行一组语句

针对于MySQL的流程控制语句为：

条件判断语句：IF语句和CASE语句
循环语句：LOOP、WHILE和REPEAT语句
跳转语句：ITERATE和LEAVE语句

注意：只能用于存储程序

分支结构之IF

IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]
...
[ELSE 操作N]
END IF

特点：

不同的表达式对应不同的操作
使用在BEGIN…END中 ```sql //声明存储过程”update_salary_by_eid3”, 定义IN参数emp_id, 输入员工编号. 判断该员工薪资如果低于9000元, 就更新薪资为9000元; 薪资如果大于等于9000元且低于10000的, 但是奖金比例为NULL的, 就更新奖金比例为0.01; 其他的涨薪100元

DELIMITER // CREATE PROCEDURE update_salary_by_eid3(IN emp_id INT) BEGIN DECLARE emp_salary DOUBLE; DECLARE bonus DECIMAL(3, 2); SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id; SELECT commission_pct INTO bonus FROM employees WHERE employee_id = emp_id; IF emp_salary < 9000 THEN UPDATE employees SET salary = 9000 WHERE employee_id = emp_id; ELSEIF emp_salary < 10000 AND bonus IS NULL THEN UPDATE employees SET commission_pct = 0.01 WHERE employee_id = emp_id; ELSE UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id; END IF; END // DELIMITER ;

<a name="ESIX2"></a>
#### 分支结构之CASE
```sql
//情况一：类似于switch
CASE 表达式
WHEN 值1 THEN 结果1或语句1(如果是语句, 需要加分号)
WHEN 值2 THEN 结果2或语句2(如果是语句, 需要加分号)
...
ELSE 结果n或语句n(如果是语句, 需要加分号)
END [case] (如果是放在begin end中需要加上case, 如果放在select后面不需要)

//情况二：类似于多重if
CASE
WHEN 条件1 THEN 结果1或语句1(如果是语句, 需要加分号)
WHEN 条件2 THEN 结果2或语句2(如果是语句, 需要加分号)
...
ELSE 结果n或语句n(如果是语句, 需要加分号)
END [case] (如果是放在begin end中需要加上case, 如果放在select后面不需要)

//声明存储过程"update_salary_by_eid4", 定义IN参数emp_id, 输入员工编号. 判断该员工
薪资如果低于9000元, 就更新薪资为9000元; 薪资大于等于9000元且低于10000的, 但是奖金比例
为NULL的, 就更新奖金比例为0.01; 其他的涨薪100元

DELIMITER //

CREATE PROCEDURE update_salary_by_eid4(IN emp_id INT)
BEGIN
  DECLARE emp_sal DOUBLE;
  DECLARE bonus DECIMAL(3, 2);
  SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
  SELECT commission_pct INTO bonus FROM employees WHERE employee_id = emp_id;
  CASE
    WHEN emp_sal < 9000
      THEN UPDATE employees SET salary = 9000 WHERE employee_id = emp_id;
    WHEN emp_sal < 10000 AND bonus IS NULL
      THEN UPDATE employees SET commission_pct = 0.01 WHERE employee_id = emp_id;
    ELSE
      UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
  END CASE;
END //

DELIMITER ;

循环结构之LOOP

LOOP循环语句用来重复执行某些语句，LOOP内的语句一直重复执行直到循环被退出（使用LEAVE子句），跳出循环过程

[loop_label:] LOOP
  循环执行的语句
END LOOP [loop_label]

其中，loop_label表示LOOP语句的标注名称，该参数可以省略

//当市场环境变好时, 公司为了奖励大家, 决定给大家涨工资. 
声明存储过程"update_salary_loop()", 声明OUT参数num, 输出循环次数. 
存储过程中实现循环给大家涨薪, 薪资涨为原来的1.1倍, 直到全公司的平均薪资达到12000结束. 并统计循环次数

DELIMITER //

CREATE PROCEDURE update_salary_loop(OUT num INT)
BEGIN
  DECLARE avg_salary DOUBLE;
  DECLARE loop_count INT DEFAULT 0;
  SELECT AVG(salary) INTO avg_salary FROM employees;

  label_loop: LOOP
    //循环条件
    IF avg_salary >= 12000 THEN LEAVE label_loop;
    END IF;
    //循环体
    UPDATE employees SET salary = salary * 1.1;
    SET loop_count = loop_count + 1;
    //迭代条件
    SELECT AVG(salary) INTO avg_salary FROM employees;
  END LOOP label_loop;
  SET num = loop_count;
END //

DELIMITER ;

循环结构之WHILE

WHILE语句创建一个带条件判断的循环过程。WHILE在执行语句时，先对指定的表达式进行判断，如果为真，就执行循环内的语句，否则退出循环。WHILE语句的基本格式如下：

[while_label:] WHILE 循环条件 DO
  循环体
END WHILE [while_label];

while_label为WHILE语句的标注名称。如果循环条件结果为真，WHILE语句内的语句或语句群将被执行，直至循环条件为假，退出循环

//市场环境不好时, 公司为了渡过难关, 决定暂时降低大家的薪资. 
声明存储过程"update_salary_while()", 声明OUT参数num, 输出循环次数.
存储过程中实现循环给大家降薪，薪资降为原来的90%, 直到全公司的平均薪资达到5000结束. 并统计循环次数

DELIMITER //

CREATE PROCEDURE update_salary_while(OUT num INT)
BEGIN
  DECLARE avg_sal DOUBLE;
  DECLARE while_count INT DEFAULT 0;
  SELECT AVG(salary) INTO avg_sal FROM employees;
  WHILE avg_sal > 5000 DO
    UPDATE employees SET salary = salary * 0.9;
    SET while_count = while_count + 1;
    SELECT AVG(salary) INTO avg_sal FROM employees;
  END WHILE;
  SET num = while_count;
END //

DELIMITER ;

//调用
CALL update_salary_while(@num);
SELECT @num;

循环结构之REPEAT

REPEAT语句创建一个带条件判断的循环过程。与WHILE循环不同的是，REPEAT循环首先会执行一次循环，然后在UNTIL中进行表达式的判断，如果条件满足就退出，即END REPEAT；如果条件不满足，则会继续执行循环，直到满足退出条件为止

[repeat_label:] REPEAT
循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]

说明：

repeat_label为REPEAT语句的标注名称，该参数可以省略
REPEAT语句内的语句或语句群被重复，直至expr_condition为真 ```sql //当市场环境变好时, 公司为了奖励大家, 决定给大家涨工资. 声明存储过程”update_salary_repeat()”, 声明OUT参数num, 输出循环次数. 存储过程中实现循环给大家涨薪, 薪资涨为原来的1.15倍, 直到全公司的平均薪资达到13000结束. 并统计循环次数

DELIMITER //

CREATE PROCEDURE update_salary_repeat(OUT num INT) BEGIN DECLARE avg_sal DOUBLE; DECLARE repeat_count INT DEFAULT 0; SELECT AVG(salary) INTO avg_sal FROM employees; REPEAT UPDATE employees SET salary = salary * 1.15; SET repeat_count = repeat_count + 1; SELECT AVG(salary) INTO avg_sal FROM employees; UNTIL avg_sal >= 13000 //别加分号 END REPEAT; SET num = repeat_count; END //

DELIMITER ;

<a name="jpP5J"></a>
#### 跳转语句之LEAVE语句
LEAVE语句：可以用在循环语句内，或者以BEGIN和END包裹起来的程序体内，表示跳出循环或者跳出循环体的操作。可以理解为break<br />语法格式：`LEAVE 标记名`<br />其中，label参数表示循环的标志，LEAVE和BEGIN...END或循环一起使用
```sql
//当市场环境不好时, 公司为了渡过难关, 决定暂时降低大家的薪资.
声明存储过程"leave_while()", 声明OUT参数num, 输出循环次数,
存储过程中使用WHILE循环给大家降低薪资为原来薪资的90%, 直到全公司的平均薪资小于等于10000, 并统计循环次数

DELIMITER //

CREATE PROCEDURE leave_while(OUT num INT)
BEGIN
  DECLARE avg_sal DOUBLE;
  DECLARE while_count INT DEFAULT 0;

  SELECT AVG(salary) INTO avg_sal FROM employees;
  while_label:WHILE TRUE DO
    IF avg_sal <= 10000 THEN
      LEAVE while_label;
    END IF;
    UPDATE employees SET salary = salary * 0.9;
    SET while_count = while_count + 1;
    SELECT AVG(salary) INTO avg_sal FROM employees;
  END WHILE;
  SET num = while_count;
END //

DELIMITER ;

跳转语句之ITERATE语句

ITERATE语句：只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序转到语句段开头处。可以理解为continue
语法格式：ITERATE label
label参数表示循环的标志，ITERATE语句必须跟在循环标志前面

DELIMITER //

CREATE PROCEDURE test_iterate()
BEGIN
  DECLARE num INT DEFAULT 0;
  my_loop:LOOP
    SET num = num + 1;
    IF num < 10
      THEN ITERATE my_loop;
    ELSEIF num > 15
      THEN LEAVE my_loop;
    END IF;
    SELECT 'daydayup';
  END LOOP my_loop;
END //

DELIMITER ;

对比三种循环结构

这三种循环都可以省略名称，但是如果循环中添加了循环控制语句（LEAVE或ITERATE），则必须添加名称
LOOP：一般用于实现简单的“死”循环
WHILE：先判断后执行
REPEAT：先执行后判断，无条件至少执行一次

游标

什么是游标
游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作。游标让SQL这种面向集合的语言有了面向过程开发的能力
在SQL中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针，我们可以通过操作游标来对数据行进行操作
在MySQL中，游标可以在存储过程和函数中使用
使用游标步骤
游标必须在声明处理程序之前被声明，并且变量和条件还必须在声明游标或处理程序之前被声明

第一步：声明游标

DECLARE cursor_name CURSOR FOR select_statement;

说明：其中的select_statement代表的是SELECT语句，返回一个用于创建游标的结果集。要使用SELECT语句来获取数据结果集，而此时还没有开始遍历数据

第二步：打开游标

OPEN cursor_name;

说明：当我们定义好游标之后，如果想要使用游标，必须先打开游标。打开游标的时候SELECT语句的查询结果集就会送到游标工作区，为后面游标的逐条读取结果集中的记录做准备

第三步：使用游标

FETCH cursor_name INTO var_name [, var_name]...;

说明：这句的作用是使用cursor_name这个游标来读取当前行，并且将数据保存到var_name这个变量中，游标指针指到下一行。如果游标读取的数据行有多个列名，则在INTO关键字后面赋值给多个变量名即可
注意：

var_name必须在声明游标之前就定义好
游标的查询结果集中的字段数，必须和INTO后面的变量数一致

第四步：关闭游标

CLOSE cursor_name;

说明：

当我们使用完游标后需要关闭掉该游标，因为游标会占用系统资源，如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率。而关闭游标的操作，会释放游标占用的系统资源
关闭游标之后，我们就不能再检索查询结果中的数据行，如果需要检索只能再次打开游标
举例
```sql //创建存储过程”get_count_by_limit_total_salary()”, 声明IN参数limit_total_salary, DOUBLE类型; 声明OUT参数total_count, INT类型. 函数的功能可以实现累加薪资最高的几个员工的薪资值, 直到薪资总和达到limit_total_salary参数的值, 返回累加的人数给total_count

DELIMITER //

CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE, OUT total_count INT) BEGIN DECLARE sum_salary DOUBLE DEFAULT 0; //记录累加的总工资 DECLARE cursor_salary DOUBLE DEFAULT 0; //记录某一个工资值 DECLARE emp_count INT DEFAULT 0; //记录循环个数 //定义游标 DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC; //打开游标 OPEN emp_cursor; REPEAT //使用游标(从游标中获取数据) FETCH emp_cursor INTO cursor_salary; SET sum_salary = sum_salary + cursor_salary; SET emp_count = emp_count + 1; UNTIL sum_salary >= limit_total_salary END REPEAT; SET total_count = emp_count; //关闭游标 CLOSE emp_cursor; END //

DELIMITER ;

<a name="jVM0x"></a>
#### 小结

- 游标为逐条读取结果集中的数据，提供了完美的解决方案。跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁
- 但是在使用游标的过程中，会对数据进行加锁，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会消耗系统资源，造成内存不足
- 建议：养成用完之后就关闭的习惯，这样才能提高系统的整体效率
<a name="qyxgA"></a>
### 补充：MySQL 8.0的新特性—全局变量的持久化
在MySQL数据库中，全局变量可以通过SET GLOBAL语句来设置。例如，设置服务器语句超时的限制，可以通过设置系统变量max_execution_time来实现：
```sql
SET GLOBAL MAX_EXECUTION_TIME = 2000;

使用SET GLOBAL语句设置的变量值只会临时生效，数据库重启后，服务器又会从MySQL配置文件中读取变量的默认值。MySQL8.0新增了SET PERSIST命令，例如，设置服务器的最大连接数为1000：

SET PERSIST global MAX_CONNECTIONS = 1000;

MySQL会将该命令的配置保存到数据目录下的mysqld-auto.cnf文件中，下次启动时会读取该文件，用其中的配置来覆盖默认的配置文件