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