第16章_变量、流程控制与游标

阿Y加油吧

于 2024-06-25 15:32:51 发布

阅读量1k

点赞数 14

文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/2401_84526799/article/details/139959427

版权

1. 变量

在MySQL数据库的存储过程和函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终的结果数据。

在 MySQL 数据库中，变量分为系统变量以及用户自定义变量。

1.1 系统变量

1.1.1 系统变量分类

系统变量分为全局系统变量（需要添加 global 关键字）以及会话系统变量（需要添加 session 关键字），有时也把全局系统变量简称为全局变量，有时也把会话系统变量称为local变量。如果不写，默认会话级别。静态变量（在 MySQL 服务实例运行期间它们的值不能使用 set 动态修改）属于特殊的全局系统变量。

全局系统变量针对于所有会话（连接）有效，但不能跨重启
会话系统变量仅针对于当前会话（连接）有效。会话期间，当前会话对某个会话系统变量值的修改，不会影响其他会话同一个会话系统变量的值。
会话1对某个全局系统变量值的修改会导致会话2中同一个全局系统变量值的修改

1.1.2 查看系统变量

#1.2 查看系统变量
#查询全局系统变量
SHOW GLOBAL VARIABLES;
#查询会话系统变量
SHOW SESSION VARIABLES;

SHOW VARIABLES ;#默认查询的是会话变量

#查询部分系统变量
SHOW GLOBAL VARIABLES LIKE 'admin_%';

SHOW VARIABLES LIKE 'character_%';

查看指定系统变量

#1.3查看指定的系统变量
SELECT @@global.max_connections;
SELECT  @@global.character_set_client;

#错误
SELECT @@global.pseudo_thread_id;

#错误
SELECT @@session.max_connections;#他是全局变量，不是会话变量 

SELECT  @@session.character_set_client;

SELECT @@session.pseudo_thread_id;

#“@@”首先标记会话系统变量，如果会话系统变量不存在，则标记全局系统变量。
SELECT  @@character_set_client;

修改系统变量的值

#1.4修改系统变量的值
#全局系统变量：
#方式1：
SET @@global.max_connections=161;
#方式2：
SET GLOBAL max_connections=171;
#针对于当前的数据库实例是有效的，一旦重启mysql服务，就失效了


#会话系统变量:
#方式1：
SET @@session.character_set_client='gbk';

#方式2：
SET SESSION character_set_client='utf8';
#针对当前会话是有效的，一旦结束会话，重新建立起新的会话，就会变回默认值了

1.2 用户变量

1.2.1 用户变量分类

会话用户变量：
作用域和会话变量一样，只对当前连接会话有效。

局部变量：
只在 BEGIN 和 END 语句块中有效。局部变量只能在存储过程和函数中使用。

1.2.2 会话用户变量

#1.6 会话用户变量
CREATE   DATABASE dbtest16;

USE  dbtest16;

CREATE TABLE employees
AS
SELECT *FROM atguigudb.`employees`;

CREATE TABLE departments
AS
SELECT *FROM atguigudb.`departments`;

SELECT *FROM employees;
SELECT *FROM departments;
/*
#变量的声明和赋值
#方式1：“=”或“:=”
 SET @用户变量 = 值;
 SET @用户变量 := 值;
 
 #方式2：“:=” 或 INTO关键字
 SELECT @用户变量 := 表达式 [FROM 等子句];
 SELECT 表达式 INTO @用户变量  [FROM 等子句];
 
 #使用
 select @变量名

*/

#测试：
#方式1：
SET @m1=1;
SET @m2:=2;
SET @sum :=@m1+@m2;
SELECT @sum;

#方式2：
SELECT @count := COUNT(*) FROM employees;
          
SELECT @count;

SELECT AVG(salary) INTO @avg_sal FROM employees;

SELECT @avg_sal;

1.2.3 局部变量

