CODEWITHSUNDEEP
mysqlwhile-loopcursor
jason-lin
jason-lin

Reputation: 17

mysql use while loop in cursor


delimiter $$
drop procedure if exists insert_person_param;
create procedure insert_person_param()
begin


DECLARE s int DEFAULT 0;
declare p_t_id bigint(20);
declare varmodule int DEFAULT 0;
declare varparam int DEFAULT 0; 
declare m_name varchar(255);

declare pid cursor for select product_id from products;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;

open pid;
fetch pid into p_t_id;
while s<>1 do

while varmodule<3 do
set m_name=rand_string(2);

while varparam<10 do

insert into person_param (product_id, module_name, param_name, var_type, var_name, var_value, is_activated, compute_value) 
values(p_t_id,concat('模块',m_name),rand_string(3),'int',rand_string(6),'200',1,'ok');
set varparam=varparam+1;
end while;
set varparam=0;
set varmodule=varmodule+1;
end while;
set varmodule=0;

fetch pid into p_t_id;
end while;
close pid;
end $$

rand_string() and rand_num() is a rand function

I want to start a loop in the cursor, I run this sql file inside navicat, but I keep getting an error and I can't see a valid error message. Hope to give me some advice

Upvotes: 0

Views: 310

Answers (1)

blabla_bingo
blabla_bingo

Reputation: 2152

I would use loop instead of while. If the condition check is true for a While loop, a whole cycle is executed. Using a loop is more flexible as you can choose when to leave. By the way, you have a varialbe named varparam which you have not defined. I changed it to a user variable @varparam and successfully created the procedure in workbench.

create procedure insert_person_param()
begin

DECLARE s int DEFAULT 0;
declare p_t_id bigint(20);
declare varmodule int DEFAULT 0;

declare pid cursor for select product_id from products;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;

open pid;
lp:loop -- the label for the loop is lp
fetch pid into p_t_id;  -- Unlike like while loop, here we can do the fetch at the begining of the loop. And leave the loop in the upcoming if statement if the NOT FOUND handler is triggered.
if s=1 then -- It should be s=1 when no more to fetch
leave lp; -- leave the loop if NOT FOUND
end if;

while varmodule<3 do
insert into person_param (product_id, module_name, param_name, var_type, var_name, var_value, is_activated, compute_value) 
values(p_t_id,concat('模块','acv'),rand_string(3),'int',rand_string(6),'200',1,'ok');

set varparam=varparam+1; -- WATCH OUT for this varparam, which you have not defined. It raises an error.
end while;
set varparam=0;

end loop lp;
close pid;
end $$

Upvotes: 1

Related Questions