实验七 存储过程和触发器

实验七 存储过程和触发器

一、实验目的：理解存储过程概念、类型；掌握各种存储过程创建方法和查看、修改、删除存储过程方法。 二、实验内容及步骤

1、使用T-SQL语句创建存储过程 （1）创建不带参数存储过程

1）创建一个从student表查询学号为95002学生信息的存储过程proc_1，其中包括学号、姓名、性别、出生日期、系别等；调用过程proc_1查看执行结果。 use 选课系统 go

create proc proc_1 as

select sno,sname,ssex,birthday,sdept from student

where sno=95002

执行：

use 选课系统 go

exec proc_1

2）在选课系统数据库中创建存储过程proc_2，要求实现如下功能：查询学分为4的课程学生选课情况列表，其中包括学号、姓名、性别、课程号、学分、系别等。调用过程proc_2查看执行结果。 use 选课系统 go

create proc proc_2 as

select student.sno,sname,ssex,course.cno,course.credit,sdept from student,sc,course

where student.sno=sc.sno and sc.cno=course.cno and course.credit=4; 执行：

use 选课系统 go

exec proc_2

（2）创建带参数存储过程

创建一个从student表中按学生学号查询学生信息的存储过程proc_3.其中包括：学号、姓名、性别、出生日期、系别等。查询学号通过执行语句中输入。 use 选课系统 go

create proc proc_3 @sno1 int as

select sno,sname,ssex,birthday,sdept from student

where sno=@sno1

执行：use 选课系统

go

exec proc_3 95003

（3）创建带输出参数存储过程

创建一个从sc表查询一门课程考试成绩总分存储过程proc_4，要求查询的课程号通过执行语句中的输入参数@course_no传递给存储过程，

s@sum_grade 作为输出参数用来存放查询得到的总分。执行此过程时用declare声明临时变量@sum_grade_output接收查询的总分。

参考：

use 选课系统 go

create proc student_proc4

@startdate datetime,@enddate datetime,@recordcount int output As

If @startdate is null or @enddate is null Begin

Raiserror(‘null value are invalid’,5,5) Return End

Select * from 学生信息表

Where birthday between @startdate and @enddate Select @recordcount=@@rowcount go

执行语句： use 选课系统 go

declare @recordn int

exec student_proc4 '01/01/1988','01/01/1990',@recordn output print 'the order count is:'+str(@recordn)

（4）创建带重编译和加密的存储过程

在sc表中创建一个带创建带重编译和加密的存储过程proc_exp5，要求实现如下功能：输入学生学号、根据该生选课的总学分显示提示 信息，如果总学分<9,则显示“此学生学分不足”，否则显示“此学分已足”。调用过程proc_4查看执行结果。 参考：

Use 选课系统 Go

Create proc proc_exp4 @no char(5) As

Declare @savg tinyint /* inyint从 0 到 255 的整型数据。存储大小为 1 字节。*/ Select @savg=avg(grade) from sc Where sno=@no If @savg>60

Print '该学生综合成绩合格,成绩为'+convert(char(2),@savg)+'分' Else

Print '该学生综合成绩不合格,成绩为'+convert(char(2),@savg)+'分' go 执行：

Use 选课系统 Go

Exec proc_exp4 ‘95001’

2、使用T—SQL语句查看、修改和删除存储过程 （1）查看存储过程proc_2、proc_5定义 Exec sp_helptext proc_2 Exec sp_helptext proc_3 （2）删除存储过程proc_1 Drop proc proc_1

3、使用T—SQL语句实现触发器定义。

（1）为表sc创建一个插入触发器，当向表sc中插入数据时，如果成绩grade大于等于60分，该生就能得到相应学分， 否则得不到学分。

Use 选课系统 Go

If exists(select name from sysobjects where name= 'credit_insert'and type='tr')

Drop trigger credit_insert go

create trigger credit_insert on sc for insert as

declare @credit0 tinyint declare @grade0 tinyint

select @grade0=inserted.grade from inserted

select @credit0=course.credit from course,inserted where course.cno=inserted.cno if (@grade0>=60) begin

update sc set sc.credit=@credit0 from sc,inserted

where sc.cno=inserted.cno and sc.sno=inserted.sno end

go 执行： Use 选课系统 Go

Insert into sc(sno,cno,grade,credit) Values (95001,’001’,80,0)

（2）为表student创建一个删除触发器，当删除表student中一个学生的资料时，将表sc中相应的成绩数据删除。

use 选课系统 go

If exists(select name from sysobjects where name= 'student_delete'and type='tr')

Drop trigger student_delete go

create trigger student_delete on Student for delete as

declare @Sno1 int

select @Sno1=deleted.Sno from deleted delete from SC where SC.Sno=@Sno1

（3）为表sc创建一个更新触发器，当更改表sc的成绩数据时，如果成绩由原来的小于60分更改为大于60分，该学生能得到相应学分，如果由原来的大于

等于60更改为小于等于60分，则相应学分改为0.

use 选课系统 go

If exists(select name from sysobjects where name= 'credit_update'and type='tr')

Drop trigger credit_update Go

create trigger credit_update on Sc for update as

declare @credit0 tinyint declare @grade0 tinyint

select @grade0=inserted.grade from inserted

select @credit0=course.credit from course,inserted where course.cno=inserted.cno if (@grade0>=60) begin

update sc set sc.credit=@credit0 from sc,inserted

where sc.cno=inserted.cno and sc.sno=inserted.sno

end else begin

update sc set sc.credit=0 from sc,inserted

where sc.cno=inserted.cno and sc.sno=inserted.sno end 执行： use 选课系统 go

update sc set grade=35

where sno=95001 and cno=‘001’

三、实验总结及心得

四、实验结果（在下一页，要求打印出来附在报告后）

本文来源：https://www.bwwdw.com/article/tajx.html