##存储函数

#存储函数
#1.基础（与函数的不同）
#1）过程可以没有返回值，但是函数必须有：
#2）参数区别：函数只有传入的参数：但是过程不一样，过程有in,out,inout
#1. 根据输入的课程号，输出其课程名
create function coursename(cno char(6)) returns varchar(50)
reads sql data
begin
declare cname varchar(50);
case cno
when '010001' then set
cname='大学计算机基础';
when '010002' then set
cname='数据结构';
when '010003' then set
cname='数据库原理';
else set cname='非计算机类课程';
end case;
return cname;
end;
#如何调用函数
select coursename('010023')
#2.根据学号，返回其年龄
drop function get_age;
delimiter$$
create function get_age(sno char(10)) returns int
reads sql data
begin
declare myage int;
select year(noe())-year(stu_birth) into myage
from student where stu_no=sno;
return myage;
end$$;
delimiter;
#通过查询获得对应人名对应的学号，将其传给函数
select stu_no into @sno from student where stu_name='沈柯辛'
select get_age(@sno) as 年龄;
#2.根据姓名和课程名，查询其学号，姓名，课程名及成绩
delimiter$$
create procedure proc_stu_score(in sname char(10),in cname char(20))
reads sql data
begin
select student.stu_no,stu_name,course_name,score
from student join score on student.stu_no=score.stu_no 
join course on score.course_no=course.course_no
where stu_name=sname and course_name=cname;
end$$
delimiter;
#调用过程
call proc_stu_score('秦建兴','数据库原理')
#3.根据姓名，输出其平均分
drop procedure proc_stu_avg;
delimiter$$
create procedure proc_stu_avg(in sname char(10),out avg_score float)
reads sql data
begin
select avg(score) into avg_score
from student join score on student.stu_no=score.stu_no 
where stu_name=sname group by score.stu_no;
end$$
delimiter;
#调用过程
call proc_stu_avg('朱凡',@avg_score)
select round(@avg_score,2)

CREATE PROCEDURE proc_update_score(IN kch  char(6))
Modifies sql data 
BEGIN 
DECLARE xh char(10);
DECLARE cj float;
DECLARE state char(10);
DECLARE update_cj_cursor CURSOR FOR
  SELECT stu_no,score FROM score WHERE course_no=kch;
DECLARE continue HANDLER FOR 1329 SET state='遍历完成';
   OPEN update_cj_cursor;
REPEAT
    FETCH update_cj_cursor INTO xh,cj;
    IF (CJ>=55 AND CJ<60) THEN SET cj=60;
    END IF;
    UPDATE score SET score=cj WHERE stu_no=xh AND course_no=kch;
    UNTIL state='遍历完成'
  END REPEAT;
  CLOSE update_cj_cursor;
END $$
DELIMITER ;