在Student_Course資料庫上,創建存儲過程實現:計算每門課程的平均分,將平均分低於70分的課程號、課程名、平均分存入表fail_course中
DELIMITER $$ USE `student-course` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `cour_ave_cursor_procedure`() BEGIN declare aveno char(11); declare avename char(10); declare avegrade float; declare flag int; declare courave cursor for select course.cno,cname,avg(grade) from course,sc where course.cno=sc.cno group by course.cno; DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=1; set flag=0; drop table if exists `student-course`.fail_course; create table fail_course (aveno char(11),avename char(30), avegrade float); open courave; while not flag do fetch courave into aveno,avename,avegrade; if (not flag) then if (avegrade<70) then insert into fail_course() values(aveno,avename,avegrade); end if; end if; end while; close courave; END $$ DELIMITER ;