mysql 存储过程嵌套游标动态参数( 外层查询结果作为内嵌游标动态参数)

测试用例:

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);