mysql存储过程while 遍历游标
时间:2019-02-18 12:56 来源:linux.it.net.cn 作者:IT
DELIMITER $$
CREATE PROCEDURE queryEduTopologyPerformance(IN edu_id VARCHAR(50),OUT ebu_number_total INT ,
OUT upstream_total INT,OUT downstream_total INT,OUT alarm_number_total INT)
BEGIN
-- 定义变量
DECLARE ebuId VARCHAR(50); -- 游标接收变量
DECLARE nowTime DATETIME; -- 当前时间
DECLARE beforeTwoMinuteTime DATETIME; -- 两分钟前的时间
DECLARE upstreamTemp INT; -- ebu上行流量临时变量
DECLARE downstreamTemp INT; -- ebu下行流量临时变量
DECLARE alarmTemp INT; -- ebu告警临时变量
DECLARE i INT DEFAULT 0;
-- 遍历数据结束标志
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT ebu.id FROM cap_ebu ebu,cap_edu edu WHERE ebu.edu_id = edu.id AND edu.id = edu_id;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 为变量赋值
SET ebu_number_total = 0;
SET upstream_total = 0;
SET downstream_total = 0;
SET alarm_number_total = 0;
SET upstreamTemp = 0;
SET downstreamTemp = 0;
SET alarmTemp = 0;
SET nowTime = NOW();
SET beforeTwoMinuteTime = DATE_SUB(nowTime, INTERVAL 1 MINUTE);
SELECT COUNT(*) INTO ebu_number_total FROM cap_ebu ebu,cap_edu edu WHERE ebu.edu_id = edu.id AND edu.id = edu_id;
OPEN cur;
loop1: WHILE i<ebu_number_total DO -- 遍历开始
FETCH cur INTO ebuId; -- 获取游标里的ebuId值
-- 重置变量值
SET i = i + 1;
SET upstreamTemp = 0 ;
SET downstreamTemp = 0;
SET alarmTemp = 0 ;
-- 计算总流量,总告警数
SELECT upstream INTO upstreamTemp FROM ebu_stream_view WHERE TIME >=beforeTwoMinuteTime AND TIME <=nowTime AND id = ebuId ORDER BY TIME DESC LIMIT 0,1;
SELECT downstream INTO downstreamTemp FROM ebu_stream_view WHERE TIME >=beforeTwoMinuteTime AND TIME <=nowTime AND id = ebuId ORDER BY TIME DESC LIMIT 0,1;
SET upstream_total = upstream_total + upstreamTemp;
SET downstream_total = downstream_total + downstreamTemp;
SELECT alarmNumber INTO alarmTemp FROM current_alarm WHERE last_update_time >=beforeTwoMinuteTime AND last_update_time <=nowTime AND device_id = ebuId ORDER BY last_update_time DESC,ceate_time DESC LIMIT 0,1;
SET alarm_number_total = alarm_number_total + alarmTemp;
END WHILE loop1;
CLOSE cur;
END $$
DELIMITER ;
(责任编辑：IT)
| DELIMITER $$ CREATE PROCEDURE queryEduTopologyPerformance(IN edu_id VARCHAR(50),OUT ebu_number_total INT , OUT upstream_total INT,OUT downstream_total INT,OUT alarm_number_total INT) BEGIN -- 定义变量 DECLARE ebuId VARCHAR(50); -- 游标接收变量 DECLARE nowTime DATETIME; -- 当前时间 DECLARE beforeTwoMinuteTime DATETIME; -- 两分钟前的时间 DECLARE upstreamTemp INT; -- ebu上行流量临时变量 DECLARE downstreamTemp INT; -- ebu下行流量临时变量 DECLARE alarmTemp INT; -- ebu告警临时变量 DECLARE i INT DEFAULT 0; -- 遍历数据结束标志 DECLARE done INT DEFAULT FALSE; DECLARE cur CURSOR FOR SELECT ebu.id FROM cap_ebu ebu,cap_edu edu WHERE ebu.edu_id = edu.id AND edu.id = edu_id; -- 将结束标志绑定到游标 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 为变量赋值 SET ebu_number_total = 0; SET upstream_total = 0; SET downstream_total = 0; SET alarm_number_total = 0; SET upstreamTemp = 0; SET downstreamTemp = 0; SET alarmTemp = 0; SET nowTime = NOW(); SET beforeTwoMinuteTime = DATE_SUB(nowTime, INTERVAL 1 MINUTE); SELECT COUNT(*) INTO ebu_number_total FROM cap_ebu ebu,cap_edu edu WHERE ebu.edu_id = edu.id AND edu.id = edu_id; OPEN cur; loop1: WHILE i<ebu_number_total DO -- 遍历开始 FETCH cur INTO ebuId; -- 获取游标里的ebuId值 -- 重置变量值 SET i = i + 1; SET upstreamTemp = 0 ; SET downstreamTemp = 0; SET alarmTemp = 0 ; -- 计算总流量,总告警数 SELECT upstream INTO upstreamTemp FROM ebu_stream_view WHERE TIME >=beforeTwoMinuteTime AND TIME <=nowTime AND id = ebuId ORDER BY TIME DESC LIMIT 0,1; SELECT downstream INTO downstreamTemp FROM ebu_stream_view WHERE TIME >=beforeTwoMinuteTime AND TIME <=nowTime AND id = ebuId ORDER BY TIME DESC LIMIT 0,1; SET upstream_total = upstream_total + upstreamTemp; SET downstream_total = downstream_total + downstreamTemp; SELECT alarmNumber INTO alarmTemp FROM current_alarm WHERE last_update_time >=beforeTwoMinuteTime AND last_update_time <=nowTime AND device_id = ebuId ORDER BY last_update_time DESC,ceate_time DESC LIMIT 0,1; SET alarm_number_total = alarm_number_total + alarmTemp; END WHILE loop1; CLOSE cur; END $$ DELIMITER ; (责任编辑：IT) |