MySQL存储过程创建实例,双循环结果集并定时执行

BEGIN
    #Routine body goes here...
            DECLARE startTime  DATETIME  default date_sub(now(),interval 60 MINUTE);#起始时间 当前时间前一小时
            DECLARE endTime  DATETIME  default NOW(); #结束时间 当前时间
    
            declare start_num  int;  
            declare end_num  int;  
            #声明结束标识
            DECLARE end_flag int DEFAULT 0;

            #定义变量 s 游标坐标 
            DECLARE s int DEFAULT 0;
            DECLARE scollector_id int;
            DECLARE skwHd DOUBLE;
            DECLARE skVarHd DOUBLE;
            DECLARE scollect_time datetime;
            DECLARE ecollector_id int;
            DECLARE ekwHd DOUBLE;
            DECLARE ekVarHd DOUBLE;
            DECLARE ecollect_time datetime;

            #声明游标 start_curosr 
            DECLARE start_curosr CURSOR FOR select t.id as collector_id,t1.EPS1+t1.EPS2 as kwhd,t1.EQS1+t1.EQS2 as kVarhd,t1.collect_time from tb_collector_info t LEFT JOIN tb_electric_power_info t1 ON t.id = t1.collector_id 
                    where t.id not in (SELECT collector_id FROM tb_kwh_info WHERE start_time = startTime and end_time = endTime) 
                    AND ABS(TIMESTAMPDIFF(MINUTE,t1.collect_time,startTime)) <= 4 order by collector_id;
    

            #声明游标 end_curosr 
            DECLARE end_curosr CURSOR FOR select t.id as collector_id,t1.EPS1+t1.EPS2 as kwhd,t1.EQS1+t1.EQS2 as kVarhd,t1.collect_time from tb_collector_info t LEFT JOIN tb_electric_power_info t1 ON t.id = t1.collector_id 
                    where t.id not in (SELECT collector_id FROM tb_kwh_info WHERE start_time = startTime and end_time = endTime) 
                    AND ABS(TIMESTAMPDIFF(MINUTE,t1.collect_time,endTime)) <= 4 order by collector_id;
    
            #设置终止标志
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_flag=1;

            #将结果集大小放入start_num
            select count(*) into start_num from(select t.id as collector_id,t1.EPS1+t1.EPS2 as kwhd,t1.EQS1+t1.EQS2 as kVarhd,t1.collect_time from tb_collector_info t LEFT JOIN tb_electric_power_info t1 ON t.id = t1.collector_id 
                    where t.id not in (SELECT collector_id FROM tb_kwh_info WHERE start_time = startTime and end_time = endTime) 
                    AND ABS(TIMESTAMPDIFF(MINUTE,t1.collect_time,startTime)) <= 4 order by collector_id) as t2;

            #将结果集大小放入end_num 
            select count(*) into end_num from(select t.id as collector_id,t1.EPS1+t1.EPS2 as kwhd,t1.EQS1+t1.EQS2 as kVarhd,t1.collect_time from tb_collector_info t LEFT JOIN tb_electric_power_info t1 ON t.id = t1.collector_id 
                    where t.id not in (SELECT collector_id FROM tb_kwh_info WHERE start_time = startTime and end_time = endTime) 
                    AND ABS(TIMESTAMPDIFF(MINUTE,t1.collect_time,endTime)) <= 4 order by collector_id) as t2;

            
            #当两个都不为空时进行操作
            if start_num>0 and end_num>0 THEN
                #打开游标
                OPEN start_curosr;
                    -- 第一个游标循环
                    out_loop:LOOP
                        FETCH start_curosr INTO scollector_id,skwHd,skVarHd,scollect_time;
                        IF end_flag = 1 THEN 
                            LEAVE  out_loop;
                        END IF;
                        OPEN end_curosr;
                            -- 第二个游标循环
                            inner_loop:LOOP
                            FETCH end_curosr INTO ecollector_id,ekwHd,ekVarHd,ecollect_time;
                            IF end_flag = 1 THEN 
                                LEAVE  inner_loop;
                            END IF;
                            IF  scollector_id=ecollector_id then
                                INSERT INTO tb_kwh_info(collector_id,start_time,end_time,kWh,kVarh,type) VALUES(scollector_id,scollect_time,ecollect_time,ekwHd-skwHd,ekVarHd-skVarHd,1);
                            END IF;
                            END LOOP inner_loop;
                        CLOSE end_curosr;
                    -- 注意这里，停止循环标志
                    SET end_flag=0;
                    END LOOP out_loop;
                CLOSE start_curosr;
            end if;

END