mysql 双层游标循环_mysql 双游标游标嵌套循环

BEGIN

declare tmp bigint default 0;

declare uploaddate,currenttime DATETIME;

declare freetraffic,sales,trafficpc,trafficapp,activity,calculate varchar(32);

declare recordid,calid,errorsInfo int(11);

-- 计算过程中的变量

declare gmv,taxsales,priceindex,margin,guestsinglequantity,nontaxcost,Basket,goodsprice,priceadjustment double;

declare ordernumber,totalsales int(11);

declare goodscode varchar(10);

-- 定义第一个游标 取未计算的记录遍历

declare cur CURSOR FOR

SELECT

id,upload_date,jd_free_traffic,jd_sales,paid_traffic_pc,paid_traffic_app,promotion_activity,data_calculate

FROM

ec_upload_across

WHERE

data_calculate ='no'

AND

upload_date < NOW()

ORDER BY

upload_date;

-- 定义第二个游标 取商品编号遍历

declare innrcur CURSOR FOR

SELECT

goods_code

FROM

ec_jd_sales

WHERE

date=uploaddate; -- 定义游标

declare continue handler for not found set tmp=1;

declare continue handler for sqlexception set errorsInfo =1;

OPEN cur; -- 打开游标

FETCH NEXT from cur INTO recordid,uploaddate,freetraffic,sales,trafficpc,trafficapp,activity,calculate; -- 游标向下走一步

WHILE(tmp=0)

DO

-- INSERT INTO ec_calculate_logs(start_time,record_id,deal_flag,status) values(NOW(),recordid,'N','N');-- 测试用

IF freetraffic ='yes' and freetraffic ='yes' and sales ='yes' and trafficpc ='yes' and trafficapp ='yes' and activity='yes'

THEN

START TRANSACTION;

set currenttime =NOW();

INSERT INTO ec_calculate_logs(start_time,record_id,deal_flag,status) values(currenttime,recordid,'N','N'); -- 写入ec_calculate_logs表中一条记录

select cal_id INTO calid from ec_calculate_logs where record_id=recordid AND start_time = currenttime; -- 生成的主键ID

-- 开始计算

OPEN innrcur; -- 打开游标

FETCH NEXT from innrcur INTO goodscode; -- 游标向下走一步

WHILE(tmp=0)

DO

-- 计算GMV、Margin

select tax_sales into taxsales from ec_jd_sales where date = uploaddate and goods_code=goodscode;

if taxsales IS NOT NULL

THEN

select COUNT(*) into priceindex from ec_promotion_activity where date = uploaddate and goods_code=goodscode;

if priceindex=0

THEN

SET priceindex=1.2;

ELSE

select price_index into priceindex from ec_promotion_activity where date = uploaddate and goods_code=goodscode;

if priceindex IS NULL

THEN SET priceindex=1.2;

end IF;

end IF;

-- 自定义测试异常

-- if goodscode = 1828002 THEN INSERT INTO ec_calculate_logs(start_time,record_id,deal_flag,status) values(currenttime,mx,'N','N'); end IF;

SET gmv=taxsales*priceindex;

update ec_jd_sales set GMV=gmv where date = uploaddate and goods_code=goodscode;

set margin=gmv-taxsales;

update ec_jd_sales set Margin=margin where date = uploaddate and goods_code=goodscode;

end IF;

-- 计算客单量

select COUNT(*) into ordernumber from ec_free_traffic where date = uploaddate and goods_code=goodscode;

select total_sales into totalsales from ec_jd_sales where date = uploaddate and goods_code=goodscode;

if ordernumber<>0

THEN

select order_number into ordernumber from ec_free_traffic where date = uploaddate and goods_code=goodscode;

if ordernumber IS NOT NULL OR ordernumber<>0

THEN

set guestsinglequantity=totalsales/ordernumber;

update ec_jd_sales set guest_single_quantity=guestsinglequantity where date = uploaddate and goods_code=goodscode;

-- 计算未税成本依赖于客单量

select non_tax_cost into nontaxcost from ec_jd_sales where date = uploaddate and goods_code=goodscode;

set Basket=guestsinglequantity*nontaxcost;

update ec_jd_sales set basket=Basket where date = uploaddate and goods_code=goodscode;

end IF;

end IF;

-- 计算京东(当天售价)调价

select COUNT(*) into priceadjustment from ec_promotion_activity where price_adjustment IS NULL AND date = uploaddate and goods_code=goodscode;

IF priceadjustment=1

THEN

select goods_price into goodsprice from ec_jd_sales where date = uploaddate and goods_code=goodscode;

update ec_promotion_activity set price_adjustment=goodsprice where date = uploaddate and goods_code=goodscode;

end IF;

FETCH NEXT from innrcur INTO goodscode;

END WHILE;

SET tmp=0;

CLOSE innrcur; -- 关闭游标

-- 更新相关表的状态

update ec_calculate_logs set end_time=NOW(),status='Y' where cal_id =calid;

UPDATE ec_upload_across SET data_calculate='yes' WHERE id=recordid;

update ec_calculate_logs set deal_flag='Y' where cal_id =calid;

-- 发生异常回滚，正常提交

if errorsInfo=1

THEN

ROLLBACK;

INSERT INTO ec_calculate_logs(start_time,record_id,deal_flag,status) values(currenttime,recordid,'N','N');

SET tmp=1;

ELSE

commit;

end IF;

ELSE

SET tmp=1;

END IF;

FETCH NEXT from cur INTO recordid,uploaddate,freetraffic,sales,trafficpc,trafficapp,activity,calculate;

END WHILE;

CLOSE cur; -- 关闭游标

END