MySQL 游标的详解

MySQL 游标的详解讲解

---

每博一文案

曾经看过一个这样的故事：
有一个人非常喜爱金币，他从年轻的时候就开始收集、累积金币。
直到有一天他存了三万个金币，他想好好地享受接下来的生活，不想再累积、追求金币了。
正当他这样想时，有一个老人突然来找他，这位老人就是死神。
死神对他说：“你的生命已到尽头了，我是来带你走的。”
这个人非常吃惊，好不容易累积了三万个金币，居然现在就要走了。
于是他要求死神再给他三天时间，并愿意分给死神一万个金币，死神不答应。
他以为死神嫌太少，于是要求只要给他两天的时间，就分给死神两万个金币，死神仍然不答应。
他又要求死神给他一天的时间，就分给死神三万个金币，死神还是不答应。
最后，他哭着要求死神，只要给他三分钟，写几句话给后代的人，他便给死神三万个金币。
死神看他那么可怜，终于答应给他三分钟，他便在纸上写了一句话：
“世人啊！请好好珍惜人生的时光吧！因为三万个金币也买不回一个小时。”如果没了生命，再多的钱也都没有意义。
人生短暂，就像石头相撞时所发出的一点火花转瞬即逝。
智者和愚者的区别就在于谁懂得珍惜健康，愚者用健康换取财富，智者为健康可放下一切。
没有什么事情，值得我们用健康去交换。
                               ——————   一禅心灵庙语

---

---

1. 游标的概念

虽然我们也可以通过筛选条件 WHERE 和 HAVING ,或者是限定返回记录的关键字 LIMIT 返回一条记录。但是却无法在结果集中像指针一样，向前定位一条记录，向后定位一条记录，或者是随意定位到某一条记录。并对记录的数据进行处理。

这个时候，就可以用到游标。游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录，进行定位，并对指向的记录中的数据进行操作的数据结构。游标让 SQL 这种面向集合的语言有了面向过程开发的能力。

在 SQL 中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标充当了指针的作用，我们可以操作游标对数据进行操作。

我们其实可以把游标理解成是一个查询的结果集,让我们可以通过像操作指针一样定位，到结果集中的某个记录，修改，取出数据

MySQL中游标可以在存储过程和函数中使用。

比如，我们查询了 employees 数据表中工资高于15000的员工都有哪些：

SELECT employee_id,last_name,salary FROM employees
WHERE salary > 15000;

这里我们就可以通过游标来操作数据行，如图所示此时游标所在的行是“108”的记录，我们也可以在结果 集上滚动游标，指向结果集中的任意一行。

2. 使用游标的步骤

游标必须在声明处理程序之前被声明，并且变量和条件还必须在声明游标或处理程序之前被声明。如果我们想要使用游标，一般需要经历 四个步骤 。不同的 DBMS 中，使用游标的语法可能略有不同。

第一步：声明游标

在MySQL中，使用 DECLARE 关键字来声明游标，其语法的基本形式如下：

DECLARE cursor_name CURSOR FOR select..(查询结果集);

这个语法适用于 MySQL，SQL Server，DB2 和 MariaDB。如果是用 Oracle 或者 PostgreSQL，需要写成：

DECLARE cursor_name CURSOR IS select_statement;

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

比如：

DECLARE cursor_emp CURSOR FOR
SELECT employee_id,salary FROM employees;

DECLARE cur_emp CURSOR FOR
SELECT last_name, salary FROM employees;

第二步：打开游标

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

打开游标的基本格式语法如下：

OPEN 游标名;

例如：打开我们前面创建的游标

OPEN cursor_emp;

OPEN cur_emp;

第三步：使用游标（从游标(结果集)中取得数据）

语法如下 ：

FETCH 游标名 INTO 变量名1,变量名2...

/*将游标结果集中的数据取出，并赋值到 变量1，变量2...当中
注意：变量需要在使用游标之前先定义声明好。*/

注意游标的查询结果集中的字段数，必须跟 INTO 赋值到的变量数一致，否侧，在执行的时候，MySQL会提示错误，尽量让变量的数据类型与 游标(查询的结果集)的数据类型一致，防止，赋值到的数据，出现错误，截断丢失的情况。

例如：我们将我们刚刚 定义的游标 cursor_emp 结果集数据取出

FETCH cursor_emp INTO emp_id ,emp_sal;

上述代码的作用是，使用 cursor_emp 这个游标来读取当前行，并且将数据保存到 emp_id,emp_sal 这两个变量中，游标指针直到下一行。如果游标读取的数据有多个列名，则在 INTO 关键字后面赋值给多个变量名即可。

注意：emp_id,emp_sal 变量必须在游标之前就定义好

第四步: 关闭游标

基本语法如下：

CLOSE 游标名 ; -- 关闭游标

有OPEN(打开游标) 就有 CLOSE(关闭游标)。

当我们使用完 游标 后需要关闭掉该游标，因为游标会占用 系统资源，如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率。而关闭游标的操作，会释放游标占用的系统资源。

关闭游标之后，我们就不能使用游标检索查询结果中的数据行，如果需要再次使用游标检索只能再次打开游标后，才能继续使用游标。

