MySQL循环练习题

#向t_xunhuan1表中插入1-100
#WHILE 写法
delimiter $$
create procedure pro_insertxh()
begin
 #创建局部变量作为计数器
 declare i int default 1;
 while i <= 100 do #开始循环
  insert into t_xunhuan1 values(i);
  set i = i + 1;
 end while; #结束循环 
end $$
delimiter ;
call pro_insertxh();
drop procedure pro_insertxh;

#repeat 写法
delimiter $$
create procedure pro_insertxh2()
begin
 #创建计数器 declare
 declare i int default 1;
 repeat  #开始循环
   insert into t_xunhuan1 values(i); #循环体
   #更新计数器
   set i = i + 1;
 until i > 100  #当i大于100退出循环
 end repeat;
end $$
delimiter ;
call pro_insertxh2();
drop procedure pro_insertxh2;


#创建存储过程，求1-100的值
#while 写法
delimiter $$
create procedure pro_sum()
begin
declare i int default 1;
declare sum int default 0;
while i <= 100 do #开始循环
  set sum = sum + i;
  set i = i + 1;
 end while; #结束循环 
select sum;
end $$
delimiter ;
call pro_sum();
drop procedure pro_sum;

#repeat 写法
delimiter $$
create procedure pro_sum2()
begin
declare i int default 1;
declare sum int default 0;
repeat #开始循环
  set sum = sum + i;
  set i = i + 1;
until i > 100
 end repeat; #结束循环 
select sum;
end $$
delimiter ;
call pro_sum2();

#LOOP
delimiter $$
create procedure pro_sum3()
begin
 #创建计数器
 declare i int default 1;
 declare sum int default 0;
 lp:loop
 set sum = sum + i;
 #更新计数器
 set i = i + 1;
 #判断
 if i > 100 then #开始if
 #结束循环
 leave lp;
 end if; #结束if
end loop lp;#结束loop
select sum;
end $$
delimiter ;
call pro_sum3();
drop procedure pro_sum3;



#求10以内奇数值的和
#while
delimiter $$
create procedure pro_jssum()
begin
declare i int default 1;
declare sum int default 0;
while i <= 10 do #开始循环
 if i % 2 != 0 then
  set sum = sum + i;
 end if;
set i = i + 1;
 end while; #结束循环 
select sum;
end $$
delimiter ;
call pro_jssum();
drop procedure pro_jssum;

#repeat 写法
delimiter $$
create procedure pro_jssum2()
begin
declare i int default 1;
declare sum int default 0;
repeat #开始循环
  if i % 2 != 0 then
  set sum = sum + i;
 end if;
  set i = i + 1;
until i > 10
 end repeat; #结束循环 
select sum;
end $$
delimiter ;
call pro_jssum2();

#LOOP
delimiter $$
create procedure pro_jssum3()
begin
 #创建计数器
 declare i int default 1;
 declare sum int default 0;
 lp:loop
 if i % 2 != 0 then
 set sum = sum + i;
 end if;
 #更新计数器
 set i = i + 1;
 #判断
 if i > 10 then #开始if
 #结束循环
 leave lp;
 end if; #结束if
end loop lp;#结束loop
select sum;
end $$
delimiter ;
call pro_jssum3();
drop procedure pro_jssum3;

#存储过程实现求1-100之间的偶数之和的功能
#while 
delimiter $$
create procedure pro_osum()
begin
 declare i int default 1;
 declare sum int default 0;
  while i <= 100 do
   if i % 2 = 0 then
   set sum = sum + i;
   end if;
   set i = i + 1;
  end while;
select sum;
end $$
delimiter ;
call pro_osum();

#repeat 
delimiter $$
create procedure pro_osum2()
begin
 declare i int default 1;
 declare sum int default 0;
 repeat
  if i % 2 = 0 then
   set sum = sum + i;
  end if;
  set i = i + 1;
  until i > 100
 end repeat;
select sum;
end $$
delimiter ;
call pro_osum2();
drop procedure pro_osum2;

#loop
delimiter $$
create procedure pro_osum3()
begin
 declare i int default 1;
 declare sum int default 0;
 lp:loop
 if i % 2 = 0 then
 set sum = sum + i;
 end if;
 set i = i + 1;
 if i > 100 then
 leave lp;
 end if;
 end loop lp;
select sum;
end $$
delimiter ;
call pro_osum3();
drop procedure pro_osum3; 


#计算n!
#while 
delimiter $$
create procedure pro_jcsum(in n int,out sum int)
begin
 set sum = 1;
 while n > 0 do
  set sum = sum * n;
  set n = n - 1;
 end while;
end $$
delimiter ;
call pro_jcsum(5,@csum);
select @csum;
drop procedure pro_jcsum;

#repeat 
delimiter $$
create procedure pro_jcsum2(in n int,out sum int)
begin
set sum = 1;
 repeat
 set sum = sum * n;
 set n = n - 1;
 until n <= 0
 end repeat;
end $$
delimiter ;
call pro_jcsum2(5,@sum);
select @sum;
drop procedure pro_jcsum2;

#loop
delimiter $$
create procedure pro_jcsum3(in n int,out sum int)
begin
 set sum = 1;
 lp:loop
 set sum = sum * n;
 set n = n - 1;
 if n <= 0 then
 leave lp;
 end if;
 end loop lp;
end $$
delimiter ;
call pro_jcsum3(5,@sum);
select @sum;


#创建存储过程，求1-n的值
#while 
delimiter $$
create procedure pro_nsum(in n int,out sum int)
begin
declare i int default 1;
set sum = 0;
while i <= n do
set sum = sum + i;
set i = i + 1;
end while;
end $$
delimiter ;
call pro_nsum(1000,@sum);
select @sum;
drop procedure pro_nsum;

#repeat 写法
delimiter $$
create procedure pro_nsum2(in n int,out sum int)
begin
 declare i int default 1;
 set sum = 0;
repeat
 set sum = sum + i;
 set i = i + 1;
 until i > n
end repeat;
end $$
delimiter ;
call pro_nsum2(1000,@sum2);
select @sum2;
drop procedure pro_nsum2;

#LOOP
delimiter $$
create procedure pro_nsum3(in n int,out sum int)
begin
 declare i int default 1;
 set sum = 0;
 lp:loop
  set sum = sum + i;
  set i = i + 1;
  if i > n then
   leave lp;
  end if;
 end loop lp;
end $$
delimiter ;
call pro_nsum3(1000,@sum3);
select @sum3;
drop procedure pro_nsum3;

# loop+if+leave+循环名
delimiter $$
create procedure pro_insertxh3()
begin
 #创建计数器
 declare i int default 1;
 lp:loop
 insert into t_xunhuan1 values(i);
 #更新计数器
 set i = i + 1;
 #判断
 if i > 100 then #开始if
 #结束循环
 leave lp;
 end if; #结束if
end loop lp;#结束loop
end $$
delimiter ;
call pro_insertxh3();