#1.7局部变量
/*
局部变量：1。使用DECLARE声明 
          2.声明并使用在BEGIN.....END中（存储过程或函数中）
          3.使用DECLARE声明，必须声明在BEGIN的首行

2.声明格式
DECLARE 变量名 类型 [default 值];  # 如果没有DEFAULT子句，初始值为NULL

3.赋值
方式1：
 SET 变量名=值;
 SET 变量名:=值;
 
 方式2：
 SELECT 字段名或表达式 INTO 变量名 FROM 表;
 
 4.使用
 select 局部变量名；
*/

#举例
DELIMITER //
CREATE  PROCEDURE  test_var()
BEGIN 
       #声明局部变量
       DECLARE  a INT DEFAULT 0;
       DECLARE  b INT;
       DECLARE  emp_name VARCHAR(25);
       
       #赋值
       SET  a=1;
       SET b:=2;
       SELECT last_name INTO   emp_name FROM employees WHERE employee_id=101;
       
       SELECT a,b,emp_name;
      
END //       
DELIMITER ;

CALL test_var();


#举例1：声明局部变量，并分别赋值为employees表中
#          employee_id为102的last_name和salary
DELIMITER //
CREATE PROCEDURE test_pro()
BEGIN 
      #声明
      DECLARE empname VARCHAR(25);
      DECLARE sal DOUBLE(10,2) DEFAULT 0;
      #赋值
      SELECT  last_name,salary INTO empname,sal
      FROM employees 
      WHERE employee_id=102;
      
      #使用
      SELECT empname,sal;
END //     
DELIMITER ;

CALL test_pro();


#举例2：声明两个变量，求和并打印 （分别使用会话用户变量、局部变量的方式实现）
#方式1：会话用户变量
SET @v1=10;
SET @v2:=20;
SET @v3 :=@v1 +@v2;

SELECT @v3;

#方式2：局部变量
DELIMITER //
CREATE PROCEDURE add_value()
BEGIN 
        DECLARE value1,value2,sum_value INT;
        SET  value1=10;
        SET  value2:=20;
        SET    sum_value=value1+value2;
        SELECT sum_value;
END //        
DELIMITER ;
DROP PROCEDURE add_value;
CALL add_value();

#举例3：创建存储过程“different_salary”查询某员工和他领导的薪资差距，
#       并用IN参数emp_id接收员工id，用OUT参数dif_salary输出薪资差距结果
  
DELIMITER //
CREATE PROCEDURE different_salary(IN id INT,OUT dif_salary DOUBLE)
BEGIN 
          #声明变量
          DECLARE emp_sal DOUBLE DEFAULT 0;
          DECLARE mgr_sal DOUBLE DEFAULT 0;
          
          DECLARE mgr_id INT ;
          
         #赋值
         SELECT salary INTO emp_sal FROM employees WHERE employee_id=id;
                  
         SELECT manager_id INTO mgr_id FROM employees WHERE employee_id=id;
          
          SELECT salary INTO mgr_sal FROM employees WHERE employee_id=mgr_id;
          
          SET dif_salary = mgr_sal - emp_sal ;
          
          
END //
DELIMITER ;

CALL different_salary(102,@dif_sal);
SELECT @dif_sal;

1.2.4 对比会话用户变量与局部变量

2. 定义条件与处理程序

定义条件是事先定义程序执行过程中可能遇到的问题，处理程序定义了在遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力，避免程序异常停止运行

2.1 案例分析

#错误案例分析：
 DELIMITER //
 CREATE PROCEDURE UpdateDataNoCondition()
 BEGIN
 SET @x = 1;
 UPDATE employees SET email = NULL WHERE last_name = 'Abel';
 SET @x = 2;
 UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
 SET @x = 3;
 END //
 DELIMITER ;
 
#Column 'email' cannot be null
CALL UpdateDataNoCondition();
SELECT @x;

2.2 定义条件

定义条件就是给MySQL中的错误码命名

#格式：DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）


#举例1：定义“Field_Not_Be_NULL”错误名与MySQL中违反非空约束的错误类型
#       是“ERROR 1048 (23000)”对应。
#方式1：使用MYSQL_error_code 
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;

#方式2：使用sqlstate_value
DECLARE  Field_Not_Be_NULL CONDITION FOR SQLSTATE'23000';

#举例2：定义"ERROR 1148(42000)"错误，名称为command_not_allowed。

