BEGIN
-- 声明局部变量
DECLARE done BOOLEAN DEFAULT 0;
DECLARE goods_name varchar(266);
DECLARE cat_name varchar(255);
DECLARE goname CURSOR
For
SELECT b.goodsName as goodsName,c.name as catname FROM es_spider_bigdata b,es_goods_cat c where c.cat_id=b.catid GROUP BY goodsName ;
-- 当SQLSTATE为02000时设置done值为1
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
-- 创建一个ordertotals的表
-- CREATE TABLE IF NOT EXISTS ordertotals( order_num INT , total DECIMAL(8,2))
start transaction;
delete from bigana_result_temp where DATE_SUB(curdate(),INTERVAL 30 day)>date;
commit;
Open goname ;
-- 开始循环
REPEAT
-- 把当前行的值赋给声明的局部变量o中
FETCH goname INTO goods_name,cat_name;
-- 用上文讲到的ordertotal存储过程并传入参数,返回营业税计算后的合计传给t变量
INSERT INTO bigana_result_temp (catName, price, unit, location, name, date) SELECT
c.name catName,
format(avg(goodsprice), 2) price,
u.unit unit,
e.location location,
e.goodsName name,
date_format(e.date, '%y-%m-%d') date
FROM
es_goods_cat c,
es_spider_bigdata e,
(
SELECT
unit
FROM
es_spider_bigdata t,
es_goods_cat c
WHERE
t.goodsname = goods_name
AND t.catid = c.cat_id
AND c. NAME = cat_name
GROUP BY
unit
ORDER BY
count(unit) DESC
LIMIT 0,
1
) u
WHERE
e.unit = u.unit
AND goodsname = goods_name
AND c.cat_id = e.catid
AND c. NAME = cat_name
-- 正式环境中需要放开
-- And e.date=CURDATE();
GROUP BY
e.location;
-- 把订单号和合计插入到新建的ordertotals表中
-- 当done为真时停止循环
UNTIL done END REPEAT;
-- 关闭游标
Close goname ;
END
参考资料:
MYSQL存储过程、游标、触发器