mysql 存储过程按日期循环插入

红太狼 2022-01-19 04:47 401阅读 0赞

参考博客

[https://blog.csdn.net/qiao\_198911/article/details/40348747][https_blog.csdn.net_qiao_198911_article_details_40348747]

[https://www.cnblogs.com/william-lin/archive/2013/03/18/2966431.html][https_www.cnblogs.com_william-lin_archive_2013_03_18_2966431.html]

定义与使用变量时需要注意以下几点：   
  
1、 DECLARE语句必须用在DEGIN…END语句块中，并且必须出现在DEGIN…END语句块的最前面，即出现在其他语句之前。   
  
2、DECLARE定义的变量的作用范围仅限于DECLARE语句所在的DEGIN…END块内及嵌套在该块内的其他DEGIN…END块。   
  
3、存储过程中的变量名不区分大小写。

SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Procedure structure for insert_stock_n_one
    -- ----------------------------
    DROP PROCEDURE IF EXISTS `insert_stock_n_one`;
    DELIMITER ;;
    CREATE DEFINER=`root`@`%` PROCEDURE `insert_stock_n_one`()
    BEGIN
     -- 定义开始循环时间变量
     DECLARE nowdate date DEFAULT NOW();
    -- 结束时间循环变量
     DECLARE endtmp  date DEFAULT NOW();
    	-- 设置存储过程名字
    	SET @procedure_name = 'insert_stock_n_one';
    
    	-- 存储过程开始运行日志
    	CALL sp_log(@procedure_name, 'xxx报表插入', 0, 0, NULL, 'START', @LogId);
    
    	-- 打开SQL批量更新开关
    	SET sql_safe_updates=0;
    
    	-- 取出上次交易处理的最大时间戳
    	select max(max_timestamp) into @maxTimestamp from wx_mini_procedure_exec_log
    	where 1=1
    	  and procedure_name = @procedure_name;
    
    	-- 0. 参数设置
    	SET @endDate = curdate();
    
    	-- 1. 删除原表
    	truncate table wx_mini_report_product_stock_n;
    
    	-- 2. 插入截止到今天凌晨的数据
    	-- select * from wx_mini_report_product_stock_n;
    
    -- 查询最小时间 开始时间
    select DATE_FORMAT(min(create_time),'%Y%m%d') into nowdate from wx_mini_product_stock;
    
    
    select DATE_FORMAT( max(create_time),'%Y%m%d') into endtmp from wx_mini_product_stock;
    
    
    
    -- set nowdate = DATE_FORMAT(beginDate,'%Y%m%d');
    -- set endtmp = DATE_FORMAT(endDate,'%Y%m%d');
    WHILE nowdate <= endtmp 
    DO
    
    
    	INSERT INTO wx_mini_report_product_stock_n (stockId, stockDetailId, regionId, regionName,
    												provinceId, provinceName, cityId, cityName, shopId, shopCode, shopName, pcId, pcCode, pcName, pcsId, pcsCode, pcsName, pclId, pchId,
    												custGroupName, reportMonth, reportDate, createTime, pcType, alarInfo, ProductName,
    												ProductNum, proNumPrice, ProductPrice, SixMonthExpNum, ProductPriceSixMonth, proalarInfo)
    	select
    sd.stock_id stockjoinid,
    		sd.id  stockDetailId,
    		
    		r.id regionId,
    		r.name regionName,
    		p.id provinceId,
    		p.name provinceName,
    		c.id cityId,
    		c.name cityName,
    		s.report_shop_id as shopId,
    		si.venderShopId as shopCode,
    		si.shopName as shopName,
    		s.promoter_id as pcId,
    		pa.account as pcCode,
    		pa.realName as pcName,
    
    		pa.parent_promoter_id as pcsId,
    		ppa.account as pcsCode,
    		ppa.realName as pcsName,
    		ppa.parent_promoter_id as pclId,
    		pcl.parent_promoter_id as pchId,
    		si.custgroup as custGroupName,
    		s.title as reportMonth,
    		s.report_date as reportDate,
    		NOW(),
    		pp.pc_type as pcType,
    		s.alar_info,
    		pd.name productName,
    		IFNULL(sd.product_num ,0) productNum,
    		IFNULL(sd.product_num * pd.sale_price ,0) proNumPrice,
    
    		IFNULL(pd.sale_price ,0) productPrice,
    		IFNULL(sd.six_month_exp_num ,0) sixMonthExpNum,
    
    		IFNULL( sd.six_month_exp_num * pd.sale_price  ,0) ProductPriceSixMonth,
    	sd.alar_info alarInfo  
    	from
    
    		shop_info si
    			left join product pd on 1=1 
    			  join stock s on s.report_shop_id=si.id
    			left join stock_detail sd on sd.product_id=pd.id and sd.stock_id=s.id
    
    			left	join account pa on s.promoter_id = pa.promoter_id
    			left join profile pp on pa.promoter_profile_id = pp.id
    
    			left join region p on pp.province_id = p.id
    			left join region c on pp.city_id = c.id
    			left join region r on pp.region_id = r.id
    			left join account ppa on pa.parent_promoter_id = ppa.promoter_id
    			left join account pcl on ppa.parent_promoter_id = pcl.promoter_id
    	where    date(s.create_time) = nowdate 
    	order by sd.id desc;
    
    -- 给日期+1天
    set nowdate = DATE_ADD(nowdate,INTERVAL 1 DAY);
    
    
    END WHILE;
     
     -- 循环结束
    
    
    	-- 3. 更新最大的时间戳 总数
    	select
    		@endDate, count(1) into @max_timestamp_new,@sql_update_count
    	from stock s
    	where 1=1
    	;
    
    
    	-- 5.存储过程开始运行日志
    	IF @max_timestamp_new IS NULL THEN
    		SET @max_timestamp_new = @maxTimestamp;
    	END IF;
    
    
    	-- 关闭SQL批量更新开关
    	SET sql_safe_updates=1;
    
    	-- 更新日志
    	CALL sp_log(@procedure_name, 'xxx报表插入成功', @sql_update_count, @sql_update_count, @max_timestamp_new, 'END', @LogId);
    END
    ;;
    DELIMITER ;

[https_blog.csdn.net_qiao_198911_article_details_40348747]: https://blog.csdn.net/qiao_198911/article/details/40348747
[https_www.cnblogs.com_william-lin_archive_2013_03_18_2966431.html]: https://www.cnblogs.com/william-lin/archive/2013/03/18/2966431.html