mysql set 算法_MySQL高级算法题 存储过程

#创建存储过程 求1-100的值

#while

delimiter $$

create procedure pro_sum(out count int)

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;

set count = sum;

end $$

delimiter ;

call pro_sum(@count);

select @count;

#repeat

delimiter $$

create procedure pro_sum2(out count int)

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;

set count = sum;

end $$

delimiter ;

#LOOP+if+leave+循环体

delimiter $$

create procedure pro_sum3(out count int)

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

leave lp;

end if;

end loop lp;

end $$

delimiter ;

#求10以内奇数值的和

#while

delimiter $$

create procedure pro_jsum(out count int)

begin

declare j int(11) default 1;

declare sum int(11) default 0;

while j <= 10 do

set sum = sum + j;

set j = j + 2;

end while;

set count = sum;

end $$

delimiter ;

call pro_jsum(@count);

select @count;

delimiter $$

create procedure pro_jsum1(out count int)

begin

declare j int default 1;

declare sum int default 0;

repeat

set sum = sum + j;

set j = j + 2;

until j>=10

end repeat;

end $$

delimiter ;

#loop+leave

delimiter $$

create procedure pro_jsum2(out count int)

begin

declare j int default 1;

declare sum int default 0;

lp1:loop

set sum = sum + j;

set j = j + 2;

if i>=10 then

leave lp1;

end if;

end loop lp1;

end $$

delimiter ;

#创建存储过程 求1-n的值

delimiter $$

create procedure pro_n(in num1 int)

begin

declare i int default 1;

declare sum int default 1;

while i < num1 do

set i = i+1;

set sum = sum + i;

end while;

select sum;

end $$

delimiter ;

call pro_n(10);

#repeat

delimiter $$

create procedure pro_n1(in num1 int)

begin

declare i int default 1;

declare sum int default 1;

repeat

set sum = sum + i;

set i = i + 1;

until i < num1

end repeat;

end $$

delimiter ;

#LOOP

delimiter $$

create procedure pro_n2(in num1 int)

begin

declare i int default 1;

declare sum int default 1;

lp:loop

set sum = sum + i;

set i = i + 1;

if i < num1 then

leave lp;

end if;

end loop lp;

end $$

delimiter ;

#存储过程实现1-100偶数和

#while

delimiter $$

create procedure pro_osum(out num2 int)

begin

declare i int default 0;

declare sum int default 0;

while i <= 100 do

set i = i + 2;

set sum = sum + i;

end while;

set num2=sum;

end $$

delimiter ;

call pro_osum(@num2);

select @num2;

#repeat

delimiter $$

create procedure pro_osum1(out num2 int)

begin

declare i int default 0;

declare sum int default 0;

repeat

set i = i + 2;

set sum = sum + i;

until i<=100

end repeat;

set num2 = sum;

end $$

delimiter ;

#loop

delimiter $$

create procedure pro_osum2(out num2 int)

begin

declare i int default 0;

declare sum int default 0;

lp:loop

set i = i + 2;

set sum = sum + i;

if i>100 then

leave lp;

end if;

end loop lp;

end $$

delimiter ;

#计算n！

#while

delimiter $$

create procedure pro_n3(in num int)

begin

declare i int default 1;

declare n int default 1;

while i<=num do

set n = n * i;

set i = i + 1;

end while;

select n;

end $$

delimiter ;

call pro_n3(5);

#repeat

delimiter $$

create procedure pro_n4(in num int)

begin

declare i int default 1;

declare n int default 1;

repeat

set n = n* i;

set i = i + 1;

until i > num

end repeat;

end $$

delimiter ;

#loop

delimiter $$

create procedure pro_n5(in num int)

begin

declare i int default 1;

declare n int default 1;

lp:loop

set n = n * i;

set i = i + 1;

if i>num then

leave lp;

end if;

end loop lp;

end $$

delimiter ;