DECLARE command_not_allowed  CONDITION FOR 1148;

DECLARE  command_not_allowed CONDITION FOR  SQLSTATE '42000';

2.3 定义处理程序

#格式：DECLARE 处理方式 HANDLER FOR 错误类型处理语句

处理方式：处理方式有3个取值：CONTINUE、EXIT、UNDO

CONTINUE ：表示遇到错误不处理，继续执行。
EXIT ：表示遇到错误马上退出。
UNDO ：表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作。

#方法1：捕获sqlstate_value
 DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';
 
 #方法2：捕获mysql_error_value
 DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';
 
 #方法3：先定义条件，再调用
 DECLARE no_such_table CONDITION FOR 1146;
 DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';
 
 #方法4：使用SQLWARNING
 DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';
 
 #方法5：使用NOT FOUND
 DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';
 
 #方法6：使用SQLEXCEPTION
 DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';

2.4 案例解决

ROP PROCEDURE UpdateDataNoCondition;
#重新定义一个存储过程
 DELIMITER //
 CREATE PROCEDURE UpdateDataNoCondition()
 BEGIN
               #声明处理程序
               #处理方式1:
               DECLARE CONTINUE HANDLER FOR 1048 SET @prc_value=-1;
               #处理方式2：
              # DECLARE CONTINUE HANDLER FOR sqlstate '23000' SET @prc_value=-1;
                SET @x = 1;
		UPDATE employees SET email = NULL WHERE last_name = 'Abel';
		SET @x = 2;
		UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
		SET @x = 3;
 END //
 DELIMITER ;

#调用存储过程
CALL UpdateDataNoCondition();

SELECT  @prc_value,@x;

#2.5 举例：
#创建一个名称为“InsertDataWithCondition”的存储过程，

#准备工作
CREATE TABLE departments
 AS
 SELECT * FROM atguigudb.`departments`;

DESC departments; 
 
 ALTER TABLE departments
 ADD CONSTRAINT uk_dept_name UNIQUE(department_id);

# 定义存储过程
 DELIMITER //
 CREATE PROCEDURE InsertDataWithCondition()
 BEGIN
        SET @x = 1;
	INSERT INTO departments(department_name) VALUES('测试');
	SET @x = 2;
	INSERT INTO departments(department_name) VALUES('测试');
	SET @x = 3;
 END //
 DELIMITER ;

CALL InsertDataWithCondition();
 
SELECT @x; 

#删除此过程
DROP PROCEDURE InsertDataWithCondition;

#重新定义
 DELIMITER //
 CREATE PROCEDURE InsertDataWithCondition()
 BEGIN
        #处理程序
        #方式1：
        DECLARE EXIT HANDLER FOR 1062 SET @pro_value=-1;
        #方式2：
        #declare exit handler for sqlstate '23000' set  @pro_value=-1;
        #方式3：
        #定义条件
       # declare  entry  condition for  1062;
       # declare exit  handler for entry set  @pro_value=-1;
        SET @x = 1;
	INSERT INTO departments(department_name) VALUES('测试');
	SET @x = 2;
	INSERT INTO departments(department_name) VALUES('测试');
	SET @x = 3;
 END //
 DELIMITER ;
  
CALL InsertDataWithCondition();

SELECT @x, @pro_value;

3. 流程控制

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

3.1 分支结构之 IF

举例1:
DELIMITER //
CREATE PROCEDURE  test_if()
BEGIN 
       /*#情况1：
       declare stu_name varchar(15);
       
       if stu_name is null 
                   then select 'stu_name is null';
       end if ;            
        */
     
        /*#情况2：
        declare email varchar(25) default 'aaa';
        if  email is null
                   then select 'email is null';
        else select 'email is not null';
        
        end if;               
       */
        
        #情况3：多选一
        DECLARE age INT DEFAULT 20;
        
        IF age>40 THEN SELECT '中老年';
        ELSEIF  age >18 
                   THEN SELECT '青壮年';
        ELSEIF age >8  
                    THEN  SELECT '青少年';
        ELSE 
               SELECT '幼儿';
         END IF;      
        
           
       
       
       
       
