#处理采集数据DROP PROCEDURE IF EXISTS do_collect_price;CREATE PROCEDURE do_collect_price()BEGIN DECLAR
#处理采集数据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
