视图、存储过程与触发器

实验目的：掌握视图的创建方法，掌握 MySQL 变量、流程控制等复合语句的使用；掌握

存储过程及触发器，了解并运用数据库应用编程的基本技术

实验内容与步骤：

1. 视图的创建与应用

为表 sc 创建一个视图 s_grade(sno,sname,cou_cnt,avg_grade,fail_cnt)，包括有各个学生学号、

姓名、选修课程的门数、平均分及不及格门数。

create or replace view s_grade(sno,sname,cou_cnt,avg_grade,fail_cnt)

as

select a.sno,sname,count(a.grade),avg(a.grade),count(c.grade)

from sc a

left outer join  

(select *from sc b  

where b.grade<60) as c on a.sno=c.sno and a.cno=c.cno

left outer join stu on a.sno=stu.sno

group by a.sno;

分析说明，该视图是否是行列子集视图。

  答 该视图不是行列子集视图，因为此视图不是由单个表导出，此视图涉及了stu 和 sc 表

存储过程、存储函数的创建与使用

创建存储过程 P1，查询学生表所有信息；运行之。

delimiter $$

create procedure p1()

begin

select *from stu;

end $$

delimiter ;

call p1;

创建存储过程 P2，输入某学号，查询该学生的所有信息（包括选课成绩），运行之

delimiter $$

create procedure p2(stusno char(5))

begin

select *from stu inner join  sc on  sc.sno=stu.sno where stu.sno=stusno;

end $$

delimiter ;

call p2('S001')

创建存储过程 P3，输入某学号，查询学生的姓名及所就读专业，运行之

 delimiter $$

 create procedure p3(stusno char(5))

 begin

 select sname,mname

 from stu left outer join major on stu.mno=major.mno

 where stu.sno=stusno;

 end $$

 delimiter ;

 call p3('S002');

创建存储过程 P4，输入某课程名，查询该课程的平均分，选课人数并输出之。运行之

 drop procedure p4;

 delimiter &&

 create procedure p4(couname varchar(30))

 begin

 select avg(grade),count(sno) count from

 sc right outer join cou on sc.cno=cou.cno

 where cou.cname=couname

 group by cou.cno;

 end &&

 delimiter ;

 call p4('高等数学');

创建存储过程 P5，输入某学号，完成该生的退学业务处理（即删除某学号学生的所有信

息），运行之。

delimiter $$

create procedure p5(stusno char(5))

begin

 delete from sc where sc.sno=stusno;

 delete from stu where stu.sno=stusno;

end $$

delimiter ;

call p5('S401');

创建存储过程 P6，输入某学号，为该学生插入一条选课记录（输入参数），并统计当前

选了几门课，返回选课门数。运行存储过程，显示返回结果值。

要求：存储过程中要检查插入的选课记录是否已经存在，如不存在才进行插入操作，如已经

存在，则不插入，并显示提示信息。

drop procedure p6;

delimiter $$

create procedure p6(stusno char(5),coucno char(5),cougrade decimal(4,1),out flag int,out count int)

begin

     set flag=0;

     if exists(select *from stu where sno=stusno) and

        exists(select *from cou where cno=coucno)  then

        insert into sc

             values(stusno,coucno,cougrade);

             set flag=1;

             end if;

             

             select count(sc.cno) count from sc

             where sc.sno=stusno

             group by sc.sno;

end $$

delimiter ;

call p6('S008','C007',65,@flag,@count);

select @flag;

创建存储过程 P7，输入某学号，查询某学生的在每个学期所学课程门数、平均分以及不

及格的课程门数。

要求：MySQL 存储过程不能使用游标返回结果集，考虑在存储过程中创建一个临时表用来存

放该学生在每个学期所学课程门数、平均分以及不及格的课程门数。运行存储过程 P7 后，

可查看该临时表以显示查询结果集。临时表在当前用户会话期间都存在，在创建时如已经存

在则无需新建。

delimiter $$

create procedure  p7 (stusno char(5))

begin

create temporary table if not exists tmpTable(

tempsno char(5),

totalCourse tinyint,

unCourse tinyint,

AvgGrade decimal(4,1)

);

insert into tmpTable(tempsno,totalCourse,unCourse,AvgGrade)

  select a.sno,count(a.grade),count(b.grade),avg(a.grade) from sc a

  left outer join sc b on a.sno=b.sno and a.cno=b.cno and b.grade<60

  where a.sno=stusno;

  end $$

  delimiter ;

  call p7('S001');

  select *from tmpTable;

  call p7('S002');

  call p7('S003');

  call p7('S004');

  call p7('S005');

