Mysql自学笔记五（存储过程）

1.存储过程--存储过程是编译后在服务器执行，速度快

2.定义存储过程

delimiter //

create procedure pro1(in/out/inout para 数据类型)

begin

declare var int;

set var=para;

if var>1 then

select sname from student;

else

select age from student;

end if;

end //

3.in参数：存储过程中，只接收参数，不改变参数值

delimiter //

create procedure pro1(in para int)

begin

select para;

set para=para+1;

select para;

end//

set @para=2;

call pro1(para);

select @para;

输出结果：

2；

3；

2;

4.out参数：存储过程中，只输出参数

delimiter //

create procedure pro2(out para int)

begin

select count(*) into para from student;

end//

call pro2(@para);

输出结果：

学生的总人数

5.inout参数:存储过程中，既接收参数，又改变参数值

delimiter //

create procedure pro3(inout para int)

begin

select para;

set para=para+1;

select para;

end//

set @para=2;

call pro3(@para);

select @para;

输出结果

2；

3；

3;

6.存储过程声明变量

declare var int default 1;

declare 变量名 变量类型 [default 默认值];

7.变量赋值

set var=1;

8.定义用户变量

set @para=1;

9.存储过程调用

call pro1(@para);

call 存储过程名(用户变量);

10.查看数据库中有多少个存储过程

show procedure status where db='mytest';

11.查询存储过程详情

show create procedure mytest.pro1;

12.删除存储过程

drop procedure pro1;

13.条件语句

if....then...else....end if;

case 变量

 when 常量1/表达式1 then..;

 when 常量2/表达式2 then..;

else .....;

end case;

例：查询出student表中学生姓名和对应的成绩等级（>=90为优秀>=70为良好>=60为及格其余为补考）

select tname,score,
case
when score>=90 then '优秀'
when score>=70 then '良好'
when score>=60 then '及格'
else '补考'
end
from t1;

create procedure pro3()
begin
select sname,
case
when score>=90 then '优秀'
when score>=70 then '良好'
when score>=60 then '及格'
else '补考'
end 
from student;
end//

【会计算每个表达式的值，如果表达式值为true，则会执行后续语句】

例：

create procedure pro4(in para int)

begin

case para

when 0 then select * from student where score<=0;

when 1 then select * from student where score between 0 and 80;

else

select * from student where score>90;

end case;

end//

14.存储过程中的循环语句

while..[条件]..do..[语句]...end while

例

create procedure pro6(in para int)

begin

while para<6 do

update student set score=score+1 where score<60;

set para=para+1;

end while;

end//

repeate ...until...end repeat

例

create procedure pro7(in para int)

begin

repeat

update student set score=score-1 where score>60;

set para=para+1;

until para<6

end repeat;

end//

LOOP_LABLE:loop...if..then leave LOOP_LABLE

例

create procedure pro8(in para int)

begin

LOOP_LABLE:loop

update student set score=score+1 where score<60;

set para=para+1;

if para>=6

then leave LOOP_LABLE;

end if;

end loop;

end//