mysql存储过程分隔符 mysql存储过程示例

存储过程

• 存储过程就是一条或多条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 '转出账户不存在或者没有存款';
   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 default 0; -- 游标结束的标志
 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!=1 do
   if v_grade>=90 and v_grade<=100
    then
     update sc set rank='A' where cno=v_cno and sno=v_sno;
   elseif v_grade>=80 and v_grade<90
    then
     update sc set rank='B' where cno=v_cno and sno=v_sno;
   elseif v_grade>=70 and v_grade<80
    then
     update sc set rank='C' where cno=v_cno and sno=v_sno;
   elseif v_grade>=60 and 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();