数据库实验报告5、)

实验五存储过程的应用
1 实验要求与目的
● 掌握利用查询分析器或对象资源管理器创建存储过程
● 掌握存储过程的执行与维护
● 理解使用存储过程来维护数据完整性
2 实验内容
本次实验所用的数据库名为TEST。

数据库主要包括的数据表为：学生、课程和成绩，其创建脚本如下：
CREATETABLE studentinfo
(学号char(10),姓名char(8),性别char(2),年级int,出生日期datetime)
GO
CREATETABLE course
(课程号char(4),课程名varchar(20),学分int)
GO
CREATETABLE stumark
(考试号char(10),学号char(10),
笔试成绩decimal(18,1),机试成绩decimal(18,1))
GO
要求：在TEST数据库中完成如下操作。

（1）创建一个能向创建学生表中插入一条记录的存储过程Insert_student,该过程需要5个参数，分别用来传递学号、姓名、性别、年级、出生日期5个值。

写出执行存储过程Insert_student的SQL语句，向学生表中插入一个新同学，并提供相应的实参值（实参值自定）。

//创建存储过程
Create procedure Insert_student
@xh char(10),@xm char(8),@sex char(2),@nj int,@csrq datetime
As
Begin
Insert into studentinfo(学号,姓名,性别,年级,出生日期)
Values (@xh,@xm,@sex,@nj,@csrq)
End
//执行存储过程
exec insert_student '20040101','张小明','男',2004,'1995-01-01'
（2）创建一个向课程表中插入一门新课程的存储过程Insert_course，该存储过程需要三个参数，分别用来传递课程号、课程名、学分，但允许参数“学分”的默认值为2，即当执行存储过程Insert_course时，未给参数“学分”提供实参值时，存储过程将按默认值2进行运算。

Create procedure Insert_course
@cno char(4),@cname varchar(20),@score int=2
As
Begin
Insert into course (课程号,课程名,学分) values (@cno,@cname,@score)
end
（3）执行存储过程Insert_course，向课程表Course中插入一门新课程。

分两种情况写出相应的SQL命令：
①提供三个实参值执行存储过程Insert_course。

Exec Insert_course @cno=’c001’,@cname=’数据库原理与应用’,1
②只提供二个实参值执行存储过程Insert_course，即：不提供与参数“学分”对应的实参值。

Exec Insert_course @cno=’c002’,@cname=’Java Web程序设计’
（4）创建一个名为Query_stusystem的存储过程，该存储过程的功能是根据学号查询学生表中某一学生的姓名、年级、性别及出生日期。

执行存储过程Query_stusystem，查询学号为”20060201”的学生的姓名、班级号、性别及出生日期。

写出完成此功能的SQL命令。

Create procedure Query_stusystem
@xh char(10)
As
Begin
Select 姓名,年级,性别,出生日期from studentinfo where 学号=@xh
End
Exec Query_stusystem ‘20060201’
（5）修改存储过程Query_stusystem，增加自定义处理错误信息，用来提示指定的学生学号不存在。

Alter procedure Query_stusystem
@xh char(10)
As
Begin
if (select COUNT(*) from studentinfo where 学号=@xh )=0
begin
raiserror('该学生不存在',16,1)
return
end
else
Select 姓名,年级,性别,出生日期from studentinfo where 学号=@xh
End
（6）创建存储过程，查看本次考试平均分以及未通过考试的学员名单。

并执行该存储过程。

create proc chakan
as
begin
select AVG(笔试成绩) as 笔试平均分,AVG(机试成绩) as 机试平均分from stumark
select 学号,姓名from studentinfo
where 学号in
(select 学号from stumark where 笔试成绩>=60 and 机试成绩>=60) end
（7）修改上例6：由于每次考试的难易程度不一样，每次笔试和机试的及格线可能随时变化（不再是60分），这导致考试的评判结果也相应变化。

并执行该存储过程。

分析：上述存储过程添加2个输入参数@writtenPass（笔试及格线）和
@labPass （机试及格线）
Alter proc chakan
@writtenPass decimal(18,1),@labPass decimal(18,1)
as
begin
select 学号,姓名from studentinfo
where 学号in
(select 学号from stumark where 笔试成绩>=@writtenPass and 机试成绩>@labPass)
end。