mysql 存储过程跟循环使用
mysql 存储过程和循环使用
一 、 while 循环
二 、 REPEAT 循环
三、 LOOP 循环
引用
mysql 循环使用:
一 、 while 循环
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
二 、 REPEAT 循环
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 //
三、 LOOP 循环
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 $$
引用
mysql 游标使用:
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