MySQL 基础篇 -- 变量、流程控制与游标

最新推荐文章于 2023-07-27 21:36:10 发布
最新推荐文章于 2023-07-27 21:36:10 发布
阅读量263 收藏 1
点赞数
分类专栏： MySQL 文章标签： mysql 数据库 dba
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42480750/article/details/123334863
版权

文章目录

一、变量

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

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

1.1 系统变量

  • 分类
  1. 系统变量分为全局系统变量（需要添加global关键字）以及会话系统变量（需要添加session关键字）；
  2. 有时也把全局系统变量简称为全局变量，有时也把会话系统变量称为local变量；
  3. 未指定关键字时默认为会话级别，静态变量（在 MySQL 服务实例运行期间它们的值不能使用 set 动态修改）属于特殊的全局系统变量。
  • 查看所有或部分系统变量
#查看所有全局变量
SHOW GLOBAL VARIABLES;
#查看所有会话变量
SHOW SESSION VARIABLES;
# 或
SHOW VARIABLES;

#查看满足条件的部分系统变量。
SHOW GLOBAL VARIABLES LIKE '%标识符%';
#查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%标识符%';
  • 查看指定系统变量
  1. 作为 MySQL 编码规范，MySQL 中的系统变量以@@开头；
  2. @@global仅用于标记全局系统变量，@@session仅用于标记会话系统变量；
  3. @@首先标记会话系统变量，如果会话系统变量不存在，则标记全局系统变量。
#查看指定的系统变量的值
SELECT @@global.变量名;
#查看指定的会话变量的值
SELECT @@session.变量名;
#或者
SELECT @@变量名;
  • 修改系统变量的值
  1. 修改MySQL配置文件，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）
  2. 在MySQL服务运行期间，使用set命令重新设置系统变量的值：
#为某个系统变量赋值
#方式1：
SET @@global.变量名=变量值;
#方式2：
SET GLOBAL 变量名=变量值;

#为某个会话变量赋值
#方式1：
SET @@session.变量名=变量值;
#方式2：
SET SESSION 变量名=变量值;

SELECT @@global.autocommit;
SET GLOBAL autocommit=0;

SELECT @@session.tx_isolation;
SET @@session.tx_isolation='read-uncommitted';

SET GLOBAL max_connections = 1000;
SELECT @@global.max_connections;

1.2 用户变量

  • 分类
  1. 用户变量是用户自己定义的，作为 MySQL 编码规范，MySQL 中的用户变量以 一个@开头。根据作用范围不同，又分为会话用户变量和局部变量。
  2. 会话用户变量：作用域和会话变量一样，只对当前连接会话有效。
  3. 局部变量：只在 BEGIN 和 END 语句块中有效。局部变量只能在存储过程和函数中使用。

1.2.1 会话用户变量

  • 定义
#方式1：“=”或“:=”
SET @用户变量 = 值;
SET @用户变量 := 值;

#方式2：“:=” 或 INTO关键字
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量 [FROM 等子句];
  • 查看：SELECT @用户变量
SET @a = 1;
SELECT @a;

SELECT @num := COUNT(*) FROM employees;
SELECT @num;

SELECT AVG(salary) INTO @avgsalary FROM employees;
SELECT @avgsalary;

SELECT @big; #查看某个未声明的变量时，将得到NULL值

1.2.2 局部变量

  • 简介
  1. 定义：可以使用DECLARE语句定义一个局部变量；
  2. 作用域：仅仅在定义它的BEGIN ... END中有效；
  3. 位置：只能放在BEGIN ... END中，而且只能放在第一句；
BEGIN
  #声明局部变量
  DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值];
  DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值];

  #为局部变量赋值
  SET 变量名1 = 值;
  SELECT 值 INTO 变量名2 [FROM 子句];
  
  #查看局部变量的值
  SELECT 变量1,变量2,变量3;
END
  • 定义
DECLARE 变量名 类型 [default 值]; # 如果没有DEFAULT子句，初始值为NULL

DECLARE myparam INT DEFAULT 100;
  • 赋值
  1. 一般用于赋简单的值
SET 变量名=值;
SET 变量名:=值;
  1. 一般用于赋表中的字段值
SELECT 字段名或表达式 INTO 变量名 FROM 表;
  • 使用：SELECT 局部变量名
  1. 声明局部变量，并分别赋值为 employees 表中 employee_id 为 102 的 last_name 和 salary
DELIMITER //

CREATE PROCEDURE set_value()
BEGIN
  DECLARE emp_name VARCHAR(25);
  DECLARE sal DOUBLE(10,2);

  SELECT last_name,salary INTO emp_name,sal
  FROM employees
  WHERE employee_id = 102;
  
  SELECT emp_name,sal;
END //

DELIMITER ;
  1. 声明两个变量，求和并打印 （分别使用会话用户变量、局部变量的方式实现）
#方式1：使用用户变量
SET @m=1;
SET @n=1;
SET @sum=@m+@n;

SELECT @sum;

#方式2：使用局部变量
DELIMITER //

CREATE PROCEDURE add_value()
BEGIN
  #局部变量
  DECLARE m INT DEFAULT 1;
  DECLARE n INT DEFAULT 3;
  DECLARE SUM INT;
  
  SET SUM = m+n;
  SELECT SUM;
END //

DELIMITER ;
  • 创建存储过程 different_salary 查询某员工和他领导的薪资差距，并用 IN 参数 emp_id 接收员工 id，用 OUT 参数 dif_salary 输出薪资差距结果。
#声明
DELIMITER //

CREATE PROCEDURE different_salary(IN emp_id INT,OUT dif_salary DOUBLE)
BEGIN
  #声明局部变量
  DECLARE emp_sal,mgr_sal DOUBLE DEFAULT 0.0;
  DECLARE mgr_id INT;

  SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
  SELECT manager_id INTO mgr_id FROM employees WHERE employee_id = emp_id;
  SELECT salary INTO mgr_sal FROM employees WHERE employee_id = mgr_id;

  SET dif_salary = mgr_sal - emp_sal;
END //

DELIMITER ;

#调用
SET @emp_id = 102;
CALL different_salary(@emp_id,@diff_sal);

#查看
SELECT @diff_sal;
  • 对比会话用户变量与局部变量
作用域定义位置语法
会话用户变量当前会话会话的任何地方加@符号，不用指定类型
局部变量定义它的BEGIN END中BEGIN END的第一句话一般不用加@，需要指定类型

二、定义条件与处理程序

  • 简介
  1. 定义条件是事先定义程序执行过程中可能遇到的问题；
  2. 处理程序定义了在遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行；
  3. 增强存储程序处理问题的能力，避免程序异常停止运行，在存储过程、存储函数中都是支持的；

2.1 案例分析

  • 创建一个名称为UpdateDataNoCondition的存储过程。代码如下：
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 ;
  • 调用存储过程：
mysql> CALL UpdateDataNoCondition();
ERROR 1048 (23000): Column 'email' cannot be null

mysql> SELECT @x;
+------+
|  @x  |
+------+
|  1   |
+------+
1 row in set (0.00 sec)

在存储过程中未定义条件和处理程序，且当存储过程中执行的SQL语句报错时，MySQL数据库会抛出错误，并退出当前SQL逻辑，不再向下继续执行。

2.2 定义条件

定义条件就是给MySQL中的错误码命名，这有助于存储的程序代码更清晰。它将一个错误名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的DECLARE HANDLER语句中。

  • 语法
DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）
  • 错误码
  1. MySQL_error_code 和 sqlstate_value 都可以表示 MySQL 的错误。
  2. MySQL_error_code是数值类型错误代码；sqlstate_value是长度为5的字符串类型错误代码。
  3. 例如，在 ERROR 1418 (HY000) 中，1418 是 MySQL_error_code，‘HY000’ 是 sqlstate_value。
  4. 例如，在 ERROR 1142 (42000) 中，1142 是 MySQL_error_code，‘42000’ 是 sqlstate_value。
  • 举例1：定义 Field_Not_Be_NULL 错误名与MySQL中违反非空约束的错误类型是 ERROR 1048 (23000) 对应。
#使用MySQL_error_code
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;

#使用sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';
  • 举例2：定义 ERROR 1148(42000) 错误，名称为 command_not_allowed。
#使用MySQL_error_code
DECLARE command_not_allowed CONDITION FOR 1148;

#使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';

2.3 定义处理程序

含义：为SQL执行过程中发生的某种类型的错误定义特殊的处理程序。

  • 语法
DECLARE 处理方式 HANDLER FOR 错误类型 处理语句
  • 处理方式
取值说明
CONTINUE表示遇到错误不处理，继续执行。
EXIT表示遇到错误马上退出。
UNDO表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作。
  • 错误类型
