MySQL在存储过程中实现while循环

whlie 循环

drop  procedure if exists sample_while_loop;
delimiter //
create procedure sample_while_loop ()
begin
	declare counter int default 1;
	while counter<10 DO
		select counter;
	    set counter = counter +1;
	end while;
end //
call   sample_while_loop() //
drop  procedure sample_while_loop //
delimiter ;

工作实际使用

循环2500次在表中插入数据，插入的字段值中有rownum

drop procedure if exists testInsert;
DELIMITER $$
create procedure testInsert ()
begin
	declare rownum int default 1;

while rownum<2500 DO
     insert
	into
	vip_point_spec_rule(
	saas_cust_id,
	vip_point_spec_rule_id,
	vip_point_prj_id,
	grp_type,
	grp_code,
	grp_name,point_rate,crt_date,crt_usr_id,crt_usr_code,upd_date,upd_usr_id,upd_usr_code,org_group_id)
select
	'0',
	nc.cls_id ,
	'1454871602956075166',
	'C',
	nc.cls_code ,
	nc.cls_name, 
    rownum,
	sysdate() ,
	'1353007673544540190',
	'0023',
	date_format(sysdate(), '%Y-%m-%d'),
	'1353007673544540190',
	'0023',
	'0'
from
	nav_category nc limit rownum, 1;
	 set rownum = rownum +1;

	end while;
end$$
call testInsert() $$
drop procedure testInsert $$
DELIMITER ;