游标
游标是一个重要的概念,提供了一种灵活的操作方式,可以从数据结果集中每次提取一条数据记录进行操作。
在SQL中,游标是一种临时的数据库对象,可以指向存储在数据库中的数据行指针。
示例
-查询heros数据表中最大生命值大于8500的英雄有哪些
SELECT id, name, hp_max FROM heros WHERE hp_max > 8500
查询结果
如何使用游标
游标实际上是一种控制数据集的更加灵活的处理方式
使用游标,一般需要5个步骤:
第一步:定义游标
DECLARE cursor_name CURSOR FOR select_statement
语法适用于MySQL,SQL Server,DB2
Oracle或者PostgreSQL需要写成
DECLARE cursor_name CURSOR IS select_statement
注:select_statement代表的是SELECT语句
第二步:打开游标
OPEN cursor_name
当定义好游标之后,想要使用游标,必须先打开游标。打开游标的时候SELECT语句的查询结果集就会送到游标工作区。
第三步:从游标中取得数据
FETCH cursor_name INTO var_name ...
这句的作用是使用cursor_name这个游标来读取当前行,并且将数据保存到var_name这个变量中,游标指针直到下一行。
第四步:关闭游标
CLOSE cursor_name
有OPEN就会有CLOSE,也就是打开和关闭游标。
第五步:释放游标
DEALLOCATE cursor_namec
有SECLARE就需要有DEALLOCATE,DEALLOCATE的作用是释放游标。
游标实战
实例
- 用游标来扫描heros数据表中的数据行,然后累计最大生命值
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
执行call calc_hp_max()的时候系统会提示1329错误,也就是在LOOP中当游标没有取到数据时会报的错误。
当游标溢出时(也就是当游标指向最后一行数据后继续执行会报的错误)。可以定义continue的事件,指定这个事件发生时修改变量done的值,以此来判断游标是否已经溢出。
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
同时在循环中加上对done的判断,如果游标的循环已经结束,就需要跳出read_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;END
运行结果
除了LOOP循环以外,还可以使用REPEAT...UNTIL...以及WHILE循环。
总结