条件说明
SQLSTATE 字符串错误码表示长度为 5 的 sqlstate_value 类型的错误代码；
MySQL_error_code匹配数值类型错误代码；
错误名称表示 DECLARE ... CONDITION 定义的错误条件名称。
SQLWARNING匹配所有以 01 开头的 SQLSTATE 错误代码；
NOT FOUND匹配所有以 02 开头的 SQLSTATE 错误代码；
SQLEXCEPTION匹配所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE 错误代码；
  • 处理语句
  1. 如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。
  2. 语句可以是像 SET 变量 = 值 这样的简单语句，也可以是使用 BEGIN ... END 编写的复合语句。
  • 实例
#方法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 案例解决

  • UpdateDataNoCondition：在存储过程中，定义处理程序，捕获 sqlstate_value 值，当遇到 MySQL_error_code 值为 1048 时，执行 CONTINUE 操作，并且将 @proc_value 的值设置为 -1。
  1. 定义
DELIMITER //

CREATE PROCEDURE UpdateDataNoCondition()
BEGIN
  #定义处理程序
  DECLARE CONTINUE HANDLER FOR 1048 SET @proc_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 ;
  1. 调用
mysql> CALL UpdateDataWithCondition();
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @x,@proc_value;
+------+-------------+
|  @x  | @proc_value |
+------+-------------+
|  3   |      -1     |
+------+-------------+
1 row in set (0.00 sec)
  • InsertDataWithCondition：在存储过程中，定义处理程序，捕获 sqlstate_value 值，当遇到 sqlstate_value 值为 23000 时，执行 EXIT 操作，并且将 @proc_value 的值设置为 -1。
  1. 准备工作
#准备工作
CREATE TABLE departments
AS
SELECT * FROM atguigudb.`departments`;

ALTER TABLE departments
ADD CONSTRAINT uk_dept_name UNIQUE(department_id);
  1. 定义
DELIMITER //

CREATE PROCEDURE InsertDataWithCondition()
BEGIN
  DECLARE duplicate_entry CONDITION FOR SQLSTATE '23000' ;
  DECLARE EXIT HANDLER FOR duplicate_entry SET @proc_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 ;
  1. 调用
mysql> CALL InsertDataWithCondition();
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @x,@proc_value;
+------+-------------+
|  @x  | @proc_value |
+------+-------------+
|  2   |      -1     |
+------+-------------+
1 row in set (0.00 sec)

三、流程控制

  • 分类

针对于MySQL 的流程控制语句主要有 3 类。注意：只能用于存储程序。

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

3.1 分支结构之 IF

  • 语法
IF 表达式1
THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF
  • 举例1
IF val IS NULL
THEN SELECT 'val is null';
ELSE SELECT 'val is not null';
END 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_salary DOUBLE;
  DECLARE hire_year DOUBLE;

  SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;

  SELECT DATEDIFF(CURDATE(),hire_date)/365 INTO hire_year
  FROM employees WHERE employee_id = emp_id;
  
  IF emp_salary < 8000 AND hire_year > 5
    THEN UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;
  END IF;
END //

DELIMITER ;
  • 举例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_salary DOUBLE;
  DECLARE hire_year DOUBLE;

  SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;

  SELECT DATEDIFF(CURDATE(),hire_date)/365 INTO hire_year
  FROM employees WHERE employee_id = emp_id;

  IF emp_salary < 9000 AND hire_year > 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 ;
  • 举例4
  1. 声明存储过程update_salary_by_eid3，定义IN参数 emp_id，输入员工编号。
  2. 判断该员工薪资如果低于 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 ;

3.2 分支结构之 CASE

  • 语法
# 情况一：类似于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后面不需要）
  • 举例1：使用CASE流程控制语句的第1种格式，判断val值等于1、等于2，或者两者都不等。
CASE val
  WHEN 1 THEN SELECT 'val is 1';
  WHEN 2 THEN SELECT 'val is 2';
  ELSE SELECT 'val is not 1 or 2';
END CASE;
  • 举例2：使用CASE流程控制语句的第2种格式，判断val是否为空、小于0、大于0或者等于0。
CASE
  WHEN val IS NULL THEN SELECT 'val is null';
  WHEN val < 0 THEN SELECT 'val is less than 0';
  WHEN val > 0 THEN SELECT 'val is greater than 0';
  ELSE SELECT 'val is 0';
