第一个mysql存储过程

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存储过程、游标、触发器

MySQL必知必会笔记（六）存储过程 游标 触发器