MySQL 5.5.31 procedure 的语法规则细节

1.不能使用return；

2.Delimiter //(分割符声明，没声明默认为分号（；））

3.在procedure外没法用declare，声明变量直接用 @变量名；

4.要显示字符串，直接使用select语句；

Delimiter //
Drop procedure if exists Disp_Student;

create procedure Disp_Student(in nSNO char(20), out SUM_CREDIT numeric(5,0), out AVG_GRADE numeric(5,0))
begin
select *
from Students
where Students.SNO=nSNO;

select avg(Grade) into AVG_GRADE
from SC natural join Courses
where SNO=nSNO and Credit>3;

select sum(Credit) into SUM_CREDIT
from SC natural join Courses
where SNO=nSNO;
end
//


call Disp_Student('201130580497',@sum_credit ,@avg_grade );
select @avg_grade,@sum_credit;

5. 视图不被允许在procedure中进行修改（alter），即使是temporary table......=。=

6.在procedure中对变量直接进行赋值，用set语句。

delimiter //

drop procedure if exists CAL_GPA;
create procedure  CAL_GPA(in nSNO char(20), out GPA numeric(5,2) )

begin
declare SUM_CREDIT numeric(5,0);
declare SUM_GPC numeric(5,0);

create temporary table if not exists temp(CREDIT numeric(5,2), Grade numeric(5,2),GPC numeric(5,2));


insert into temp
select Credit,Grade,NULL
from SC natural join Courses
where SNO=nSNO ;

select sum(Credit) into SUM_CREDIT
from temp;


update temp
set GPC=4*Credit
where Grade>=85;

update  temp
set GPC=3*Credit
where Grade>=75 and Grade<85;

update  temp
set GPC=2*Credit
where Grade>=60 and Grade<75;

update  temp
set GPC=1*Credit
where Grade<60;

select sum(GPC) into SUM_GPC
from temp;

set GPA=SUM_GPC/SUM_CREDIT;


drop temporary table if exists temp;
end
//

call CAL_GPA('201130580497',@GPA);
select @GPA;

 7.查看procedure使用

show procedure status;