END CASE;
  • 举例3
  1. 声明存储过程update_salary_by_eid4，定义IN参数emp_id，输入员工编号。
  2. 判断该员工薪资如果低于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 ;
  • 举例4
  1. 声明存储过程update_salary_by_eid5，定义IN参数emp_id，输入员工编号。
  2. 判断该员工的入职年限，如果是0年，薪资涨50；如果是1年，薪资涨100；如果是2年，薪资涨200；如果是3年，
    薪资涨300；如果是4年，薪资涨400；其他的涨薪500。
DELIMITER //

CREATE PROCEDURE update_salary_by_eid5(IN emp_id INT)
BEGIN
  DECLARE emp_sal DOUBLE;
  DECLARE hire_year DOUBLE;

  SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
  SELECT ROUND(DATEDIFF(CURDATE(),hire_date)/365) INTO hire_year FROM employees
  WHERE employee_id = emp_id;
  
  CASE 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 ;

3.3 循环结构之 LOOP

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

  • 基本格式
# 其中，loop_label表示LOOP语句的标注名称，该参数可以省略。
[loop_label:] LOOP
  循环执行的语句
END LOOP [loop_label]
  • 举例1：使用LOOP语句进行循环操作，id值小于10时将重复执行循环过程。
DECLARE id INT DEFAULT 0;

add_loop:LOOP
  SET id = id +1;
  IF id >= 10 THEN LEAVE add_loop;
  END IF;
END LOOP add_loop;
  • 举例2
  1. 当市场环境变好时，公司为了奖励大家，决定给大家涨工资。
  2. 声明存储过程update_salary_loop()，声明OUT参数num，输出循环次数。
  3. 存储过程中实现循环给大家涨薪，薪资涨为原来的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 ;

3.4 循环结构之 WHILE

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

  • 基本格式
# while_label 为 WHILE 语句的标注名称
[while_label:] WHILE 循环条件 DO
循环体
END WHILE [while_label];
  • 举例1：WHILE 语句示例，i 值小于 10 时，将重复执行循环过程
DELIMITER //

CREATE PROCEDURE test_while()
BEGIN
  DECLARE i INT DEFAULT 0;
  
  WHILE i < 10 DO
    SET i = i + 1;
  END WHILE;

  SELECT i;
END //

DELIMITER ;

#调用
CALL test_while();
  • 举例2
  1. 市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。
  2. 声明存储过程update_salary_while()，声明OUT参数num，输出循环次数。
  3. 存储过程中实现循环给大家降薪，薪资降为原来的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 ;

3.5 循环结构之 REPEAT

  • 简介
  1. REPEAT语句创建一个带条件判断的循环过程。
  2. 与WHILE循环不同的是，REPEAT 循环首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。
  • 基本格式
# repeat_label为REPEAT语句的标注名称，该参数可以省略
[repeat_label:] REPEAT
  循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]
  • 举例1
DELIMITER //

CREATE PROCEDURE test_repeat()
BEGIN
  DECLARE i INT DEFAULT 0;

  REPEAT
    SET i = i + 1;
    UNTIL i >= 10
  END REPEAT;
  
  SELECT i;
END //

DELIMITER ;
  • 举例2
  1. 当市场环境变好时，公司为了奖励大家，决定给大家涨工资。
  2. 声明存储过程update_salary_repeat()，声明OUT参数num，输出循环次数。
  3. 存储过程中实现循环给大家涨薪，薪资涨为原来的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 ;

3.6 跳转语句之 LEAVE

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

  • 基本格式
# '标记名'表示循环的标志。LEAVE和BEGIN ... END或循环一起被使用。
LEAVE 标记名
  • 举例1：创建存储过程leave_begin()，声明INT类型的IN参数num。给BEGIN…END加标记名，并在BEGIN…END中使用IF语句判断num参数的值。
  1. 如果num<=0，则使用LEAVE语句退出BEGIN…END；
  2. 如果num=1，则查询“employees”表的平均薪资；
  3. 如果num=2，则查询“employees”表的最低薪资；
  4. 如果num>2，则查询“employees”表的最高薪资。
  5. 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;
  ELSE
    SELECT MAX(salary) FROM employees;
  END IF;

  SELECT COUNT(*) FROM employees;
END //

DELIMITER ;
  • 举例2
  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 ;

3.7 跳转语句之 ITERATE

  • 简介
  1. 只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序转到语句段开头处。
  2. 如果你有面向过程的编程语言的使用经验，你可以把 ITERATE 理解为 continue，意思为“再次循环”。
  • 基本格式
