MySQL定时任务：游标驱动的存储过程更新统计

drop procedure if exists test;
CREATE PROCEDURE test()
  Begin  
  declare adv_id int(11); /*定义属性*/

 declare Done int DEFAULT 0;/*判断循环是否关闭的标志位*/

/*定义游标 以及赋值*/
 declare result cursor for select offerslook_ad_id from api where `status`=1;
/*定义异常处理 */
 declare continue handler for not found set Done =1;
/*打开游标*/
 open  result ;

/*循环开始*/
 LOOP_LABLE:loop
  /*提前游标的值  多个值的时候： fetch xxx into xxx，xxx */
   fetch  result into adv_id; 
   /*声明结束的时候*/
   if Done=1 then leave LOOP_LABLE;end if;
 
update statistical_data,(SELECT * from (SELECT id,b.offerslook_ad_id,COUNT(*) as gOfferCount,COUNT(if(offerslook_offer_id is not null,true,null)) as offerslook_offer_count,
COUNT(if(test_status=1,true,null)) as jumpTimes1,COUNT(if(test_status=2,true,null)) as jumpTimes2,COUNT(if(test_status=3,true,null))
as jumpTimes3,COUNT(if(test_status=4,true,null)) as jumpTimes4,COUNT(if(test_status=5,true,null)) as jumpTimes5,
COUNT(if(test_status=6,true,null)) as jumpTimes6 from offerslook_offer a LEFT JOIN 
(SELECT offerslook_ad_id,pk_tb_offerslook_id from total_offer) b on a.id=b.pk_tb_offerslook_id where offer_status=1 and b.offerslook_ad_id=adv_id) s) t
set statistical_data.gOfferCount=t.gOfferCount,statistical_data.offerslook_offer_count=t.offerslook_offer_count,statistical_data.jumpTimes1=t.jumpTimes1,
statistical_data.jumpTimes2=t.jumpTimes2,statistical_data.jumpTimes3=t.jumpTimes3,
statistical_data.jumpTimes4=t.jumpTimes4,statistical_data.jumpTimes5=t.jumpTimes5,statistical_data.jumpTimes6=t.jumpTimes6,statistical_data.cz_time=unix_timestamp() * 1000
where statistical_data.offerslook_ad_id=adv_id;