MySQL学习-游标

圆之蓝调
89 阅读3分钟

携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第23天，点击查看活动详情

前言

上篇我们学习了MySQL中的流程控制-REPEAT、LEAVE与ITREATE语句。有兴趣的小伙伴可以阅读(# MySQL学习-流程控制-REPEAT、LEAVE与ITREATE语句)。
下面我们学习MySQL中的游标。

游标

定义

前面学习的语句，得到的结果集中无法向前，向后或者随意定位一条记录，并对记录的数据进行处理。这个时候，就可以用到游标。游标提供了一种灵活的操作方式，可以对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。游标让SQL这种面向集合的语言有了面向过程开发的能力。

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

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

使用游标

游标必须在声明处理程序之前被声明，并且变量和条件还必须在声明游标或处理程序之前被声明。如果想要使用游标，一般分四个步骤。

  1. 声明游标。
    在MySQL中，使用DECLARE关键字来声明游标，语法如下：
DECLARE cursor_name CURSOR FRO select_statement;

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

举例一

DECLARE cur_emp CURSOR FOR
SELECT employee_id FROM employees;
  1. 打开游标
    语法如下：
OPEN cursor_name

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

  1. 使用游标（获取数据）
    语法如下：
FETCH cursor_name INTO var_name [, var_name] ...

使用cursor_name这个游标来读取当前行，并且将数据保存到cursor_name这个变量中，游标指针直到下一行。如果游标读取的数据行有多个列名，则在INTO关键字后面复制给多个变量名即可。注意：var_name必须在声明游标之前就定义好。游标的查询结果集中的字段数，必须要跟INTO后面的变量数一致，否则，在存储过程执行的时候，MySQL会提示错误。

  1. 关闭游标
    语法如下：
CLOSE cursor_name;

有打开就有关闭。因为游标会占用系统资源，如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率。关闭游标之后，就不能检索查询结果中的数据行，如果需要检索只能再次打开游标。

举例二

创建存储过程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 ;

总结

游标为逐条读取结果集中的数据，提供了完美的解决方案。游标可以在存储程序中使用，效率高，程序也更加简洁。但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据进行加锁，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会消耗系统资源，造成内存不足，这是因为游标是在内存中进行的处理。建议，养成用完之后就关闭游标的习惯，这样才能提高系统的整体效率。

今天先学习到这里，明天继续。

avatar
文章
阅读
粉丝