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

最新推荐文章于 2024-11-13 16:46:51 发布

adsadagaf

最新推荐文章于 2024-11-13 16:46:51 发布

阅读量362

点赞数 1

分类专栏： MySQL 文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/qq_38762283/article/details/104984130

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

使用navicat创建存储过程

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