测试用例: drop procedure if exists `sp_nested_cursor`; create procedure `sp_nested_cursor`() begin declare v_uid bigint(22); declare v_code varchar(100); declare v_value varchar(100); declare _done TINYINT(1) default 0; declare cur_user cursor for select id from `stockinventory`; declare continue handler for not found set _done = 1; open cur_user; loop_xxx:loop fetch cur_user into v_uid; if _done=1 then leave loop_xxx; end if; begin declare _inner tinyint(1) default 0; declare cur_param cursor for select id, stockInventoryId from `inventory` where stockInventoryId=v_uid; declare continue handler for not found set _inner = 1; open cur_param; loop_yyy:loop fetch cur_param into v_code, v_value; if _inner=1 then leave loop_yyy; end if; select v_uid, v_code, v_value; -- insert into tb_key_value values (v_uid, v_code, v_value); end loop; commit; end; end loop; end; call `sp_nested_cursor`(); 在项目中的实用: -- 针对inventory表 drop procedure if exists `sp_inventory_cursor`; create procedure `sp_inventory_cursor`( in param_warehouse int ) begin -- declare v_uid bigint(22); DECLARE _curInventoryId int; declare _curLocationId int; DECLARE _curPadNo varchar(20); declare _curBoxNo varchar(20); DECLARE _warehouseId int; declare _curProductId int; declare _exsitFlag int DEFAULT 0; -- DECLARE done INT DEFAULT 0; DECLARE _errorSun INT DEFAULT 0 ;-- 定义错误计数器 declare resultInventory int; DECLARE token int DEFAULT 0; DECLARE inVentorys varchar(50); ## 重复表的记录作为游标循环 declare _done TINYINT(1) default 0; DECLARE rs_cursor CURSOR FOR select id,productId,storagelocationId,warehouseId from inventory where totalcount=0 and warehouseId=param_warehouse group by productId,storagelocationId,warehouseId having count(*) >1 ; -- 查询重复记录 declare continue handler for not found set _done = 1; -- select now(); open rs_cursor; loop_xxx:loop fetch rs_cursor into _curInventoryId,_curProductId,_curLocationId,_warehouseId; if _done=1 then leave loop_xxx; end if; begin declare _inner tinyint(1) default 0; declare cur_param cursor for select id from inventory where totalcount=0 and productId=_curProductId and storagelocationId=_curLocationId and warehouseid=_warehouseId and id != _curInventoryId; declare continue handler for not found set _inner = 1; open cur_param; loop_yyy:loop fetch cur_param into resultInventory; if _inner=1 then leave loop_yyy; end if; -- select _curInventoryId, resultInventory; -- 判断是否存在于相关表中 update inventoryadjustinventoryrel set inventoryId =_curInventoryId where inventoryId = resultInventory; update inventoryflow set inventoryId =_curInventoryId where inventoryId = resultInventory; update inventoryflowqueue set inventoryId =_curInventoryId where inventoryId = resultInventory; update inventoryflowqueuehistory set inventoryId =_curInventoryId where inventoryId = resultInventory; update inventorylockhistory set inventoryId =_curInventoryId where inventoryId = resultInventory; update inventorylock set inventoryId =_curInventoryId where inventoryId = resultInventory; update stockgroupdetailinventoryrel set inventoryId =_curInventoryId where inventoryId = resultInventory; update storagelocationadjust set adjustInventoryId =_curInventoryId where adjustInventoryId = resultInventory; update storagelocationadjustinventoryrel set adjustInventoryId=_curInventoryId where adjustInventoryId = resultInventory; -- 处理相关完所有记录后删除 delete from inventory where id = resultInventory; -- set inVentorys =CONCAT(inVentorys,":",resultInventory); end loop; commit; end; end loop; -- select now(); end; call `sp_inventory_cursor`(34); -- 针对stockinventory表 drop procedure if exists `sp_stockinventory_cursor`; create procedure `sp_stockinventory_cursor`( in param_warehouse int ) begin -- declare v_uid bigint(22); DECLARE _curInventoryId int; declare _curLocationId int; DECLARE _curPadNo varchar(20); declare _curBoxNo varchar(20); DECLARE _warehouseId int; declare _curProductId int; declare _exsitFlag int DEFAULT 0; -- DECLARE done INT DEFAULT 0; DECLARE _errorSun INT DEFAULT 0 ;-- 定义错误计数器 declare resultInventory int; DECLARE token int DEFAULT 0; DECLARE inVentorys varchar(50); ## 重复表的记录作为游标循环 declare _done TINYINT(1) default 0; DECLARE rs_cursor CURSOR FOR select id,productId,locationId,warehouseId from stockinventory where warehouseId=param_warehouse and quantity=0 group by productId,locationId,warehouseId having count(*) >1 ; -- 查询重复记录 declare continue handler for not found set _done = 1; -- select now(); open rs_cursor; loop_xxx:loop fetch rs_cursor into _curInventoryId,_curProductId,_curLocationId,_warehouseId; if _done=1 then leave loop_xxx; end if; begin declare _inner tinyint(1) default 0; declare cur_param cursor for select id from stockinventory where quantity=0 and productId=_curProductId and locationId=_curLocationId and warehouseid=_warehouseId and id != _curInventoryId; declare continue handler for not found set _inner = 1; open cur_param; loop_yyy:loop fetch cur_param into resultInventory; if _inner=1 then leave loop_yyy; end if; -- select _curInventoryId, resultInventory; -- 判断是否存在于相关表中 update INVENTORY set stockInventoryId =_curInventoryId where stockInventoryId = resultInventory; update inventoryadjust set stockInventoryId =_curInventoryId where stockInventoryId = resultInventory; update stocktakinggroupstockinvrel set stockInventoryId =_curInventoryId where stockInventoryId = resultInventory; -- 处理相关完所有记录后删除 delete from stockinventory where id = resultInventory; -- set inVentorys =CONCAT(inVentorys,":",resultInventory); end loop; commit; end; end loop; -- select now(); end; call `sp_stockinventory_cursor`(34); (责任编辑:IT) |