例如：关闭我们上述定义打开的游标

CLOSE cursor_emp;

---

3. 游标的使用案例

举例1: 创建存储过程“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 
     -- 声明局部变量,局部变量必须声明在begin...end最开始位置,不然报错
     DECLARE sum_sal DOUBLE DEFAULT 0.0;  -- 记录累加的工资总数
     DECLARE emp_sal DOUBLE DEFAULT 0.0 ;  -- 记录每个员工的工资
     DECLARE emp_count INT DEFAULT 0; -- 记录循环次数,必须default 设定默认初始值,不然为null,任何数
                                      -- 与 null 运算都为null,无法累加
     
     -- 1.声明定义游标
     DECLARE emp_cursor CURSOR FOR SELECT salary
     FROM employees 
     ORDER BY salary DESC; -- desc 降序
     
     -- 2.打开游标
     OPEN emp_cursor;
     
     -- 循环使用,游标
     REPEAT 
          -- 3. 使用游标(取结果集中的数据,赋值到变量中去)
          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; -- repeat 作用范围,结束
     
     SET total_count = emp_count;
     
     SELECT sum_sal; -- 查看局部变量 sum_sal 作用域在begin...end语句块中
     
     -- 关闭游标
     CLOSE emp_cursor;

END //
DELIMITER ;

调用存储过程，如下

-- 调用存储过程
SET @total_count = 0; -- 定义会话变量,必须赋初始值
CALL get_count_by_limit_total_salary(200000,@total_count);

---

查看循环次数：

SELECT @total_count;

---

举例2: 创建存储过程 update_salary( ),参数为 IN 的int 类型的变量dep_id 表示部门id,参数2 为 in 的int类型变量。chang_sal_count,表示要调整薪资的员工的个数，查询指定id部门的员工信息，按照 salary 升序排列，根据 hire_date 的情况，调整前 change_sal_count 个员工的薪资，详情如下：

• hire_date < 1995 ——> salary = salary * 1.2
• hire_date >= 1995 and hire_date <= 1998 ——> salary = salary * 1.5
• hire_date > 1998 and hire_date <= 2001 ——> salary = salary * 1.10
• hire_date > 2001 ——> salary = salary * 1.05

---

先查询表，用于对比是否成功

SELECT employee_id,hire_date,salary
FROM employees
WHERE department_id = 50
ORDER BY salary ASC;

DELIMITER //
CREATE PROCEDURE update_salary(IN dept_id INT, IN change_sal_count INT)
BEGIN
      -- 声明局部变量,必须在begin...end最开始位置
      DECLARE emp_id INT; -- 记录员工id
      DECLARE emp_hire_date DATE; -- 记录员工入职时间
      DECLARE init_count INT DEFAULT 1; -- 表示循环结构,必须设定defaulte 默认初始值 不然为null,
      DECLARE add_sal_rate DOUBLE ; -- 记录涨薪的比例
      
      -- 1.声明游标(结果集)
      DECLARE emp_cursor CURSOR FOR 
      SELECT employee_id,hire_date FROM employees
      WHERE department_id = dept_id 
      ORDER BY salary ASC ;-- asc 升序排序 
      
      -- 2.打开游标
      OPEN emp_cursor;
      
      -- 循环使用游标
      WHILE init_count <= change_sal_count DO 
      -- 3. 使用游标(取出结果集中的数据,赋值到 变量中)
      FETCH emp_cursor INTO emp_id,emp_hire_date;  -- 尽量数据类型一致,防止存储错误,以及截断数据丢失
                                               -- 需要一一对应上,
      IF YEAR(emp_hire_date) < 1995
         THEN SET add_sal_rate = 1.2;
      ELSEIF YEAR(emp_hire_date) <= 1998 
         THEN SET add_sal_rate = 1.5;
      ELSEIF YEAR(emp_hire_date) <= 2001
         THEN SET add_sal_rate = 1.10;
      ELSE 
         SET add_sal_rate = 1.05;
      END IF ; -- if 作用范围,结束    
      
      -- 涨薪操作
      UPDATE employees
      SET salary = salary * add_sal_rate
      WHERE employee_id = emp_id;  
      
      SET init_count = init_count + 1;
      
      END WHILE; -- while 作用范围,结束
      
      -- 4.关闭游标
      CLOSE emp_cursor;

END //
DELIMITER ;

调用存储过程，并查看验证结果

-- 调用存储过程
CALL update_salary(50,3);

SELECT employee_id,hire_date,salary
FROM employees
WHERE department_id = 50
ORDER BY salary ASC;

---

4. 小结

游标是 MySQL 的一个重要的功能，为逐条读取结果集中的数据，提供了完美的解决方案。

跟应用层面，实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。但同时也会带来一些性能问题，比如:在使用游标的过程中，会对数据行进行 加锁，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会消耗系统资源，造成内存不足，这是因为游标是在内存中进行的处理。

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

5. 最后:

限于自身水平，其中存在的错误，希望大家可以给予指教，韩信点兵——多多益善，谢谢大家，后会有期，江湖再见 ！！！

---