实验五 触发器、存储过程的建立与使用

触发器、存储过程的建立与使用

实 验 五 触发器、存储过程的建立与使用

一、 实验目的:

理解触发器和存储过程的概念和作用，掌握创建和使用触发器、存储过程的方法，为后继学习和开发程序作准备。

二、 实验属性（验证性）

1．理解触发器、存储过程的原理及作用。

2．理解并掌握触发器的创建及使用。

3. 理解并掌握存储过程的定义及使用执行。

三、 实验仪器环境与要求

PC机，SQL SERVER2012

四、 实验要求

1．复习教材第五章和第八章相关内容，熟悉触发器和存储过程的创建语句。

2．熟悉SQL SERVER2012开发环境。

3．理解并掌握查看、修改和删除触发器的方法。

4．掌握验证触发器的效果。

5. 理解并掌握查看、修改和删除存储过程的方法。

6．掌握创建并执行存储过程的方法。

五、 实验原理

触发器、存储过程的建立与使用

SQL语言基本应用。

六、 实验步骤：

(1) 启动SQL SERVER2012。

(2) 附加数据库；

(3) 验证如下例子：

1 触发器

1、在学生表student上创建一个触发器，当在此表中删除数据后，弹出 请注意你删除了数据 。

create trigger delete_student1

on student after delete

as

print '请注意你删除了数据'

go

delete

from student

where sno='0603002'

2、接着再创建一个触发器，当删除某个人的数据后，弹出 请注意你删除了某个人的数据 。

create trigger delete_student2

on student after delete

as

go

delete

from student if '孙南' in (select sname from student where sname='孙南') begin print '请注意你删除了孙南数据' end

触发器、存储过程的建立与使用

where sname='孙南'

3、在student表上创建一个触发器，当插入数据时，年龄不小于15岁。

create trigger insert_student1

on student after insert

as

begin

declare @sage_tri real

select @sage_tri=(select sage

from inserted)

if @sage_tri<15

print '年龄不小于15岁'

end

insert

into student(sno ,sname,ssex,sage)

values('2014001','张三','男',21)

4、创建一个院系表Depts（院系名，学生人数）,再在这张表上创建一个触发器，当在student表中某一个院系插入一名学生时，Depts表中，该院系的人数自动加1。

create table Depts (sdept char(10) primary key,

stu_num char(10),

count int)

go

create trigger insert_student2 on student

after insert as

update Depts

set count=count+1

from inserted i join Depts d on i.sdept=d.sdept

go

insert

into student(sno ,sname,ssex,sage)

values('2014002','张五','男',22)

触发器、存储过程的建立与使用

5、利用存储过程，给Student表添加一条学生信息。 create proc add_student(@sno char(20),

@sname char(20),

@ssex char(10),

@sage int)

as

Insert

into student (sno,sname,ssex,sage)

values(@sno,@sname,@ssex,@sage)

add_student('2014002','王五','男',22)

6、利用存储过程Student、Course、SC表的连接中返回学生的学号、姓名、所选课程和成绩。

create proc Stu_Info1

as

select 学号=student.sno,

姓名=sname,

所选课程=cname,

成绩=grade

from student,course,sc

where student.sno=sc.sno and http://o=http://o

7、利用存储过程查找‘李勇’的学号、所选课程、成绩。 create proc find_stu @sname char(20)

as

select student.sno as 学号,

cname as 所选课程,

grade as 成绩

from student,sc,course

where sname=@sname

find_stu @sname='孙建'

8、利用存储过程查找姓’李’并且性别为‘男‘的学生的学号、所选课程、成绩

触发器、存储过程的建立与使用

create proc stu_info2

as

select student.sno as 学号,

cname as 所选课程,

grade as 成绩

from student,sc,course

where sname like '李%' and ssex='男'

9. 利用存储过程计算出平均成绩大于k分(0<k<100)的学生的学号、姓名。

create proc cal_grade @p_grade varchar(10),

@k int output

as

select avg(grade)

from sc,student

where student.sno=sc.sno and student.sno=@p_grade

having avg(grade)>@k