实验四、视图和存储过程的定义及使用

一、实验名称

      视图和存储过程的定义及使用

二、实验目的

          掌握创建与查看视图的两种方法，加深对视图和 SQL Server 图表作用的理解。

          熟练利用代码的创建视图，并进行查看视图结果，通过实验考察 with check option 这一语句在视图 定义后产生的影响，包括对修改操作、删除操作、插入操作的影响。

          熟悉存储过程的定义和使用。 

1）SQL Server 视图的定义和使用。

2）SQL Server 存储过程的定义和调用。

三、实验内容

1）创建、查看、修改和删除视图。包括行列子集视图、带表达式的视图、with check option 视图、基于多个基表的视图、基于视图的视图、分组视图。

2）存储过程的定义和调用。包括无参和有参的存储过程。

四、实验任务及结果

1) 创建一个行列子集视图 v1，给出选课成绩合格的学生学号、所选课程号和成绩。

go

create view v1
as select *
  from SC
  where grade > 60
  with check option
  go
2) 创建学生姓名和所选修的课程名及成绩构成的视图。
create view v2
as select sname,cno,grade
     from SC,student
     where SC.sno = student.sno
3)利用视图查询所有选修“操作系统”课程的学生姓名。
create view v3
as select sname
    from SC,Course,student
   where SC.cno = Course.cno and SC.sno = student.sno and cname = '操作系统'
4) 插入元组（’S11’, ’C1’ ,58）到视图 V1 中。如果在视图的定义中，存在 with check 
option 子句对插入操作有什么影响？
go
insert into v1
values('D001','JS011',58)
go
   不能插入，因为有with check option这条语句，表示接下来对这个视图的增删改都必须满足where grade > 60这个条件，才能成功进行插入操作。
5) 将视图 V1（视图定义中包含 with check option）中，所有课程编号为 C1 的课程成
绩都减去 5 分，此操作数据库是否会正确执行，为什么？
update v1
set grade = grade - 5
where cno = 'JS001'
go

不会，在执行数据更新的时候，数据库会检查视图定义的条件，没有满足grade > 60,则拒绝执行操作
原因是当创建了这个视图后，因为有with check option这条语句，表示接下来对这个视图的增删改都必须满足where grade > 60这个条件，才能成功进行插入操作。
6) 将学生的学号，总成绩，平均成绩定义为一个视图。
create view v6
as select sno,sum(grade) as 总成绩,avg(grade) as 平均成绩
   from SC
   group by sno
   
7) 创建一个名称为 Proc_cc 的不带参数的存储过程，该存储过程的功能是从课程表中
查询学分大于 3 的课程信息
go
create procedure  Proc_cc 
as select Course.*
   from Course
   where ccredit > 3
go
8) 创建一个名称为 Proc_ci 的带参数的存储过程，该存储过程的功能是从课程表中插
入一条记录，新记录由参数提供。
go
create procedure  Proc_ci 
   @ccno char (10),@ccname char (16),@ccredit smallint,@ccpno char(10),@cctech char(20)
as
 insert into Course
  values( @ccno,@ccname,@ccredit,@ccpno,@cctech)
go
exec Proc_ci 'JS0019','电子商务概论','2','JS004','钟永友'
9) 创建一个名称为 Proc_sidefault 的具有参数默认值的存储过程，该存储过程的功能
是向学生表插入一条记录，新记录由参数提供，如果没有提供性别默认值时，由参
数的默认值代替。（有问题）
go
create procedure   Proc_sidefault
   @ssno char(10),@ssname char(8),@sssex char(2) = default,@ssage smallint,@ssdept char(30),@sstel char(13)
as
begin
 insert into student
  values( @ssno,@ssname,@sssex,@ssage,@ssdept,@sstel)
  if (@sssex is null)
  set @sssex = '男'
end
go
exec   Proc_sidefault  'D014','谢文','男',21,'经法','1792345678'
10) 创建一个名称为 Proc_cj 存储过程，该存储过程的功能为：用于根据学生学号信息，
查询该学生的平均成绩。
go
create procedure Proc_cj @xsxh char(10),@SC_sno char(10) output,@sc_aver smallint output
  as 
  begin
  select @SC_sno= sno,@sc_aver = avg(grade) 
   from SC 
   where sno = @xsxh
   group by sno
end
go
declare @xh char(10),@pjcj smallint
exec  Proc_cj 'D001',@xh output,@pjcj output
print '学号: '+@xh + ' 平均成绩：' + str(@pjcj)

注：如需后续实验报告，请留言。