mysql在游标中使用while循环

Question

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()和rand_num()是rand函数。

我想在游标中启动一个循环，我在navicat中运行这个sql文件，但是我一直收到一个错误，我看不到有效的错误消息。希望能给我一些建议

Answer 1

我会使用loop而不是while。如果条件检查对While循环为真，则执行整个循环。使用loop更灵活，因为您可以选择何时离开。顺便说一句，您有一个名为varparam的变量，您还没有定义它。我将其更改为用户变量@varparam，并在工作台中成功地创建了该过程。

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 $$