END //
DELIMITER ;

CALL test_if();

DROP PROCEDURE test_if;


#举例2：声明存储过程“update_salary_by_eid1”，定义IN参数emp_id，输入员工编号。
#  判断该员工薪资如果低于8000元并且入职时间超过5年，就涨薪500元；否则就不变。
DELIMITER //
CREATE PROCEDURE update_salary_by_eid1(IN emp_id INT)
BEGIN 
        DECLARE emp_sal DOUBLE;
        DECLARE emp_hire_date DATE;
        
       SELECT salary INTO emp_sal FROM employees WHERE employee_id=emp_id;
       SELECT hire_date INTO emp_hire_date FROM employees WHERE employee_id=emp_id;
       
       IF emp_sal < 8000 AND DATEDIFF(NOW(),emp_hire_date)/365 > 5
        THEN  UPDATE employees 
        SET salary=salary+500 
         WHERE  employee_id=emp_id;
      END IF; 
END //            
DELIMITER ;

CALL  update_salary_by_eid1(104);



SELECT DATEDIFF(NOW(),hire_date)/365,employee_id,salary
FROM employees
WHERE salary <8000 AND DATEDIFF(NOW(),hire_date)/365 >5;

#举例3：声明存储过程“update_salary_by_eid2”，定义IN参数emp_id，输入员工编号。
#判断该员工薪资如果低于9000元并且入职时间超过5年，就涨薪500元；否则就涨薪100元。
DELIMITER //
CREATE PROCEDURE update_salary_by_eid2(IN emp_id INT)
BEGIN 
        DECLARE emp_sal DOUBLE;
        DECLARE emp_hire_date DATE;
        
       SELECT salary INTO emp_sal FROM employees WHERE employee_id=emp_id;
       SELECT hire_date INTO emp_hire_date FROM employees WHERE employee_id=emp_id;
       
       IF emp_sal < 9000 AND DATEDIFF(NOW(),emp_hire_date)/365 > 5
        THEN  UPDATE employees 
              SET salary=salary+500 
              WHERE  employee_id=emp_id;
        ELSE  UPDATE employees 
              SET salary=salary+100 
              WHERE  employee_id=emp_id;
      END IF; 
END //            
DELIMITER ;

CALL  update_salary_by_eid2(103);
CALL  update_salary_by_eid2(104);

SELECT DATEDIFF(NOW(),hire_date)/365,employee_id,salary
FROM employees
WHERE salary <9000 AND DATEDIFF(NOW(),hire_date)/365 >5;




#举例4：声明存储过程“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_sal DOUBLE;
        DECLARE bonus  DOUBLE ;
        
        SELECT salary INTO emp_sal FROM employees WHERE employee_id=emp_id;
        SELECT commission_pct INTO bonus FROM employees WHERE employee_id=emp_id;
        
        IF emp_sal<9000 
             THEN  UPDATE employees
                    SET salary=9000
                    WHERE  employee_id=emp_id;
        ELSEIF   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 IF;                      
        
END //            
DELIMITER ;

CALL update_salary_by_eid3(102);
CALL update_salary_by_eid3(103);
CALL update_salary_by_eid3(104);

SELECT *FROM  employees;

3.2 分支结构之 CASE


#3.2分支结构 case
#举例1:
DELIMITER //
CREATE PROCEDURE test_case()
BEGIN 
      #演示1：case....when...then (switch)
     /*
      declare var int default 2;
      case var 
           when 1 then select 'var =1';
           when 2  then select 'var =2';
           when 3  then select 'var= 3';
           else select 'other var';
      end case;     
       */
      #演示2：case when ....then ... when ..then (if)
      DECLARE var1 INT  DEFAULT 10;
       CASE WHEN  var1 >=100 THEN SELECT '三位数';
            WHEN var1>=10 THEN SELECT '两位数';
        ELSE SELECT '个位数';
        END CASE;     
      
      
      
      
END//
DELIMITER ;

CALL test_case();

#举例3：声明存储过程“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  DOUBLE;
       
       
       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 ;

CALL update_salary_by_eid4(103);
CALL update_salary_by_eid4(104);
CALL update_salary_by_eid4(105);

