一 实验题目
1．存储过程的定义和使用 2．触发器的创建与使用
二 实验目的
1．掌握存储过程的定义、执行和调用方法。 2．掌握触发器的创建与使用。
三 实验内容
1．存储过程的定义和使用
(1) 创建存储过程查找姓李的学生的选修课成绩信息。
(2) 创建存储过程，统计每个学生的选修课的总成绩，显示成绩最好的前3名学生成绩。 (3) 创建存储过程，查找某门课的最高分（带输入参数的存储过程）。 (4) 创建存储过程，统计某个同学的平均分并返回统计结果。（带输入和输出参数的存储过程） (5) 创建存储过程，统计某门课选修的人数，将人数返回。（带返回值的存储过程） (6) 创建存储过程，统计选修课程最多的学生的基本信息。（存储过程的嵌套）
2．触发器的创建与使用
(1) 定义一个BEFORE行级触发器credit_TRIGER，当为C表插入新的课程信息时，若学分大于5
分，自动修改为5分。
(2) 定义一个AFTER行级触发器，当SC表的成绩发生变动时，就自动在成绩变化表sc_log中增
加一条新的纪录，该新纪录包括：操作者名称、操作日期、操作类型。 (3) 建立一个DELETE触发器，针对于SC表，每次只能删除一条信息。
(4) 建立一个UPDATE的触发器，不允许用户更改学生学号，若更改学号，给出提示信息：学号
不允许更改。
(5) 执行相应的SQL语句，触发上述定义的触发器。 (6) 删除触发器credit_TRIGER。
四 实验步骤
1．存储过程的定义和使用 存储过程格式：
CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
创建存储过程查找姓李的学生的选修课成绩信息。
(1) 创建存储过程，统计每个学生的选修课的总成绩，显示成绩最好的前3名学生成绩。
SC表基本情况如图表 1所示：
图表 1
代码：
create proc p_sumscore as
select TOP 3 sum(Grade)as 总成绩 from SC group by Sno
exec p_sumscore 运行结果：
命令已成功完成。 显示结果如图表2：
图表 2总成绩前三名
(2) 创建存储过程，查找某门课的最高分（带输入参数的存储过程）。 代码：
create proc p_maxscore as
@course
select distinct max(Grade)as 最高成绩 from SC where Cno=@course 运行结果：
第 3 行: '@course' 附近有语法错误。 必须声明变量 '@course'。
必须声明变量；且create proc 必须为第一条语句 正确代码：
create proc p_maxscore(@course nchar(6)) as
select distinct max(Grade)as 最高成绩 from SC where Cno=@course group by Cno
exec p_maxscore'0001'
运行结果：
命令已成功完成。 显示结果如图表3：
图表 3课程号为0001的最高分为68
(3) 创建存储过程，统计某个同学的平均分并返回统计结果。（带输入和输出参数的存储过程） 代码：
create procedure p_avg @Sno char(10),@Result int output as
select @Result =(select avg(Grade)from SC where Sno=@Sno） 运行结果：
命令已成功完成。
declare @Result int,@Sno char(10) set @Sno ='0002'
exec p_avg @Sno ,@Result output
print '学号为'+@Sno+'的平均分：'+cast(@Result as char(2)) 运行结果：
(4) 创建存储过程，统计某门课选修的人数，将人数返回。（带返回值的存储过程） 代码：
create procedure p_num @Cno char(10),@Num int output as
select @Num =(select count(Cno)from SC where Cno=@Cno) 运行结果：
命令已成功完成。
学号为0002 的平均分：79 declare @Num int,@Cno char(4) set @Cno ='0002'
exec p_num @Cno ,@Num output
print '课程号为'+@Cno+'的人数：'+cast(@Num as char(2)) 运行结果：
图表 4选修课程号为0002的人数为2
(5) 创建存储过程，统计选修课程最多的学生的基本信息。（存储过程的嵌套） 代码：
CREATE PROCEDURE p_max As
select * from S
where Sno in(select top 1 Sno from SC group by Sno order by count(SC.sno)desc 运行结果：
命令已成功完成。 运行过程：exec p_max 显示结果如图表5：
图表 5李四选修课最多为三门
2．触发器的创建与使用 语句格式：
CREATE TRIGGER trigger_name ON { table | view }
[ WITH ENCRYPTION ] {
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] } [ WITH APPEND ]
[ NOT FOR REPLICATION ] AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ] [ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) { comparison_operator } column_bitmask [ ...n ] } ]
sql_statement [ ...n ] } }
(1) 定义一个BEFORE行级触发器credit_TRIGER，当为C表插入新的课程信息时，若学分大于5
分，自动修改为5分。 代码：
create trigger credit_TRIGER on C for insert as begin
UPDATE C set Ccredit=5 from C
where Ccredit>5 end;
运行结果：
命令已成功完成。
运行显示结果如图6
图表 6 C表增加触发器credit_TRIGER
(2) 定义一个AFTER行级触发器，当SC表的成绩发生变动时，就自动在成绩变化表sc_log中增
加一条新的纪录，该新纪录包括：操作者名称、操作日期、操作类型。 代码： 创建表格：
create table SC_log( SCname char(10), SCdata char(12), SCtype char(10) )
运行结果：
命令已成功完成。
创建触发器：
create trigger update_SC on SC after update as
insert into SC_log(SCname ,SCdata,SCtype)
values(user,convert(varchar(12),getdate(),1),'update') 运行结果：
update SC set Grade=66 where Sno='0001'、 运行结果：
（所影响的行数为 1 行） 运行显示结果如图7：
命令已成功完成。