MySQL在光标中使用while循环-解网

问：


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 函数rand_num()

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

mysql while-loop 游标

我会用代替 .如果循环的条件检查为 true，则执行整个循环。使用 a 更灵活，因为您可以选择何时离开。顺便说一句，您有一个尚未定义的 varialbe。我将其更改为用户变量，并成功在工作台中创建了该过程。loopwhileWhileloopvarparam@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 $$

MySQL在光标中使用while循环

mysql use while loop in cursor

评论

评论