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

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

---

– 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,

【一线大厂Java面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义】

浏览器打开：qq.cn.hn/FTf 免费领取

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;