SQL从入门到放弃系列——游标[十三]

2019年7月18日22:14:28 发表评论 166
摘要

1.什么是游标?为什么要使用游标?
2.如何使用游标?
3.如何使用游标解决一些常见问题?

什么是游标

游标是数据库中的一个重要概念,它提供了一种灵活的操作方式,可以让我们从数据结果集中每次提取一条数据记录进行操作。游标让SQL这种面向集合的语言有了面向过程的开发能力。

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

如何使用游标

游标实际上是一种控制数据集的更加灵活的处理方式。

使用游标一般经过5个步骤,不同DBMS,使用游标的语法略微不同。

  • 定义游标
DECLARE cursor_name CURSOR FOR select_statement

这个语法适用于Mysql,SQL Server,DB2,MariaDB,如果是Oracle,或者PostgreSQL,需要写成:

DECLARE cursor_name CURSOR IS select_statement

要使用select语句获取结果集,select_statement代表select语句。

以Mysql举例:

DECLARE cur_hero CURSOR FOR 
	SELECT hp_max FROM heros;
  • 打开游标

OPEN cursor_name

OPEN cursor_name

当定义好游标后如果想使用游标,必须先打开游标,打开游标的时候select语句的查询结果集就会送到游标工作区。

  • 从游标中取得数据
FETCH cursor_name INTO var_name ...

这句的作用是使用cursor_name这个游标来读取当前行,并且将数据保存到var_name这个变量中,游标指针指向下一个游标。如果游标读取的数据行有多个列名,则在into关键字后赋值给多个变量名即可。

  • 关闭游标
CLOSE cursor_name

当使用完游标后需要关闭游标,关闭游标后就不能再检索查询结果中的数据行,如果需要检索只能再次打开游标。

  • 释放游标
DEALLOCATE PREPARE

一定要养成释放游标的习惯,否则游标会一直存在于内存中,知道进程结束后才会自动释放。释放游标可以减少资源浪费。

示例

使用游标扫描heros数据表中的数据行,然后累计最大生命值,怎么做呢?

先创建一个存储过程calc_hp_max,然后在存储过程中创建游标cur_hero,使用FETCH获取每一行的具体数值,然后赋值给变量hp,再用变量hp_sum做累加求和,最后输出hp_sum,如下:

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;
       DEALLOCATE PREPARE cur_hero;
END

此时执行会提示1329错误,原因是loop中当游标没有取到数据时会报错。

当游标溢出时(类似于脚标越界),可以定义一个continue事件,指定这个事件发生时修改变量done的值,以此判断是否溢出,即:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

同时在loop中添加对done的判断,如果游标的循环已结束就跳出loop:

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;
       DEALLOCATE PREPARE cur_hero;
END

使用游标解决一些常见问题

游标有什么用?

当需要一些复杂数据行计算的时候就需要用到游标了。比如,针对heros数据表,假设需要对英雄的物攻成长attack_growth进行升级,方案如下:

如果这个英雄原有的物攻成长小于 5,那么将在原有基础上提升7%-10%。如果物攻成长的提升空间(即最高物攻 attack_max- 初始物攻 attack_start)大于 200,那么在原有的基础上提升 10%;如果物攻成长的提升空间在150 到 200 之间,则提升 8%;如果物攻成长的提升空间不足 150,则提升 7%。

如果原有英雄的物攻成长在 5—10 之间,那么将在原有基础上提升 5%。如果原有英雄的物攻成长大于 10,则保持不变。以上所有的更新后的物攻成长数值,都需要保留小数点后 3 位。

针对上面这个情况,你自己可以用游标来完成转换,具体的代码如下:

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;
       DEALLOCATE PREPARE cur_hero;
END

 

总结

游标实际上是面向过程的思维方式,与面向集合的思维方式不同的地方在于游标更关注“如何执行”。可以通过游标更加精细、灵活地查询和管理想要的数据。

多表嵌套查询,join会非常消耗资源,使用游标会比较高效。

游标性能

好处:

灵活性强,可以解决复杂的数据处理问题,对数据进行逐行处理。

不足:

使用游标的过程中会对数据进行加锁,当业务并发量大的时候,会影响到业务的效率。同时游标是在内存中进行的处理,容易造成内存不足,消耗系统资源。

建议:

通常有游标替代方案时不用游标,实在绕不开还是需要使用游标。

思考

面向过程和面向集合这两种编程方式的区别?

(本文完)

  • 我的微信
  • 微信扫一扫
  • weinxin
  • 微信公众号
  • 微信公众号扫一扫
  • weinxin
  • A+
所属分类:SQL

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: