mysql存储过程小点

drop procedure if exists mypro2;
create procedure mypro2(in score int)
BEGIN
if score >= 60 then
select 'pass';
ELSE
select 'no';
end if;
end;
call mypro2(150);
-----------------------------------------------------
drop procedure if exists mypro3;
create procedure mypro3(out score int)
begin
set score = 100;
end;
call mypro3(@aa);
select @aa;
------------------------------------------------------
drop procedure if exists mypro4;
create procedure mypro4(inout score int)
begin
select score;
end;
set @aa=50;    
call mypro4(@aa);  --不能直接用数字
------------------------------------------------------
drop  procedure if exists mypro5;
create procedure mypro5(in a int, in b int) -------- 多参数
BEGIN
declare c int default 0;  -- 必须要声明这个变量
set c = a + b;
select c;
end;
call mypro5(20, 30);
------------------ case when -------------------------
drop procedure if exists mypro6;
create procedure mypro6(in score int)
begin
case score
when 20 then select '>20';
when 30 then select '>30';
when 40 then select '>40';
when 50 then select '>50';
when 60 then select '>60';
else select 'well';  -- 这里和其他语言不一样
end case;
end;
call mypro6(55);
------------------- while  ---------------------------
drop procedure if exists mypro7;
create procedure mypro7()
begin
declare i int default 0;
declare j int default 0;
while i < 10 do
set j = j + i;
set i = i + 1;
end while;
select j;
end;
call mypro7();
------------------- repeat until ----------------------
drop procedure if exists mypro8;
create procedure mypro8()
begin
declare i int default 0;
declare j int default 0;
repeat
set j = j +i;
set i = i + 1;
until j >= 10     -- 条件语句没有;
end REPEAT;       -- 逻辑结束需要;
select j;
end;
call mypro8();
------------------- loop ------------------------------
drop procedure if exists mypro9;
create procedure mypro9()
begin
declare i int default 0;
declare s int default 0;
loop_lable:loop             -- loop_lable 控制退出loop的时候需要
set s = s + 1;
set i = i + 1;
if i >= 10 then
leave loop_lable;           -- 退出loop
end if;
end loop;
select s;
end;
call mypro9();

show procedure status like '%9';   -- 查看存储过程的状态
show create procedure mypro9;      -- 查看存储过程代码