# label参数表示循环的标志
ITERATE label
  • 举例
  1. 定义局部变量num，初始值为0。循环结构中执行num + 1操作。
  2. 如果num < 10，则继续执行循环；
  3. 如果num > 15，则退出循环结构；
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;
  END LOOP my_loop;
END //

DELIMITER ;

四、游标

4.1 定义

  1. 游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。
  2. 在 SQL 中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。
  3. MySQL中游标可以在存储过程和函数中使用。

4.2 使用

  • 声明游标
# 基本形式
DECLARE cursor_name CURSOR FOR select_statement;

# 例
DECLARE cur_emp CURSOR FOR SELECT employee_id,salary FROM employees;
DECLARE cursor_fruit CURSOR FOR SELECT f_name, f_price FROM fruits;
  • 打开游标
OPEN cursor_name

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

OPEN cur_emp;
  • 使用游标
FETCH cursor_name INTO var_name [, var_name] ...

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

FETCH cur_emp INTO emp_id, emp_sal ;
# 游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致
  • 关闭游标

# 游标会占用系统资源 ，如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率。
CLOSE cursor_name

# 关闭游标之后，我们就不能再检索查询结果中的数据行，如果需要检索只能再次打开游标。
CLOSE cur_emp;

4.3 举例

  1. 创建存储过程get_count_by_limit_total_salary()，声明IN参数 limit_total_salary，DOUBLE类型；声明OUT参数total_count，INT类型。
  2. 函数的功能可以实现累加薪资最高的几个员工的薪资值，直到薪资总和达到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 ;

4.4 小结

  1. 游标是 MySQL 的一个重要的功能，为逐条读取结果集中的数据，提供了完美的解决方案。
  2. 跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。
  3. 但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据行进行加锁，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会消耗系统资源，造成内存不足，这是因为游标是在内存中进行的处理。
  4. 建议：养成用完之后就关闭的习惯，这样才能提高系统的整体效率。
