近些碰到一个业务需要处理结果集,因此需要使用游标.用于统计策略的流量值.直接上栗子说明游标用法.
先看strategy表中数据
一共10条.
通过游标遍历统计流量
CREATE DEFINER=`root`@`localhost` PROCEDURE `sum_interface_traffic`()
BEGIN
declare sum_in_traffic float;
declare sum_out_traffic float;
declare strategy_id int;
declare app_ids varchar(300);
declare done INT default 0;
#涉及到主键,必须用别名,否则fetch不到值
declare cur cursor for select s.id, s.app_ids from strategy s;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
/**
要是需要频繁的调用该存储过程,可以将
drop temporary table if exists sum_strategy_interface_traffic;注释
改为truncate table sum_strategy_interface_traffic;避免临时表的频繁创建
**/
drop temporary table if exists sum_strategy_interface_traffic;
create temporary table sum_strategy_interface_traffic(
strategy_id int unsigned,
sum_in_traffic float unsigned,
sum_out_traffic float unsigned
)default charset utf8 collate utf8_general_ci engine InnoDB;
open cur;#打开游标
re:repeat
FETCH cur INTO strategy_id, app_ids;
##结束循环
if done then
leave re;
end if;
call split(app_ids);
select sum(sit.sum_in_traffic) from sum_interface_traffic_by_app_id sit where sit.app_id in (select * from tmp_strs) into sum_in_traffic;
select sum(sit.sum_out_traffic) from sum_interface_traffic_by_app_id sit where sit.app_id in (select * from tmp_strs) into sum_out_traffic;
insert into sum_strategy_interface_traffic values(strategy_id,sum_in_traffic,sum_out_traffic);
until done end repeat;
close cur;#关闭游标
END
运行结果;
PS:在开发种会碰到遍历结果集提前退出的场景,比如
有些存储过程要是涉及select XXX into .. (其中XXX涉及到数据库的查询,哪怕是调用其他的存储过程或者函数),要是碰到查询为空的情况会导致循环退出
在上面例子上稍稍修改说明:
CREATE DEFINER=`root`@`localhost` PROCEDURE `sum_interface_traffic`()
BEGIN
declare sum_in_traffic float;
declare sum_out_traffic float;
declare strategy_id int;
declare app_ids varchar(300);
declare done INT default 0;
declare test varchar(15);
#涉及到主键,必须用别名,否则fetch不到值
declare cur cursor for select s.id, s.app_ids from strategy s;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
/**
要是需要频繁的调用该存储过程,可以将
drop temporary table if exists sum_strategy_interface_traffic;注释
改为truncate table sum_strategy_interface_traffic;避免临时表的频繁创建
**/
drop temporary table if exists sum_strategy_interface_traffic;
create temporary table sum_strategy_interface_traffic(
strategy_id int unsigned,
sum_in_traffic float unsigned,
sum_out_traffic float unsigned
)default charset utf8 collate utf8_general_ci engine InnoDB;
open cur;#打开游标
re:repeat
FETCH cur INTO strategy_id, app_ids;
##结束循环
if done then
leave re;
end if;
call split(app_ids);
select sum(sit.sum_in_traffic) from sum_interface_traffic_by_app_id sit where sit.app_id in (select * from tmp_strs) into sum_in_traffic;
select sum(sit.sum_out_traffic) from sum_interface_traffic_by_app_id sit where sit.app_id in (select * from tmp_strs) into sum_out_traffic;
insert into sum_strategy_interface_traffic values(strategy_id,sum_in_traffic,sum_out_traffic);
/***仅仅演示游标提前退出
当查询到第7条数据的时候,app_ids字符串为null,所以临时表tmp_strs是为空
select * from tmp_strs limit 1 into test;导致循环提前退出
**/
select * from tmp_strs limit 1 into test;
until done end repeat;
close cur;#关闭游标
END
看效果;
只有7条数据.
解决办法,我在一篇技术博客看到有人提供了三种解决方案.我这只提供其中一种简单明了的:
在select * from tmp_strs limit 1 into test;后面添加处理逻辑
部分代码如下
/***仅仅演示游标提前退出
当查询到第7条数据的时候,app_ids字符串为null,所以临时表tmp_strs是为空
select * from tmp_strs limit 1 into test;导致循环提前退出
**/
select * from tmp_strs limit 1 into test;
##防止循环提前结束
set done = 0;