mysql游标遍历

最新推荐文章于 2024-08-16 10:03:31 发布

原创最新推荐文章于 2024-08-16 10:03:31 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了一种使用存储过程批量更新库存中商品数量的方法。通过声明变量和游标，遍历指定库存编号下的所有商品ID，并从商品表中减去相应的数量。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

create procedure cutGoodNum(in stockNo varchar(50))
   begin
declare tempGood_id int(11) default 0 ;  
declare tempGood_num int(11) default 0 ;  

declare cur1 CURSOR FOR select  good_id  from stock_detail where stock_id = stockNo ;  


declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tempGood_id = null; 
OPEN cur1;
FETCH cur1 INTO tempGood_id;
WHILE ( tempGood_id is not null) DO 


SET tempGood_num = ( select num from stock_detail where good_id = tempGood_id and stock_id = stockNo) ;
update goods set good_num =( good_num - tempGood_num ) where id  = tempGood_id ;

FETCH cur1 INTO tempGood_id;


END WHILE;
CLOSE cur1;
     end;