mysql 8 存储过程+游标详细实例
近期小编遇到了一个小需求“获取每类商品的可用库存,计算出他们的总价值”,这个小需求看起来非常的简单,用可用库存x每类商品的最新进价然后求和汇总就行。如果用Java代码实现,当商品类型过多例如(10000笔),在获取每个商品最新的进价时,就会向数据库执行10000次请求,这样程序的性能就会大幅度降低,使用存储过程+游标则能很好的解决这个问题,下面脚本中小编详细的写出了执行逻辑和步骤,可供大家学习和参考。
若有不一样的实现方式,请大家留言赐教。
--如果存储过程存在则删除
drop procedure if exists total_val;
DELIMITER $$
CREATE PROCEDURE total_val() -- 获取所有药品库存>0,的成本
BEGIN
-- 游标循环变量
declare done int default true;
-- 药品ID
declare temp_drugs_id varchar(20) default '';
-- 可用库存
declare temp_available_inventory DECIMAL(12,2) DEFAULT 0;
-- 药品进价
declare temp_pur_price DECIMAL(12,4) DEFAULT 0;
-- 定义游标,并将查询到的结果集赋值到游标中
declare cursor_available_inventory cursor for
select drugs_id,available_inventory from hosp_inventory_info a where available_inventory > 0 ;
-- 游标遍历完后,将变量done的值设为 false,表示循环结束
declare continue handler for not found set done = false;
-- 清除临时表的数据
truncate table hosp_temp_val_tab;
-- 打开游标
open cursor_available_inventory;
fetch cursor_available_inventory into temp_drugs_id,temp_available_inventory; -- 将游标中的值赋值给变量。注意:变量名不能与返回的列名相同,变量顺序与sql结果列的顺序一致
while done <> false do
-- 获取价格
select pur_price from hosp_enter_inventory_info where drugs_id=temp_drugs_id order by en_date desc,en_time desc limit 1 into temp_pur_price;
-- 将结果保存至临时表
insert into hosp_temp_val_tab values(temp_drugs_id,temp_available_inventory,temp_pur_price);
fetch cursor_available_inventory into temp_drugs_id,temp_available_inventory;
end while;
-- 关闭游标
close cursor_available_inventory;
-- 计算汇总值
select truncate(sum(available_inventory*pur_price),2) as total_amt from hospitals.hosp_temp_val_tab;
END$$
DELIMITER ;