没有合适的资源?快使用搜索试试~ 我知道了~
mysql的定时job的一个简单应用,采用mysql存储过程的调用方式执行任务。该任务中涉及到使用游标来完成多个update执行过程。
资源推荐
资源详情
资源评论
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;
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;
资源评论
gen_chen
- 粉丝: 0
- 资源: 3
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功