[SQL必知必会学习] 12 游标

 

什么是游标？

在数据库中，游标提供了一种灵活的操作方式， 可以让我们从数据结果集中每次提取一条数据记录进行操作。 游标让 SQL 这种面向集合的语言有了面向过程开发的能力。
在 SQL 中， 游标是一种临时的数据库对象， 可以指向存储在数据库表中的数据行指针。 这里游标充当了指针的作用， 我们可以通过操作游标来对数据行进行操作。

使用游标

一般需要经历五个步骤。 不同 DBMS 中， 使用游标的语法可能略有不同。

第一步， 定义游标。

DECLARE cursor_name CURSOR FOR select_statement

适用于 MySQL，SQL Server，DB2 和 MariaDB。
Oracle 或者PostgreSQL， 需要写成：

DECLARE cur_hero CURSOR FOR 
    SELECT hp_max FROM heros;

第二步， 打开游标。

OPEN cursor_name

使用游标， 必须先打开游标。 打开游标的时候 SELECT 语句的查询结果集就会送到游标工作区。

第三步， 从游标中取得数据。

FETCH cursor_name INTO var_name ...

cursor_name 这个游标来读取当前行， 并且将数据保存到 var_name 这个变量中， 游标指针指到下一行。
⾏
第四步，关闭游标。

CLOSE cursor_name

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

最后一步， 释放游标。

DEALLOCATE cursor_namec 

使用游标来扫描 heros 数据表中的数据行， 然后累计最大生命值

DELIMITER //
CREATE PROCEDURE `calc_hp_max`()
BEGIN
    -- 创建接收游标的变量
    DECLARE hp INT;
    -- 创建总数变量
    DECLARE hp_sum INT DEFAULT 0;
    -- 第一步， 定义游标。
    DECLARE cur_hero CURSOR FOR SELECT hp_max FROM heros;
    -- 第二步， 打开游标。
    OPEN cur_hero;
    read_loop:LOOP
    -- 第三步， 从游标中取得数据。
    FETCH cur_hero INTO hp;
    SET hp_sum = hp_sum + hp;
    END LOOP;
    -- 第四步，关闭游标。
    CLOSE cur_hero;
    SELECT hp_sum;

END//
DELIMITER ;

使用游标01.png

系统会提示 1329 错误， 也就是在 LOOP 中当游标没有取到数据时会报的错误。当游标指向到最后一行数据后继续执行会游标溢出报错误，可以通过定义一个 continue 的事件， 指定这个事件发生时修改变量done 的值， 以此来判断游标是否已经溢出

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;  

完善代码为：

DELIMITER //
CREATE PROCEDURE `calc_hp_max`()
BEGIN
    -- 创建接收游标的变量
    DECLARE hp INT;
    -- 创建总数变量
    DECLARE hp_sum INT DEFAULT 0;
    -- 创建结束标志变量
    DECLARE done INT DEFAULT false;
    -- 第一步， 定义游标。
    DECLARE cur_hero CURSOR FOR SELECT hp_max FROM heros;
    -- 指定游标循环结束时的返回值
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
    -- 第二步， 打开游标。
    OPEN cur_hero;
    read_loop:LOOP
    -- 第三步， 从游标中取得数据。
    FETCH cur_hero INTO hp;
    -- 判断游标的循环是否结束
    IF done THEN
        LEAVE read_loop;
    END IF; 
    SET hp_sum = hp_sum + hp;
    END LOOP;
    -- 第四步，关闭游标。
    CLOSE cur_hero;
    SELECT hp_sum;

END//
DELIMITER ;

使用游标02.png

游标.png

参考资料：

练习数据库使用 SQL必知必会专栏（极客时间）中的作者提供的 王者荣耀数据库以及NBA数据库
练习系统 MySQL Server version: 5.7.26-0ubuntu0.16.04.1 (Ubuntu)

SQL必知必会专栏（极客时间）链接：
http://gk.link/a/103Sm

《MySQL必知必会》学习笔记（24-29）:

https://www.jianshu.com/p/ee15e71ab1b8

此篇内容:使用游标、使用触发器、管理事务处理、全球化和本地化、安全管理、数据库维护

---

GitHub链接：
https://github.com/lichangke/LeetCode

知乎个人首页：
https://www.zhihu.com/people/lichangke/

简书个人首页：
https://www.jianshu.com/u/3e95c7555dc7

个人Blog:
https://lichangke.github.io/

欢迎大家来一起交流学习