L小Ray想有腮
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql声明游标的方法
12-14
mysql声明游标的方法： 1、声明变量和游标 declare result varchar(3000); declare flag1 varchar(100); DECLARE done INT DEFAULT FALSE; declare cur1 CURSOR FOR SELECT flag from bmlx; 2、变量赋值 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; set result = ''; 3、打开游标和循环 open cur1; read_loop: LOOP — 给游标赋值 FETCH cur1
python与mysql基础-16-导入包之方法一.ev4.rar
最新发布
01-29
本篇内容主要聚焦于Python导入包的方法，结合MySQL数据库的基础应用进行讨论。Python与MySQL的结合使用在数据处理和Web开发中十分常见。 首先，我们来理解Python中的"导入包"。Python的模块化设计允许我们将代码...
mysql游标的作用_mysql游标的用法及作用
weixin_33632843的博客
01-19 326
例子：当前有三张表A、B、C其中A和B是一对多关系，B和C是一对多关系，现在需要将B中A表的主键存到C中；常规思路就是将B中查询出来然后通过一个update语句来更新C表就可以了，但是B表中有2000多条数据，难道要执行2000多次？显然是不现实的；最终找到写一个存储过程然后通过循环来更新C表，然而存储过程中的写法用的就是游标的形式。【简介】游标实际上是一种能从包括多条数据记录的结果集中每次提取一...
Mysql变量以及流程控制详解
会飞的猫的博客
01-26 1043
Mysql本质是一种编程语言，需要很多变量来保存数据。Mysql中很多的属性控制都是通过mysql中固有的变量来实现的。 系统变量 系统内部定义的变量，系统变量针对所有用户（MySQL客户端）有效。 查看系统所有变量：show variables [like ‘pattern’]; Mysql允许用户使用select查询变量的数据值（系统变量） 基本语法：select @@变量名; select @@autocommit; 修改系统变量：分为两种修改方式 1、 局部修改（会话级别）：.
MySql基础篇——变量、流程控制与游标
qq_51114283的博客
07-01 566
mysql基础篇——变量、流程控制与游标
Mysql 游标使用动态变量
Elvis技术小屋
07-31 4040
从语法上来讲DECLARE cur CURSOR for语句必须写在SET @sqlstr前面，这就意味着不能通过先执行一条动态语句根据结果拼凑游标的内容，而且游标在定义的时候也没有参数的概念，而是将定义的结构完全当作一个字符串直接处理，不会做任何的处理，也就是静态游标吧。关于dynamic cursor的内容官方有解释http://dev.mysql.com/doc/refman/5.6/en/...
python与mysql基础-11-模块定位顺序之注意点2.ev4.rar
01-18
本篇将详细讲解Python的模块定位顺序以及与MySQL交互的基础知识。 首先，让我们关注Python的模块查找顺序。当使用`import`语句导入模块时，Python会按照以下步骤查找模块： 1. 当前目录：Python首先检查当前脚本...
oracle-MySQL笔记整理资料 适合初学者
04-02
6. 高级特性：如分区、物质化视图、游标、事务管理和并发控制。 MySQL则是一款开源、轻量级的关系型数据库，适合中小型企业及互联网应用。其特点包括易用性、高效性和跨平台兼容性。MySQL的入门学习要点有： 1. ...
php_mysql_面试题_2011_03-28
03-21
1. PHP基础：变量、数据类型、流程控制（if-else, switch, for, while）、函数、类与对象、错误和异常处理。 2. MySQL基础：数据库创建、表结构设计、数据类型、插入和查询数据、更新和删除记录。 3. PHP与MySQL交互...
SQL面试经典版-整理篇
12-27
- Transact-SQL（T-SQL）：SQL Server的扩展SQL语法，包括变量、流程控制、异常处理等。 - Service Broker：SQL Server的消息队列服务，用于异步通信和任务调度。 - Indexing Strategies：包括非聚集索引、覆盖...
mysql变量与游标
L的博客
11-09 726
MySQL会将该命令的配置保存到数据目录下的mysqld-auto.cnf文件中，下次启动时会读取该文件，用其中的配置来覆盖默认的配置文件。MYSQL中的系统变量以两个@开头，其中“@@global”仅用于标记全局系统变量，其他同理。方式一：修改MySQL配置文件，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）MySQL的用户变量以一个@为开头，根据作用范围不同又分为会话用户变量和局部变量。针对于当前会话是有效的，一旦结束会话，重新建立起新的会话就会失效了。
mysql游标中可以用户变量,手把手教你mysql(十五)游标变量流程控制
weixin_39911475的博客
03-18 504
手把手教你mysql(十五)游标变量流程控制前言:这是我准备的测试用数据Mysql>DROP TABLE IF EXISTS `t_user`;Mysql>CREATE TABLE t_user(id INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(20),PASSWORD VARCHAR(20));Mysql>DROP TABLE...
【MySQL基础】变量、流程控制和游标
qq_38149054的博客
05-07 376
变量、游标、流程控制
MySQL的变量、流程控制与游标
ggggggzzzzz的博客
07-27 550
MySQL的变量、流程控制与游标
MySQL中的游标
伤心的辣条
04-20 2952
游标（cursor）是一个存储在MySQL服务器上的数据库查询， 它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游 标之后，应用程序可以根据需要滚动或浏览其中的数据。注意：MySQL游标只能用于 存储过程（和函数）。
一条一条来，MySQL变量、流程控制与游标，完整详细可收藏
叶绿体不忘呼吸
11-30 1687
1. 变量 在MySQL数据库的存储过程和函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终的结果数据。在 MySQL 数据库中，变量分为系统变量以及用户自定义变量。 1.1 系统变量 1.1.1 系统变量分类 变量由系统定义，不是用户定义，属于服务器层面。启动MySQL服务，生成MySQL服务实例期间，MySQL将为MySQL服务器内存中的系统变量赋值，这些系统变量定义了当前MySQL服务实例的属性、特征。这些系统变量的值要么是编译MySQL时参数的默认值，要么是配置文...
Mysql游标的使用：对查询的结果进行遍历
热门推荐
coder_afly的博客
11-19 1万+
问题描述：建立一个物料分类的树状图。实现效果如下： 框架使用的是Ztree， zTree 是一个依靠 jQuery 实现的多功能 “树插件”。（导航、左树右表）有兴趣的同学可以参考下面的链接： http://www.treejs.cn 部分表结构如下图所示： 由parent_id和parent_ids字段确认该分类在树状结构中的位置，今天在这里就不详细描述树结构的实现了。（Ztre...
[转]mysql 存储过程中使用多游标
weixin_33720452的博客
09-13 365
From : http://www.netingcn.com/mysql-procedure-muti-cursor.html mysql的存储过程可以很方便使用游标来实现一些功能，存储过程的写法大致如下： 先创建一张表，插入一些测试数据： DROP TABLE IF EXISTS netingcn_proc_test; CREATE TABLE `netingcn_proc_test...
第16章_变量、流程控制与游标.docx
01-17
此系列资源适用于MySQL数据库的学习，以及高级SQL语句

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值