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//