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