数据库实验报告 (6)

一实验题目

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))

运行结果：

学号为0002 的平均分：79

(4)创建存储过程，统计某门课选修的人数，将人数返回。（带返回值的存储过程）

代码：

create procedure p_num @Cno char(10),@Num int output

as

select @Num =(select count(Cno)from SC

where Cno=@Cno)

运行结果：

命令已成功完成。

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