[color=green][b][size=x-large][quote]mysql 循环使用:[/quote][/size][/b][/color]
[size=small][b]一 、 while 循环[/b][/size]
[size=small][b] 二 、 REPEAT 循环[/b][/size]
[size=small][b] 三、 LOOP 循环[/b][/size]
[color=green][b][size=x-large][quote]mysql 游标使用:[/quote][/size][/b][/color]
[size=small][b]一 、 while 循环[/b][/size]
begin
declare pageSize integer default 300;
declare totalCount integer default 0;
declare i integer default 0;
select count(*) from goods into totalCount;
if totalCount>0 then
while i < 10 do
#执行数据匹配
#insert into test(startPos, status) values(i*pageSize, 'no');
set i = i+1;
end while;
end if;
end
[size=small][b] 二 、 REPEAT 循环[/b][/size]
drop procedure if exists looppc;
create procedure looppc()
begin
declare i int;
set i = 1;
repeat
insert into user_profile_company (uid) values (i+1);
set i = i + 1;
until i >= 20
end repeat;
end //
[size=small][b] 三、 LOOP 循环[/b][/size]
delimiter $$
drop procedure if exists lopp;
create procedure lopp()
begin
declare i int ;
set i = 1;
lp1 : LOOP // lp1 为循环体名称 LOOP 为关键字insert into user_profile (uid) values (i);
set i = i+1;
if i > 30 then
leave lp1; // 离开循环体
end if;
end LOOP; // 结束循环
end $$
[color=green][b][size=x-large][quote]mysql 游标使用:[/quote][/size][/b][/color]
begin
declare name varchar(100);
declare userId int(11);
DECLARE done INT DEFAULT 0;
DECLARE mycursor CURSOR FOR select id, name from user;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN mycursor;
REPEAT
FETCH mycursor INTO userId , name;
IF NOT done THEN
select count(*) into @coun from user_te where name=name;
insert into user_compare(userId, name, total, addtime) values(userId , name, @coun, now());
END IF;
UNTIL done END REPEAT;
CLOSE mycursor;
end