SELECT *
FROM employees
WHERE employee_id IN(103,104,105);



#举例4：声明存储过程update_salary_by_eid5，定义IN参数emp_id，输入员工编号。
#   判断该员工的入职年限，如果是0年，薪资涨50；如果是1年，薪资涨100；
#   如果是2年，薪资涨200；如果是3年，薪资涨300；如果是4年，薪资涨400；其他的涨薪500。
DELIMITER //
CREATE PROCEDURE  update_salary_by_eid5(IN emp_id INT)
BEGIN 
       #声明局部变量
       DECLARE hire_year DOUBLE ;
       
       SELECT DATEDIFF(NOW(),hire_date)/365 INTO hire_year
       FROM employees 
       WHERE  employee_id = emp_id;
       
       CASE  ROUND(hire_year)
           WHEN 0 THEN UPDATE employees
                       SET salary=salary+50
                       WHERE employee_id=emp_id;
           WHEN 1 THEN UPDATE employees
                       SET salary=salary+100
                       WHERE employee_id=emp_id; 
           WHEN 2 THEN UPDATE employees
                       SET salary=salary+200
                       WHERE employee_id=emp_id;
           WHEN 3 THEN UPDATE employees
                       SET salary=salary+300
                       WHERE employee_id=emp_id;                                   
           WHEN 4 THEN UPDATE employees
                       SET salary=salary+400
                       WHERE employee_id=emp_id;
           ELSE   UPDATE employees
                  SET salary=salary+500
                  WHERE employee_id=emp_id;  
        END CASE; 
END //                        
DELIMITER ;


CALL  update_salary_by_eid5(101);


SELECT *
FROM employees

3.3 循环结构之LOOP


#4.1循环结构之LOOP
/*
[loop_label:] LOOP
     循环执行的语句
END LOOP [loop_label]

*/

#举例1：
DELIMITER //
CREATE PROCEDURE test_loop()
BEGIN 
          #声明局部变量
          DECLARE num INT DEFAULT 1;
          
          loop_tabe1:LOOP 
                SET num = num+1;
                IF num >=10 THEN  LEAVE loop_tabe1; #结束语句
                END IF;
           END LOOP loop_tabe1;
           
           #查看
           SELECT num;    
END //
DELIMITER ;

CALL test_loop();


#举例2：
#当市场环境变好时，公司为了奖励大家，决定给大家涨工资。声明存储过程
#         “update_salary_loop()”，声明OUT参数num，输出循环次数。
#          存储过程中实现循环给大家涨薪，薪资涨为原来的1.1倍。
#           直到全公司的平均薪资达到12000结束。并统计循环次数。
DELIMITER //
CREATE  PROCEDURE update_salary_loop(OUT num INT)
BEGIN 
         DECLARE avg_sal DOUBLE;
         DECLARE  loop_count INT DEFAULT 0;#记录循环的次数
         
          SELECT AVG(salary) INTO avg_sal
            FROM employees;
         
         loop_lab:LOOP
            #循环条件
            #结束循环的条件
            IF  avg_sal >=12000 
                   THEN   LEAVE   loop_lab;  
             END IF;  
           #循环体
           # 低于12000 ，更新工资
           UPDATE employees
           SET salary=salary*1.1;
         
            SET loop_count=loop_count+1;  #记录循环的次数
            
            #迭代条件
            #跟新avg_sal的值
            SELECT AVG(salary) INTO avg_sal
            FROM employees;
             
            SELECT loop_count INTO num;
         
         END LOOP  loop_lab;                                
                                           
END //
DELIMITER ;
DROP PROCEDURE update_salary_loop;
CALL update_salary_loop(@m);
SELECT @m;

SELECT AVG(salary)FROM employees;

3.4 循环结构之WHILE

#4.2 循环结构之WHILE
/*
[while_label:] WHILE 循环条件  DO
              循环体
END WHILE [while_label];
*/

#举例1：
DELIMITER //
CREATE PROCEDURE test_while()
BEGIN      
           #初始条件
           DECLARE num INT DEFAULT 1;
           
           WHILE num <=10 DO
                  #循环体
                  
                  #迭代条件
                  SET num=num+1;                  
           END WHILE;
           
           SELECT num;                      
                
