广西科技大学
计通学院
《数据库原理及应用》实验
实验四 存储过程和触发器
学生姓名:
学
班
号:
级:
指导老师:
专
业:计算机科学与技术
提交日期:2018 年 6 月 20 日
实验报告内容
一、 实验目的
1. 掌握 T-SQL 流控制语句。
2. 掌握创建存储过程的方法。
3. 掌握存储过程的执行方法。
4. 掌握存储过程的管理和维护。
5. 理解触发器的用途、类型和工作原理。
6. 掌握利用 T-SQL 语句创建和维护触发器的方法。
7. 掌握利用 SQL Server Management Studio 创建、维护触发
器的方法。
二、 实验内容(实验过程、代码和结果截图)
1. 创建简单存储过程
创建一个名为 stu_pr 的存储过程,该存储过程能查询出 051
班学生的所有资料,包括学生的基本信息、学生的选课信息
(含未选课同学的信息)。要求在创建存储过程前请判断该
存储过程是否已创建,若已创建则先删除,并给出“已删除!”
信息,否则就给出“不存在,可创建!”的信息。
T-SQL 语句:
if exists (select name from sysobjects where
name='stu_pr' and type='p')
Begin print '已删除!'
drop procedure stu_pr end
else print '不存在,可创建!'
go
create procedure stu_pr as
select * from Student
left join SC on Student.Sno=SC.Sno
left join Course on Course.Cno=SC.Cno
where Classno='051'
结果如图 1-1,图 1-2 所示:
T-SQL 语句:exec stu_pr
图 1-1
图 1-2
2. 创建带参数的存储过程
1) 创建一个名为 stu_proc1 的存储过程,查询某系、某姓名的
学生的学号、姓名、年龄,选修课程名、成绩。系名和姓名
在调用该存储过程时输入,其默认值分别为“%”与“林%”。
执行该存储过程,用多种参数加以测试。
T-SQL 语句:if exists (select name from sysobjects where
name='stu_proc1' and type='p')
begin
print '已删除!'
drop procedure stu_proc1 end
else print '不存在,可创建!'
go
create procedure stu_proc1
@Sdept char(8)='%',@Sname varchar(8)='林%'
as
select
Sdept,Student.Sno,Sname,DATEDIFF(YEAR,Birth,GETDATE()
)age,Cname,Grade
from Student,SC,Course
where Student.Sno=SC.Sno
and Course.Cno=SC.Cno
and Sdept like @Sdept
and Sname like @Sname
查询结果如图 2-1、图 2-2、图 2-3 所示:
T-SQL 语句:execute stu_proc1 '计算机系','林红'
图 2-1
T-SQL 语句:execute stu_proc1 '计算机系','张虹'
图 2-2
图 2-3
2) 创建一个名为 Student_sc 的存储过程,可查询出某段学号
的同学的学号、姓名、总成绩。(学号起始号与终止号在调
用时输入,可设默认值)。执行该存储过程。
T-SQL 语句:if exists (select name from sysobjects where
name='Student_sc'and type='p')
begin print '已删除!'
drop procedure student_sc end
else print '不存在,可创建!'
go
create procedure Student_sc @Sno1 char(8),@Sno2
char(8) as
select Student.Sno,Sname,SUM(Grade) 总 成 绩 from
Student,SC,Course
where Student.Sno=SC.Sno
and Course.Cno=SC.Cno
and Student.Sno>=@Sno1
and Student.Sno<=@Sno2
group by Student.Sno,Sname
结果如图 2-4、图 2-5 所示:
T-SQL 语句:execute Student_sc '20110000','20110003'
图 2-4
图 2-5
3. 创建带输出参数的存储过程
1) 创建一个名为 Course_average 的存储过程,可查询某门课
程考试的平均成绩。平均成绩可以输出,以便进一步调用。
T-SQL 语句:if exists (select name from sysobjects wh
ere name='course_average'and type='p')
begin print '已删除!'
drop procedure course_average
end
else print '不存在,可创建!'
go
create procedure course_average
@Cname nvarchar(20), @avg int output
=avg(grade)
from SC,Course
where Course.Cno=SC.Cno
and Cname=@Cname
group by SC.Cno,Cname
结果如图 3-1 所示:
as select @avg
图 3-1
T-SQL 语句:declare @avg int
execute course_average '高数',@avg output
print ' 该 课 程 的 平 均 成 绩 为 : '+cast(@avg as
nvarchar(20))
结果如图 3-2 所示:
图 3-2
2) 创建一个执行该存储过程的批处理,要求当平均成绩小于
60 分时显示信息为“xx 课程的平均成绩为:xx,其平均分
未达 60 分”,超过 60 分时显示信息为“xx 课程的平均成
绩为:xx”。
T-SQL 语句:create procedure course_average2
@cname varchar(10),@@avg smallint output
as select @@avg=avg(grade) from Course c,SC
where c.Cno=SC.Cno and Cname like @cname
结果如图 3-3 所示:
图 3-3
T-SQL 语句:declare @@course_average smallint
exec course_average2 '高数',@@course_average output
if @@course_average<60
begin
print '高数的平均成绩为:'+CAST(@@course_average
AS varchar(20))+',其平均分未达到分。'
end
else
print '高数的平均成绩为:'+CAST(@@course_average A
S varchar(20))+'。'
结果如图 3-4 所示:
图 3-4
4. 创建带重编译及加密选项的存储过程
创建一个名为 update_sc、并带重编译及加密选项的存储过程,
可更新指定学号、指定课程号的学生的课程成绩。(学号、课
程号由调用时输入)。
T-SQL 语句:if exists (select name from sysobjects where
name= 'update_sc' and type= 'p' )begin
print '已删除!'
drop procedure update_sc end
else print'不存在,可创建!'
go
create procedure update_sc
@sno
char(3),@grade
char(8),@cno
tinyint
With
RECOMPILE ,ENCRYPTION As
Update SC Set Grade=@grade
Where Sno=@sno and Cno=@cno
结果如图 4-1 所示:
T-SQL 语句:declare @sno char(8),@cno char(3),@grade
图 4-1
tinyint