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 ;