参考:
深入MySQL用户自定义变量:使用详解及其使用场景案例
MySQL存储过程_创建-调用-参数
简介
所谓存储过程是由过程化SQL语句编写的过程,该过程经过编译和优化处理后存储在数据库服务器中,在使用时可以随时调用。函数与存储过程类似,也是用户定义的持久化存储模块,不同之处在于函数需要指定返回类型。
使用案例
数据库的创建主要包括以下几个表:
系的信息表 Department(Dno,Dname,Daddress)
学生信息表Student(Sno, Sname, Ssex, Sage, Dno)
教师信息表Teacher (Tno, Tname, Ttitle, Dno)
课程信息表 Course (Cno, Cname, Cpno, Ccredit)
学生选课表SC(Sno,Cno,Grade)
教师授课表TC(Tno,Cno,Site)
无参存储过程
1、定义一个无参数存储过程DecreaseGrade,更新所有学生成绩,将其降低5%;并调用该存储过程
delimiter $
create procedure decreasegrade ()
begin
update SC set Grade = Grade*0.95;
end $
delimiter ;
call decreasegrade;
需要简单说明以下这里的一些参数
因为在MySQL中默认是以;
为运行结束的界定符的,而在begin...end
块可能有多个执行语句,而每个执行语句又需要以;
结束,所以需要使用delimiter
关键字将界定符暂时改为自定义的符号,这里我习惯改为$
,而在执行完毕以后使用delimiter ;
改回为;
调用一个无参存储过程只需要使用call procedurename
的形式即可。
带参存储过程
2、定义一个带输入参数存储过程IncreaseGrade,将课程号为1的所有学生成绩提升5%;要求课程号作为存储过程参数传入,并调用该存储过程
delimiter $
create procedure increasegrade ( in ccno int)
begin
update SC set Grade = Grade*1.05 WHERE Cno=ccno;
end $
delimiter ;
call increasegrade(1);
这里是带参数的存储过程,对于带参数的存储过程,分为以下三种:
IN输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
OUT输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
INOUT输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)
3、定义一个带有输入和输出参数的存储过程AverageStudentGrade,计算一个学生的所有选修课程的平均成绩,要求学号作为输入参数,计算结果——该生的所有选修课平均成绩作为输出参数;调用该存储过程,并输出计算结果
delimiter $
create procedure averagestudentgrade ( in paramsno VARCHAR(20), out paramgrade float)
begin
declare g float default 0.0;
select sg.ag into g
from (select Sno s, avg(grade) ag
from SC group by Sno) sg
where sg.s=paramsno;
end $
delimiter ;
call averagestudentgrade(20091000863, @g);
删除存储过程
4、删除存储过程IncreaseGrade、DecreaseGrades
DROP PROCEDURE DECREASEGRADE;
DROP PROCEDURE INCREASEGRADE;