END //
DELIMITER ;

CALL  test_while();


#举例2：市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。声明存储过程
#“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;

SELECT AVG(salary) FROM employees;

3.5 循环结构之REPEAT

4.3 循环结构之REPEAT
/*

[repeat_label:] REPEAT
循环体的语句

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



*/

#举例1：
DELIMITER //
CREATE PROCEDURE test_repeat()
BEGIN 
       #声明
       DECLARE num INT DEFAULT 1;
       
       REPEAT 
             SET num=num+1;
             UNTIL num >=10
       END REPEAT;
       
       #查看
       SELECT  num;
              
END //       
DELIMITER ;

CALL test_repeat();


#举例2：当市场环境变好时，公司为了奖励大家，决定给大家涨工资。声明存储过程
#“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 ;
DROP PROCEDURE update_salary_repeat;

CALL update_salary_repeat(@num);
SELECT @num;

对比三种循环结构：

1、这三种循环都可以省略名称，但如果循环中添加了循环控制语句（LEAVE或ITERATE）则必须添加名称。

2、 LOOP：一般用于实现简单的"死"循环 WHILE：先判断后执行 REPEAT：先执行后判断，无条件至少执行一次

3.6 跳转语句之LEAVE语句

LEAVE语句：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。如果你有面向过程的编程语言的使用经验，你可以把 LEAVE 理解为 break。

格式:LEAVE 标记名


#5.1 LEAVE的使用
/*#举例1：创建存储过程 “leave_begin()”，声明INT类型的IN参数num。给BEGIN...END加标记名，、、
# 并附BEGIN...END中使用IF语句判断num参数的值。
如果num<=0，则使用LEAVE语句退出BEGIN...END；
如果num=1，则查询“employees”表的平均薪资；
如果num=2，则查询“employees”表的最低薪资；
如果num>2，则查询“employees”表的最高薪资。
IF语句结束后查询“employees”表的总人数。


*/

DELIMITER //
CREATE PROCEDURE leave_begin(IN num INT)
begin_label:BEGIN 
           IF num <=0 THEN LEAVE begin_label;
           ELSEIF num=1 THEN SELECT  AVG(salary)
                             FROM employees;
            ELSEIF num=2 THEN SELECT  MIN(salary)
                             FROM employees;                 
             ELSEIF num>2 THEN SELECT  MAX(salary)
                             FROM employees;  
            END IF;
            SELECT COUNT(*) FROM employees;
END //                                    
DELIMITER ;

CALL leave_begin(1);


#举例2：
#当市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。
#声明存储过程“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 ;

SELECT AVG(salary) FROM  employees;

CALL leave_while(@num);

SELECT @num;

3.7 跳转语句之ITERATE语句

ITERATE语句：只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序转到语句段开头处。如果你有面向过程的编程语言的使用经验，你可以把 ITERATE 理解为 continue，意思为“再次循环”。

格式：ITERATE label

举例： 定义局部变量num，初始值为0。循环结构中执行num + 1操作。
如果num < 10，则继续执行循环；
如果num > 15，则退出循环结构；
*/
DELIMITER //
CREATE PROCEDURE test_iterate()
BEGIN  
         DECLARE num INT DEFAULT 0;
   loop_label:LOOP
              SET num=num+1;
              IF num<10 THEN  ITERATE loop_label;
              ELSEIF num>15 THEN LEAVE loop_label;
              END IF ;
             
             SELECT '哈哈哈';
           END LOOP;      
END //               
DELIMITER ;

CALL test_iterate();

4. 游标

4.1 什么是游标（或光标）

随意定位到某一条记录，并对记录的数据进行处理

结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构

4.2 使用游标步骤

游标的使用步骤：
1.声明游标
2。打开游标
3.使用游标（从游标中获取数据）
4。关闭游标
*/

4.3 举例

 4.3 举例 
