实验六 存储过程和触发器

实验六存储过程和触发器
电子信息科学与技术罗滨志（120802010051）
一、实验目的
（1）理解存储过程和触发器的功能和特点；
（2）学会使用Transact-SQL编写存储过程和触发器的方法；
（3）学会如何使用管理平台创建存储过程和触发器；
（4）掌握存储过程的创建、执行与删除；
（5）掌握触发器的创建、修改和删除操作；
（6）理解可以使用存储过程和触发器来维护数据完整性。

二、实验准备
（1）了解存储过程的基本概念和类型。

（2）了解创建存储过程的Transact —SQL语句的基本语法。

（3）了解查看、执行、修改和删除存储过程的基本语法。

（4）了解触发器的基本概念和类型。

（5）了解创建触发器的Transact —SQL语句基本用法。

（6）了解查看、修改和删除触发器的Transact —SQL语句的用法。

三、实验内容和步骤
1．创建存储过程stu_info,执行时通过输入姓名，可以查询该姓名对应的学生的各科成绩。

SQL语句
create proc stu_info@sname varchar(20)
as select grade
from sc,student
where student.sno=sc.sno and sname=@sname
go
如图所示：
2. 使用studentsdb数据库中的student表，course表、sc表。

（1）创建一个存储过程stu_score，查询学号为2012001的学生的姓名，课程名称，分数。

SQL语句
create proc stu_score@sno varchar(20)
as select sname,cname,grade
from sc,student,course
where student.sno=sc.sno and o=o and student.sno=@sno go
如图所示：
（2）执行存储过程stu_score,查询2012001学生的姓名，课程名称，分数。

SQL语句
exec stu_score'200515001'
如图所示：
3. 使用student表，course表、sc表。

（1）创建一个带参数的存储过程stu_p_g,当任意输入一个学生的姓名时，将从3个表中返回该学生的学号，选修的课程名称和课程成绩。

SQL语句
create proc stu_p_g@sname varchar(20)
as select student.sno,cname,grade
from sc,student,course
where student.sno=sc.sno and o=o and sname=@sname
go
如图所示：
（2）执行存储过程stu_g_p，查询“刘卫平”的学号，选修课程和课程成绩。

SQL语句
exec stu_p_g'李晨'
如图所示：
4．使用student表。

（1）执行存储过程stu_en,查看返回学生情况。

SQL语句
create procedure stu_en WITH ENCRYPTION AS select*
FROM student_info
WHERE性别='男'
EXEC stu_en
如图所示：
（2）使用Transact —SQL语句DROP PROCEDURE删除存储过程stu_en。

SQL语句
DROP procedure stu_en
如图所示：
5．使用sc表。

（1）创建一个存储过程stu_g_r,当输入一个学生学号，通过返回输出参数获取该学生各门课程的平均成绩。

SQL语句
create proc stu_g_r@sno int,@stu_score int output
as
select@stu_score=AVG(grade)
from sc
where sno=@sno
如图所示：
（2）执行存储过程stu_g_r,输入学号2012002。

SQL语句
declare@stu_score int
exec stu_g_r'200515002',@stu_score output select@stu_score as平均成绩
如图所示：
（3）使用Transact—SQL语句DROP PROCDURE删除存储过程stu_en. SQL语句
DROP procedure stu_en
如图所示：
6.为sc表建立一个名为insert_g_tr的INSERT触发器，当用户向sc表中插入记录时，如果插入的是在course表中没有的课程编号，则提示用户不能插入记录，否则提示记录插入成功。

请进行插入测试，分别输入以下数据：
学号课程编号分数
2012003 003 76
2012111 009 69
观察插入数据时的运行情况，说明什么？
SQL语句
create trigger insert_g_tr
on sc
for insert
as
declare@cno char(20)
select@cno=cno from inserted
if not exists(select*from course)print'不能插入记录！'
else print'记录插入成功！'
go
如图所示：
7. 为course表创建一个名为del_c_tr的DELETE触发器，该触发器作用是如果在sc表中有该课程的记录则提示用户禁止删除course表中的记录，否则删除该课程记录并提示删除成功。

SQL语句
create trigger del_c_tr
on course
for delete
as
begin
declare@cno char(20)
if exists(select*from sc,deleted where o=o)
begin
print'禁止删除course表中的记录！'
rollback transaction
end
else
begin
select@cno=cno from deleted
delete from sc where cno=@cno
print'删除成功！'
end
end
go
如图所示：
8.为student表创建一个名为update_s_tr的UPDATE触发器，该触发器作用是如果sc表中有该学生的记录，则提示用户禁止更新student表中的“学号”字段的内容，否则更改用户学号，并提示更改成功。

SQL语句
create trigger update_s_tr
on student
for update
as
begin
declare@sno char(20)
if exists(select*from sc,updated where sc.sno=o)
begin
print'禁止更新！'
rollback transaction
end
else
begin
update sc
set sno=(select sno from inserted)
where sno=(select sno from deleted)
print'删除成功！'
end
end
go
如图所示：
11 .为course表创建一个名为update_sc的触发器，要求当修改course表的课程号时，sc表中相应的记录也自动修改。

SQL语句
create trigger update_sc
on Student
instead of update
as
if UPDATE(Grade)
begin
update SC
set Grade=(select Grade from inserted)where
Sno=(select Sno from inserted)and
Cno=(select Cno from inserted)
end
update Student
set Grade=40
where Sno='20110001'and Cno='002'
select*from Student
select*from SC
13.创建触发器，当修改或删除sc表中的成绩时，显示其原来的成绩。

SQL语句
create trigger test on s
after delete
as
begin
delete from sc where 学号in (select 学号from deleted)
end
Oracle：
create or replace trigger test
after delete on s
for each row
begin
delete from sc where 学号=:old.学号;
end;。