在编写SQL语句的时候通常是面向集合进行思考，这种思考方式更让我们关注结果集的特征，而不是具体的实现过程。
面向集合的思考方式，关注“获取什么”，而不是“如何获取”，这也可以说是SQL与传统编程最大的区别之一，因为SQL本身是以关系模型和集合论为基础的。
也有一些情况，不需要对查询结果集中的所有数据行都采用相同的处理方式，需要每次处理一行或者一部分行，这时就需要面向过程的编程方法了。游标就是这种编程方式的体现。
1.游标
游标提供了一种灵活的操作方式，可以让我们从数据结果集中每次提取一条数据记录进行操作。游标让SQL这种面向集合的语言有了面向过程开发的能力。可以说，游标是面向过程的编程方式，这与面向集合的编程方式有所不同。
在SQL中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标充当了指针的作用，可以通过操作游标来对数据行进行操作。
2.使用游标
- step1.定义游标
DECLARE cursor_name CURSOR FOR select_statement
- step2.打开游标
打开游标的时候SELECT语句的查询结果集就会送到游标工作区。
OPEN cursor_name
- step3.从游标中取得数据
这句的作用是使用cursor_name这个游标来读取当前行，并且将数据保存到var_name这个变量中，游标指针指到下一行。如果游标读取的数据行有多个列名，则在INTO关键字后面赋值给多个变量名即可。
FETCH cursor_name INTO var_name ...
- step4.关闭游标
关闭游标之后，就不能再检索查询结果中的数据行，如果需要检索只能再次打开游标。
CLOSE cursor_name
- step5.释放游标
DEALLOCATE的作用是释放游标。一定要养成释放游标的习惯，否则游标会一直存在于内存中，直到进程结束后才会自动释放。当你不需要使用游标的时候，释放游标可以减少资源浪费。
DEALLOCATE cursor_namec
2.1 示例
用游标来扫描heros数据表中的数据行，然后累计最大生命值。
创建一个存储过程calc_hp_max，然后在存储过程中定义游标cur_hero，使用FETCH获取每一行的具体数值，然后赋值给变量hp，再用变量hp_sum做累加求和，最后再输出hp_sum：
mysql> DELIMITER //
mysql> 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;
->
-> 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 //
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql> call calc_hp_max();
ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
在LOOP中当游标没有取到数据时会报错误（也就是当游标指向到最后一行数据后继续执行会报的错误）。
修正方法：
当游标溢出时，可以定义一个continue的事件，指定这个事件发生时修改变量done的值，以此来判断游标是否已经溢出：
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
修改后完整代码如下：
mysql> DELIMITER //
mysql> 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 //
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql> call calc_hp_max();
+--------+
| hp_sum |
+--------+
| 454053 |
+--------+
1 row in set (0.00 sec)
3.使用游标来解决一些常见的问题
当需要处理一些复杂的数据行计算的时候，游标就会起到作用了。
针对heros数据表，假设我们想要对英雄的物攻成长（对应attack_growth）进行升级，在新版本中大范围提升英雄的物攻成长数值，但是针对不同的英雄情况，提升的幅度也不同，具体提升的方式如下。
- 这个英雄原有的物攻成长小于5
1）如果物攻成长的提升空间（即最高物攻attack_max-初始物攻attack_start）大于200，那么在原有的基础上提升10%
2）如果物攻成长的提升空间在150到200之间，则提升8%
3）如果物攻成长的提升空间不足150，则提升7% - 如果原有英雄的物攻成长在5—10之间
那么将在原有基础上提升5% - 如果原有英雄的物攻成长大于10
则保持不变
CREATE PROCEDURE `alter_attack_growth`()
BEGIN
-- 创建接收游标的变量
DECLARE temp_id INT;
DECLARE temp_growth, temp_max, temp_start, temp_diff FLOAT;
-- 创建结束标志变量
DECLARE done INT DEFAULT false;
-- 定义游标
DECLARE cur_hero CURSOR FOR SELECT id, attack_growth, attack_max, attack_start FROM heros;
-- 指定游标循环结束时的返回值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
OPEN cur_hero;
FETCH cur_hero INTO temp_id, temp_growth, temp_max, temp_start;
REPEAT
IF NOT done THEN
SET temp_diff = temp_max - temp_start;
IF temp_growth < 5 THEN
IF temp_diff > 200 THEN
SET temp_growth = temp_growth * 1.1;
ELSEIF temp_diff >= 150 AND temp_diff <=200 THEN
SET temp_growth = temp_growth * 1.08;
ELSEIF temp_diff < 150 THEN
SET temp_growth = temp_growth * 1.07;
END IF;
ELSEIF temp_growth >=5 AND temp_growth <=10 THEN
SET temp_growth = temp_growth * 1.05;
END IF;
UPDATE heros SET attack_growth = ROUND(temp_growth,3) WHERE id = temp_id;
END IF;
FETCH cur_hero INTO temp_id, temp_growth, temp_max, temp_start;
UNTIL done = true END REPEAT;
CLOSE cur_hero;
END
有一点需要注意的是，在对数据表进行更新前，需要备份之前的表。
4.总结
问题：
- 使用游标的过程中，会对数据行进行加锁，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会消耗系统资源，造成内存不足，这是因为游标是在内存中进行的处理。
网友评论