#创建存储过程“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_sal DOUBLE DEFAULT 0;
         DECLARE emp_sal DOUBLE ;
         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 emp_sal;
              
              SET  sum_sal= sum_sal+emp_sal;
              SET emp_count=emp_count+1;
              UNTIL  sum_sal >=limit_total_salary
         END  REPEAT ;
             SET total_count=emp_count;
             #关闭游标
             CLOSE emp_cursor;
END //              
DELIMITER ;

CALL get_count_by_limit_total_salary(200000,@k);
SELECT @k;

4.5 小结

跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。

但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据行进行加锁，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会消耗系统资源，造成内存不足，这是因为游标是在内存中进行的处理。

建议：养成用完之后就关闭的习惯，这样才能提高系统的整体效率。

补充：MySQL 8.0的新特性—全局变量的持久化**

#补充：MySQL 8.0的新特性—全局变量的持久化**
SET GLOBAL MAX_EXECUTION_TIME=2000;

SHOW VARIABLES LIKE	'%max_connections%';
SET persist max_connections=1000;

#重启MySQL服务器 ，再次查询max_connections的值：

SHOW VARIABLES LIKE	'%max_connections%';

课后练习

#第16章_变量、流程控制与游标练习题

 1. 变量 
题目：
#0.准备工作
CREATE DATABASE test16_var_cur;
 USE test16_var_cur;
 CREATE TABLE employees
 AS
 SELECT * FROM atguigudb.`employees`;
 CREATE TABLE departments
 AS
 SELECT * FROM atguigudb.`departments`;
 #无参有返回
#1. 创建函数get_count(),返回公司的员工个数
DELIMITER //
CREATE FUNCTION  get_count() 
RETURNS INT 
BEGIN 
        DECLARE emp_count INT;
         SELECT COUNT(*) INTO emp_count 
         FROM employees;
        RETURN emp_count;
             
END //
DELIMITER ;

DROP FUNCTION get_count;
SELECT get_count();
#有参有返回

#2. 创建函数ename_salary(),根据员工姓名，返回它的工资
DELIMITER //
CREATE FUNCTION ename_salary(emp_name VARCHAR(15))
RETURNS DOUBLE
BEGIN 
          SET @sal=0;#会话用户变量
           SELECT salary INTO @sal FROM employees WHERE last_name=emp_name;
         RETURN @sal;
END //
DELIMITER ;

DROP FUNCTION ename_salary;
SELECT *FROM employees;
SELECT ename_salary('Ernst')
#3. 创建函数dept_sal() ,根据部门名，返回该部门的平均工资
DELIMITER //
CREATE FUNCTION dept_sal(dep_name VARCHAR(15))
RETURNS DOUBLE
BEGIN 
          DECLARE avg_sal DOUBLE ;
           SELECT AVG(salary) INTO avg_sal
                  FROM employees  e JOIN departments d
                  ON e.department_id=d.department_id
                  WHERE d.department_name=dep_name;
         RETURN avg_sal;
                 
                 
END //
DELIMITER ;

DROP FUNCTION dept_sal;
SELECT *FROM departments;
SELECT dept_sal('Marketing');




#4. 创建函数add_float()，实现传入两个float，返回二者之和
DELIMITER //
CREATE FUNCTION add_float(f1 FLOAT ,f2 FLOAT )
RETURNS  FLOAT
BEGIN
          DECLARE SUM FLOAT ;
          SET   SUM=f1+f2;
          RETURN SUM;

END //
DELIMITER ;

DROP  FUNCTION add_float;
 



2. 流程控制 
题目：
#1. 创建函数test_if_case()，实现传入成绩，如果成绩>90,返回A，
#   如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
DELIMITER //
CREATE FUNCTION test_if_case(grade DOUBLE)
RETURNS CHAR(1)
BEGIN        
         DECLARE gra CHAR(1) ;
         IF grade>90 THEN  SET gra='A';
         ELSEIF grade >80 THEN SET gra='B'; 
         ELSEIF grade >60 THEN SET gra='C';
         ELSE SET  gra='D' ;
         END  IF;
         RETURN gra;
END //         
DELIMITER ;

SELECT   test_if_case(90);



