MySQL之存储过程之查询成绩等级

存储过程之查询成绩等级（Mysql）

`create procedure p_1()
select ‘Hello World!’;
#调用过程
call p_1()

create procedure p_2()
begin
declare v1 int default 0; – 声明局部变量
set v1 = 100; #变量的赋值
select v1;
end;
drop procedure p_2
call p_2()

create procedure p_3(in p int,out r int)
begin
declare v1 int default 0; #声明局部变量
set v1 = 100+p; #变量的赋值
set r = v1;
end;
drop procedure p_3
set @userv = 22;
call p_3(@userv ,@r);
select @r;

#case 结合查询的使用，用法相当于switch，也是做等 值判断
#依据学生的考试成绩划分等级，90优，80良，70一般，60及格，以下不及格
select s.name,c.coursename,sc.score,
case
when sc.score>=90 then ‘优’
when sc.score>=80 then ‘良’
when sc.score>=70 then ‘一般’
when sc.score>=60 then ‘及格’
else
‘不及格’
end
from student s,score sc,course c
where s.stuid=sc.stuid and c.courseid=sc.courseid

#依据学号得到学生姓名（用有参和有返回值的方式）
create procedure p_4(in no int,out nm varchar(20))
begin
select name into nm from student where stuid = no;
end;
set @no = 5;
call p_4(@no,@nm);
select @nm;

#转账的例子(有参有返回值，显式的事务处理方式)
create procedure p_5(in z1 int,in z2 int,in je int,out result varchar(20))
begin
declare t_error int default 0;
declare continue handler for sqlexception set t_error=1; #声明当sql执行出错时抛异常
start transaction;
update acc set balance=balance-je where id=z1;
update acc set balance=balance+je where id=z2;
if t_error = 0 then
commit;
set result=‘succes’;
else
rollback;
set result=‘fail’;
end if;
end;

call p_5(11,22,55,@r);
select @r;

select * from acc

`