I created a procedure as below in MySQL workbench 8.0.12 community, win10. A cursor is declared to loop through more than 16000 member's ID in a table to insert their number of orders created in different dates into another table.
Member's IDs and their dates of registration are selected here for the cursor. select distinct BAG,AAA from DICT_ALLE_AAAF where AAA>=Date_Memb_Star. Once I call it call PRO_MIDD_MEMB_AAAA_1('2017/01/01','2019/11/10',60), it only looped through 5 member's IDs and did not report any error. But if I took the while loop out of the procedure and with select Var_BAG on, the cursor loop went selecting more than 200 member's IDs until it asked me to cancel it. Can anyone tell me what is wrong in my code? Many thanks.
delimiter //
create procedure PRO_MIDD_MEMB_AAAA_1
(in Date_Memb_Star date,
in Date_Sale_End date,
in Int_Inte int)
begin
declare Int_Floo int default 0;
declare Int_Ceil int;
declare Int_MembAll int;
declare Int_Loop int default 1;
declare Int_MaxiLoop int;
declare Int_DaySpen int default 30;
declare Int_Done int;
declare Var_BAG varchar(16);
declare Date_Memb_Regi date;
declare Cur_Memb cursor for
select distinct BAG,AAA from DICT_ALLE_AAAF where AAA>=Date_Memb_Star order by BAG;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Int_Done = 1;
truncate table MIDD_MEMB_AAAA;
open Cur_Memb;
read_loop:loop
fetch Cur_Memb into Var_BAG,Date_Memb_Regi;
if Int_Done=1 then
leave read_loop;
end if;
/*select Var_BAG;*/
set Int_MaxiLoop=ceil(datediff(Date_Sale_End,Date_Memb_Regi)/Int_Inte);
set Int_Floo=0;
while Int_Loop<=Int_MaxiLoop do
set Int_Ceil=Int_Floo+Int_Inte;
insert into MIDD_MEMB_AAAA
select Var_BAG,Int_Floo,Int_Ceil,
count(distinct BAK)*(Int_DaySpen/Int_Inte) as Numb_Con_Avg
from OPER_SALE_AAAB
where BAG=Var_BAG
and timestampdiff(hour,Date_Memb_Regi,AAA)/24>Int_Floo
and timestampdiff(hour,Date_Memb_Regi,AAA)/24<=Int_Ceil
and AAB>0;
set Int_Floo=Int_Floo+Int_Inte;
set Int_Loop=Int_Loop+1;
end while;
end loop read_loop;
close Cur_Memb;
end//
delimiter ;
Little oversight: you forgot to reset your variable Int_Loop, so after the first run, it only enters the loop when Int_MaxiLoop has a new maximum value.
Reinitialize it (to 1, I assume) before the loop:
...
set Int_Floo=0;
set Int_Loop=1;
while Int_Loop<=Int_MaxiLoop do ...