mysql的declare游标一次记录多条数据

小鱼儿 2022-09-11 15:10 110阅读 0赞

declare upt_cursor cursor for
    			select availableqty,pfid,c.olshopid,c.num_iid,c.sku,c.outter_id from (
    						select (a.wareqty - a.awaitqty) as availableqty ,b.olshopid,a.warecode from d_stock_h as a left join d_shop_busi as b
    						on a.busno = b.busno
    						where a.warecode = (select warecode from d_ware_bind_list where bindwarecode = var_outter_id)
    					) as a1 left join
    					d_shop_ware as c
    					on c.olshopid = a1.olshopid
    					where outter_id = NEW.outter_id;

以上就是记录declare游标的多条记录

处理游标有以下的方式,一条一条的处理:

select count(0) into @var_count from (
    					select (a.wareqty - a.awaitqty) as availableqty ,b.olshopid,a.warecode from d_stock_h as a left join d_shop_busi as b
    					on a.busno = b.busno
    					where a.warecode = NEW.warecode
    				) as a1 left join
    				d_shop_ware as c
    				on c.olshopid = a1.olshopid
    				where outter_id like concat( '%', NEW.warecode, '%' );
    			open upt_cursor;
    			while @var_count > 0 do
    			fetch next from upt_cursor into var_availableqty,var_pfid,var_olshopid,var_numiid,var_sku,var_outter_id;
    			if exists(select wareqty from d_ware_bind_list where bindwarecode = var_outter_id)
    				then
    					select wareqty into @wareqty_copy from d_ware_bind_list where bindwarecode = var_outter_id;
    					select floor(var_availableqty/@wareqty_copy) into @var_wareqty_copy;
    					insert into dst_111yao_doms.d_stock_upt(olshopid, tfid, busno_online, numiid, warecode, skuid, wareqty, `status`, createtime, time_stamp)
    					values (var_olshopid, var_pfid, (select s.sellerid from d_shop as s where olshopid = var_olshopid), var_numiid, var_outter_id, var_sku, @var_wareqty_copy, 0,now(),  now());
    				
    			
    			else
    				insert into dst_111yao_doms.d_stock_upt(olshopid, tfid, busno_online, numiid, warecode, skuid, wareqty, `status`, createtime, time_stamp)
    				values (var_olshopid, var_pfid, (select s.sellerid from d_shop as s where olshopid = var_olshopid), var_numiid, var_outter_id, var_sku, var_availableqty, 0,now(),  now());
    			end if;
    			set @var_count = @var_count -1;
    			end while;
    			close upt_cursor;