select *from tmpTable;

创建存储过程 P8，查询每个学生在每个学期所学课程门数、平均分以及不及格的课程门

数。

要求：使用游标，取出 stu 表中每个学生学号，逐个学生调用存储过程 P7，将每个学生的

每个学期的课程门数、平均分以及不及格的课程门数插入到临时表中。最终查询该临时表显

示结果集。

注意：临时表在当前用户会话期间都存在，第一次调用 P7 之前，应先删除该临时表，运行

存储过程 P8 后，将创建的临时表删除。

drop temporary table tmpTable;

drop procedure p8;

delimiter $$

create procedure p8()

begin

declare stuno char(5);

declare done tinyint default 0;

declare cur_stu cursor for  

select sno from stu;

declare continue handler for not found set done=1;

create temporary table if not exists tmpTable(

tempsno char(5),

totalCourse tinyint,

unCourse tinyint,

AvgGrade decimal(4,1)

);

open cur_stu;

read_loop : loop

fetch cur_stu into stuno;

if done then leave read_loop;

end if;

call p7(stuno);

end loop read_loop;

close cur_stu;

end $$

delimiter ;

call p8;

select *from tmpTable;

创建存储函数 F9，实现学生学籍状态判定，若学生不及格的课程数超过其已学的课程数

的一半时，则留级，返回 0，否则返回 1。

要求：在 select 查询语句中使用该函数，显示所有留级的学生记录。

【注意】为测试存储过程和存储函数的创建是否正确，需要根据具体题目要求自行对表数据

进行修改。

delimiter $$

create function F9(stusno char(5))

returns tinyint

begin

declare flag tinyint default 1;

declare unCourse tinyint;

declare allCourse tinyint;

select count(grade) from sc where grade<60 and sno=stusno into unCourse;

select count(grade) from sc where sc.sno=stusno into allCourse;

if

    unCourse*2>allCourse

then  set flag=0;

end if;

return flag;

end $$

delimiter ;

select sno,F9(sno) from stu where F9(sno)=0;

在 stu 表 上 增 加 一 个 总 学 分 的 字 段 totalcredit 。 在 sc 表 上 创 建

insert/delete/update 触发器，当向 sc 表中插入/删除/修改一个学生的成绩时，修改 stu

表中该学生获得的总学分，及格的成绩才能添加的课程的学分。

insert 触发器

drop trigger if exists tri_updt_totalcredit;

delimiter $$

create trigger tri_updt_totalcredit

after insert

on sc for each row

begin

declare c int;

--  declare 定义变量  只在begin和end中间有效   

select credit from cou where cou.cno=new.cno into c;

--  找到课程对应学分

if (new.grade>=60)  then

update stu

set stu.totalcredit=ifnull(stu.totalcredit,0)+c where stu.sno=new.sno;

end if;

end $$

delimiter ;

delete 触发器

drop  trigger if exists tri_dele_totalcredit;

delimiter $$

create trigger tri_dele_totalcredit

after delete

on sc for each row

begin

declare c int;

select credit from cou where cou.cno=old.cno into c;

if(old.grade>=60)

then update stu

set stu.totalcredit=stu.totalcredit-c where stu.sno=old.sno;

end if;

end $$

delimiter ;

update触发器

drop trigger if exists tri_update_totalcredit;

delimiter $$

create trigger tri_update_totalcredit

after update

on sc for each row

begin

declare c int;

select  credit from cou where old.cno=cou.cno into c;

if(old.grade>=60 and new.grade<60)

then update stu

set stu.totalcredit=stu.totalcredit-c where stu.sno=old.sno;

elseif(old.grade<60 and new.grade>=60)

then update stu

set stu.totalcredit=stu.totalcredit+c where stu.sno=old.sno;

end if;

end $$

delimiter ;

实验总结：

1、视图可以理解为观察数据的窗口，简化了很多多表连接的查询过程

2、触发器式一种保证数据完整性的方法，是由事件来触发，十分方便

3、在触发器的测试过程中，经常出现以下报错：

不是所有的语句都可以通过修改而使这种报错消失

解决的办法：把触发器的构造语句和测试语句放到华为云数据库上运行即可