mysql use while loop in cursor

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

Provide ALL code needed for to reproduce (tables structures and initial sample data as CREATE TABLE + INSERT INTO, DDL for all used functions, and desired output or final data state) or a link to online fiddle with this code. — Akina
– Akina, Commented Jul 12, 2022 at 4:44
I run this sql file inside navicat Use CLI until the code is completely debugged. — Akina
– Akina, Commented Jul 12, 2022 at 4:45
This is my problem so sorry, I have put the correct working code, thank you for your suggestion. — jason-lin
– jason-lin, Commented Jul 13, 2022 at 4:28

blabla_bingo · Accepted Answer · 2022-07-12 04:48:06Z

1

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

edited Jul 12, 2022 at 4:48

answered Jul 12, 2022 at 4:14

blabla_bingo

2,2171 gold badge4 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

blabla_bingo Over a year ago

@Akina Thank you very much for your observance. I have changed it to s=1. Usually I declare a fin variable (which defaults to false) to have a better readability.

Akina Over a year ago

s can be either 0 or 1. In this case if s=1 then and if s then is the same (but 2nd variant is shorter and performs 1 compare less). Usually I define a fin variable Opinion-based. I use done variable name.

jason-lin Over a year ago

Thank you very much for your answer, I have solved my problem. The variable name in the above code was wrong, I corrected it and still used the while loop, and the result was correct. I will remember your answer.

Collectives™ on Stack Overflow

mysql use while loop in cursor

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related