MySQL存储过程实例--NavicatPremium12
存储过程
存储过程就是⼀条或多条SQL语句的集合，当对数据库进⾏⼀系列复杂操作时，存储过程可以将这些复杂操作封装成⼀个代码块，以便重复使⽤，减少数据库开发⼈员的⼯作量
存储过程经编译和优化后存储在数据库服务器中，使⽤时只要调⽤即可
存储过程（procedure）类似于C语⾔
⽤来执⾏管理任务或应⽤复杂的业务规则
存储过程可以带参数，也可以返回结果
任务⼀
利⽤存储过程实现下⾯的应⽤：从账户1转指定数额的款项到账户2中，假设账户关系表为account(accountnum，total)， accountnum 为账号， total为余额。
1、创建accoimt表
create table account(
accountnum varchar(30) PRIMARY key,
total float
);
2、插⼊数据
insert into account values('a123456',50000);
insert into account values('b123456',500);
3、 定义存储过程
delimiter $
create procedure transfer(inAccount varchar(30),outAccount varchar(30),amount float)
proc_label:begin
declare totalDepositOut,totalDepostIn float;
declare inAccountnum varchar(30);
select total into totalDepositOut from account where accountnum=outAccount;
if totalDepositOut is null
then
select '转出账户不存在或者没有存款';
mysql存储过程使用
leave proc_label;
end if;
if totalDepositOut<amount
then
select '转出账户存款不⾜';
leave proc_label;
end if;
select accountnum into inAccountnum from account where accountnum=inAccount;
if inAccountnum is null
then
select '转⼊账户不存在';
leave proc_label;
end if;
update account set total=total-amount where accountnum=outAccount;
update account set total=total+amount where accountnum=inAccount;
commit;
end$
delimiter ;
4、 执⾏存储过程
样例1（异常）
call transfer('b123456','c123456',500);
样例2（异常）
call transfer('a123456','b123456',600);
样例3（异常）
call transfer('c123456','a123456',600);
样例4（成功转账）
call transfer('b123456','a123456',600);
任务⼆
在实验⼆创建的学⽣数据库基础上，使⽤存储过程实现业务规则的封装，完成以下功能：
⼀） 统计某门课的平均成绩；
⼆）按分数段[100,90]、(90,80]、(80,70]、(70,60]和(60,0]与等级制A、B、C、D、E的对应关系；将学⽣选课成绩从百分制改为等级制（即A、B、C、D、E）。
1、创建学⽣stu、课程course、学⽣-课程sc表
create table stu(
sno varchar(10) primary key,
sname varchar(20),
ssex varchar(6)
);
create table course(
cno varchar(10) primary key,
cname varchar(20)not null
);
create table sc(
cno varchar(10),
sno varchar(10),
grade float,
rank char(2),
foreign key(sno) references stu(sno),
foreign key(cno) references course(cno)
);
2、插⼊数据
-- stu表
insert into stu values('1815101','⼩明','男');
insert into stu values('1815102','⼩红','⼥');
insert into stu values('1815103','⼩刚','男');
-- course表
insert into course values('001','数据库');
insert into course values('002','⾼数');
-- sc表
insert into sc values('001','1815101',80,null);
insert into sc values('002','1815101',95,null);
insert into sc values('001','1815102',75,null);
insert into sc values('002','1815102',60,null);
insert into sc values('001','1815103',58,null);
insert into sc values('002','1815103',80,null);
针对统计某门课的平均成绩
3、创建统计某门课平均成绩的存储过程（带输出参数）
delimiter $
create procedure average_grade(in course_name varchar(20),out avg_grade float)
proc_label:begin
declare v_cname varchar(20);
declare v_cno varchar(10);
select cname,cno INTO v_cname,v_cno FROM course WHERE cname=course_name;
if v_cname is null
then
select '该课程不存在';
leave proc_label;
end if;
select avg(grade) into avg_grade from sc where cno=v_cno;
end $
delimiter ;
4、执⾏统计某门课平均成绩的存储过程
样例1（异常）
call average_grade('英语',@avg_grade);
样例2（成功统计）
call average_grade('数据库',@avg_grade);
select @avg_grade;
call average_grade('⾼数',@avg_grade);
select @avg_grade;
针对百分制改为等级制
5、创建百分制改为等级制的存储过程
如果sc表中没有等级这⼀属性列可以通过
alter table sc add rank char(2);
进⾏创建
delimiter $
create procedure grade_rank()
begin
declare v_cno varchar(10);--创建⽤于接收游标变量的变量（课程号）
declare v_sno varchar(10);--创建⽤于接收游标变量的变量（学号）
declare v_grade float;--创建⽤于接收游标变量的变量（课程成绩）
declare flag int default0;--游标结束的标志
declare g_rank cursor for select cno,sno,grade from sc;--声明游标
declare continue handler for not found set flag=1;--指定游标循环结束时的返回值 open g_rank;--打开游标
fetch g_rank into v_cno,v_sno,v_grade;--判断flag
--while循环及内容
while flag!=1do
if v_grade>=90and v_grade<=100
then
update sc set rank='A' where cno=v_cno and sno=v_sno;
elseif v_grade>=80and v_grade<90
then
update sc set rank='B' where cno=v_cno and sno=v_sno;
elseif v_grade>=70and v_grade<80
then
update sc set rank='C' where cno=v_cno and sno=v_sno;
elseif v_grade>=60and v_grade<70
then
update sc set rank='D' where cno=v_cno and sno=v_sno;
else
update sc set rank='E' where cno=v_cno and sno=v_sno;
end if;
fetch g_rank into v_cno,v_sno,v_grade;
end while;
close g_rank;--关闭游标
end $
delimiter ;
6、执⾏百分制改为等级制的存储过程
call grade_rank();

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。