#方式2：
DELIMITER //
CREATE FUNCTION test_if_case2(grade DOUBLE)
RETURNS CHAR(1)
BEGIN        
         DECLARE gra CHAR(1) ;
         CASE WHEN grade>90 THEN  SET gra='A';
         WHEN grade >80 THEN SET gra='B'; 
        WHEN grade >60 THEN SET gra='C';
         ELSE SET  gra='D' ;
         END  CASE;
         RETURN gra;
END //         
DELIMITER ;

SELECT   test_if_case2(76);
#要求：分别使用if结构和case结构实现
#2. 创建存储过程test_if_pro()，传入工资值，如果工资值<3000,
#则删除工资为此值的员工，如果3000 <= 工资值 <= 5000,
#则修改此工资值的员工薪资涨1000，否则涨工资500
DELIMITER //
CREATE PROCEDURE test_if_pro(IN sal DOUBLE)
BEGIN 
        IF sal <3000 THEN DELETE FROM employees
                          WHERE salary=sal;
        ELSEIF  sal <=5000 THEN  UPDATE employees
                                 SET salary =salary+1000
                                 WHERE  salary=sal;                  
        ELSE  UPDATE employees
              SET salary =salary+500
              WHERE  salary=sal;
        END IF;
END //               
DELIMITER ;

DROP PROCEDURE test_if_pro;

SELECT *FROM  employees;
CALL  test_if_pro(24000);

#3. 创建存储过程insert_data(),传入参数为 IN 的 INT 类型变量 insert_count,
#   实现向admin表中批量插入insert_count条记录
CREATE TABLE admin(
 id INT PRIMARY KEY AUTO_INCREMENT,
 user_name VARCHAR(25) NOT NULL,
 user_pwd VARCHAR(35) NOT NULL
 );
 SELECT * FROM admin;
 
DELIMITER //
CREATE PROCEDURE insert_data(IN insert_count INT)
BEGIN 
        DECLARE a INT DEFAULT 1;
        
        WHILE a<=insert_count DO 
                INSERT INTO admin(user_name,user_pwd)
                VALUE('tom',a);
                SET a=a+1;
        END WHILE;       
END //
DELIMITER ; 
 
CALL insert_data(10);




3. 游标的使用 
#创建存储过程update_salary()，参数1为 IN 的INT型变量dept_id，
#表示部门id；参数2为 IN的INT型变量change_sal_count，
#表示要调整薪资的员工个数。查询指定id部门的员工信息，
#按照salary升序排列，根据hire_date的情况，
#调整前change_sal_count个员工的薪资，详情如下。
DELIMITER //
CREATE PROCEDURE update_salary(IN dept_id INT,IN change_sal_count INT )
BEGIN 
           DECLARE i INT DEFAULT 1 ;
           DECLARE emp_id VARCHAR(25)  ;
           DECLARE emp_date DATE;
           DECLARE count_cursor CURSOR FOR  SELECT employee_id,hire_date 
                                            FROM employees
                                            WHERE department_id=dept_id
                                            ORDER BY salary ;
           OPEN  count_cursor;
           WHILE i<=change_sal_count DO
                   FETCH count_cursor INTO emp_id,emp_date;
                                      
                   IF YEAR(emp_date) < 1995 THEN  UPDATE employees 
                                                  SET salary=salary*1.2
                                                  WHERE  employee_id=emp_id;
                   ELSEIF YEAR(emp_date) <=1998 THEN UPDATE employees
                                                SET salary=1.15
                                                WHERE  employee_id=emp_id;
                   ELSEIF  YEAR(emp_date) <=2001 THEN UPDATE employees
                                                 SET salary=salary*1.1
                                                 WHERE    employee_id=emp_id;                         
                   ELSE    UPDATE employees
                           SET salary=salary*1.05
                           WHERE   employee_id=emp_id; 
                           
                   END IF;  
                   SET i=i+1;
            END WHILE ;
            CLOSE count_cursor;
                  
                                           
END //
DELIMITER ;
DROP PROCEDURE update_salary;

SELECT salary 
FROM employees
WHERE department_id=90
ORDER BY salary ;

SELECT *FROM employees;
   
CALL   update_salary(90,3);