需求:存在一张customer表,现需要统计customer的总消费金额。但是MYSQL的实时查询在customer数据量非常大时查询速度会很慢,这时可以利用存储过程将customer的消费金额提前统计出来,用字段保存在customer表中,查询时只需直接在customer表中读取数据即可。
DELIMITER $$ #定义语句结束标识
DROP PROCEDURE IF EXISTS `tb_moneyNum`$$
CREATE PROCEDURE `tb_moneyNum`() #创建存储过程
BEGIN
DECLARE v_customerId INT; #定义customerId,用于查询此customer的消费统计
DECLARE done INT DEFAULT 0; #定义游标结束标识
DECLARE v_moneyNum DECIMAL(10,2); #定义消费总额
DECLARE token INT DEFAULT 0; #定义子游标结束标识
#定义获取customer游标
DECLARE c_cursor CURSOR FOR SELECT id FROM tb_customer WHERE isDel=0;
#游标结束条件
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
#打开游标
OPEN c_cursor;
cursor_loop:LOOP #读取游标
FETCH c_cursor INTO v_customerId; #将游标中查询的数据放入v_customerId
IF done = 1 THEN #判断当前游标是否需要结束,done=1时结束
LEAVE cursor_loop;
END IF;
SET v_moneyNum=0; #给消费总额赋初始值
BEGIN
#定义统计子游标
DECLARE c_cursor_item CURSOR FOR
SELECT SUM(cost) FROM tb_makeup_card WHERE customerId=v_customerId;
#游标结束条件
DECLARE CONTINUE HANDLER FOR NOT FOUND SET token=1;
OPEN c_cursor_item;
REPEAT #读取统计结果
FETCH c_cursor_item INTO v_moneyNum; #将统计结果放入v_moneyNum
#SELECT moneyNum;
UNTIL token = 1
END REPEAT;
CLOSE c_cursor_item;
SET token=0; #操作完成,重新赋token初始值,下次统计才会执行
UPDATE tb_customer SET moneyNum = v_moneyNum WHERE id = v_customerId; #保存统计结果到customer表
SET v_moneyNum=0;
END;
END LOOP;
CLOSE c_cursor;
END$$
DELIMITER ;