mysql存储过程 定时任务

#处理采集数据DROP PROCEDURE IF EXISTS do_collect_price;CREATE PROCEDURE do_collect_price()BEGIN     DECLARE flag INT DEFAULT 0;#声明游标循环标识    DECLARE now_time INT DEFAULT UNIX_TIMESTAMP();#声明游标循环标识    DECLARE start_time int DEFAULT UNIX_TIMESTAMP(date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(day from now())-1 day),interval 1 month));    DECLARE end_time int DEFAULT UNIX_TIMESTAMP(date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(day from now()) day),interval 0 month));    DECLARE percent FLOAT DEFAULT 0.1;#去除最高最低价格百分比    DECLARE date_time char(8) DEFAULT extract(year_month from NOW());#当前年份月份201310    DECLARE num int DEFAULT 0;#总数    DECLARE t_cut int DEFAULT 0;#需要去掉的价格一半    DECLARE t_total int DEFAULT 0;#有效价格总数    DECLARE temp_Price DECIMAL(18,4);    DECLARE tMaterialID char(30);    DECLARE tMaterialName char(250);    DECLARE tMaterialUnit char(150);    DECLARE tMaterialFormat char(150);    DECLARE cur CURSOR FOR SELECT MaterialID,MaterialName,MaterialUnit,MaterialFormat,count(MaterialID) as num FROM collect_price WHERE add_time > start_time AND add_time < end_time GROUP BY MaterialID,MaterialName,MaterialUnit,MaterialFormat;    DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=1;    OPEN cur;        #删除jcj_price当月价格包信息        DELETE FROM jcj_price WHERE PriceMonth = date_time;        #删除pricepacklist可用期间信息        DELETE FROM pricepacklist WHERE packid = 1020 and packmonth = date_time;        FETCH cur INTO tMaterialID,tMaterialName,tMaterialUnit,tMaterialFormat,num;    WHILE flag <> 1 DO        IF num < 3            THEN                select avg(MaterialPrice) from (select MaterialPrice from collect_price where add_time > start_time and add_time < end_time and MaterialID=tMaterialID and MaterialName=tMaterialName and MaterialUnit=tMaterialUnit and MaterialFormat=tMaterialFormat) p INTO temp_Price;        ELSE                SET t_cut = ROUND(num*percent);                SET t_total = num - 2*t_cut;                select avg(MaterialPrice) from (select MaterialPrice from collect_price where add_time > start_time and add_time < end_time and MaterialID=tMaterialID and MaterialName=tMaterialName and MaterialUnit=tMaterialUnit and MaterialFormat=tMaterialFormat order by MaterialPrice asc limit t_cut,t_total) p INTO temp_Price;        END IF;        insert into  jcj_price(MaterialID,PriceMonth,Price,MaterialName,MaterialUnit,MaterialFormat)values(tMaterialID,date_time,temp_Price,tMaterialName,tMaterialUnit,tMaterialFormat);        FETCH cur INTO tMaterialID,tMaterialName,tMaterialUnit,tMaterialFormat,num;    END WHILE;    insert into pricepacklist(packid,packmonth,packver,packname,periodtype,enabled)values(1020,date_time,1,'',1,0);    #备份收集数据    #SELECT * INTO collect_price_bakup FROM collect_price WHERE add_time > start_time and add_time < end_time;    #DELETE FROM collect_price WHERE add_time > start_time and add_time < end_time;    CLOSE cur;END#call pricepack.do_collect_price()

转载自:http://www.9958.pw/post/mysql_procedure