admin 管理员组文章数量: 1184232
2023年12月25日发(作者：国家四大基础数据库)
数据库基础与实践实验报告
实验五 存储过程和触发器
班级：惠普测试142
学号：**********
姓名：***
日期：2016-11-14
1 实验目的：
1) 掌握SQL进行存储过程创建和调用的方法；
2) 掌握SQL进行触发器定义的方法，理解触发器的工作原理；
3) 掌握触发器禁用和重新启用的方法。
2 实验平台：
操作系统：Windows xp。
实验环境：SQL Server 2000以上版本。
3 实验内容与步骤
利用实验一创建的sch_id数据库完成下列实验内容。
1. 创建存储过程JSXX_PROC，调用该存储过程时可显示各任课教师姓名及其所教课程名称。
存储过程定义代码：
CREATE PROCEDURE JSXX_PROC
AS
SELECT tn 教师姓名,cn 所教课程 FROM T,TC,C WHERE = AND =
存储过程执行语句与执行结果截图：
EXECUTE JSXX_PROC
2. 创建存储过程XM_PROC，该存储过程可根据输入参数（学生姓名）查询并显示该学生的学号、所学课程名称和成绩；如果没有该姓名学生，则提示“无该姓名的同学”。
存储过程定义代码：
CREATE PROCEDURE XM_PROC @sname VARCHAR(100)
AS
BEGIN
IF EXISTS(SELECT NULL FROM S WHERE sn=@sname)
SELECT 学号,cn 课程,score 成绩 FROM S,SC,C WHERE = AND = AND
=@sname
ELSE
PRINT '无该姓名的同学。'
END
运行截图：
3. 创建存储过程XBNL_PROC，该存储过程可根据输入参数（专业名词，默认值为计算机专业），统计并显示该专业各年龄段男、女生人数。如果没有该专业，则显示“无此专业”。
存储过程定义代码：
CREATE PROCEDURE XBNL_PROC
@departName VARCHAR(30)='计算机',
@begin INT,
@end INT
AS
DECLARE @numOfBoys INT
DECLARE @numOfGirls INT
DECLARE @d# VARCHAR(3)
DECLARE @result VARCHAR(50)
BEGIN
SELECT @d# = dno FROM D WHERE dn=@departName
IF @d# IS NOT NULL
BEGIN
男'
女'
END
ELSE
END
SET @result='无此专业。'
PRINT @result
SET @result = @departName+'专业年龄在'+
CAST(@begin AS VARCHAR(3))+'-'+
CAST(@end AS VARCHAR(3))+'之间的男生有'+
CAST(@numOfBoys AS VARCHAR(3))+'人，'+'女生有'+
CAST(@numOfGirls AS VARCHAR(3))+'人'
SELECT @numOfGirls = COUNT(sno) FROM S WHERE age BETWEEN @begin AND @end AND dno=@d# AND sex='SELECT @numOfBoys = COUNT(sno) FROM S WHERE age BETWEEN @begin AND @end AND dno=@d# AND sex='执行结果：
4. 执行XM_PROC存储过程，查询“贾慧”同学的学号、所学课程名称和成绩。
存储过程调用语句及其执行结果截图：
EXEC XM_PROC '贾慧'
5. 如果学生表中无“贾慧”同学，则另查询一位学生表中出现过的学生姓名；如果学生表中有“贾慧”同学，则另查询一位学生表中没有的学生姓名。
存储过程调用语句及其执行结果截图：
EXEC XM_PROC '张明'
6. 执行XBNL_PROC存储过程，查询计算机专业各年龄的男、女生人数。（要求计算机专业必须有至少3个年龄段的男女学生）
存储过程调用语句及其执行结果截图：
EXEC XBNL_PROC @begin=21,@end=25 --默认：计算机
EXEC XBNL_PROC '计算机',18,25
EXEC XBNL_PROC '计算机',23,25
EXEC XBNL_PROC '计算机',20,21
7. 执行XBNL_PROC存储过程，输入一个专业表中没有的专业。
存储过程调用语句及其执行结果截图：
EXEC XBNL_PROC '音乐',18,25
8. 分别查看XBNL_PROC存储过程的一般信息。
执行语句及结果截图：
EXEC sp_help XM_PROC
9. 删除XM_PROC存储过程。
执行语句及结果截图：
DROP PROC XM_PROC
10. 创建班级表B(bno,bn,num)，三个属性分别表示班号，班名，人数；在学生S表中增加一个属性班号。
表创建语句：
--创建班级表：
CREATE TABLE B(
bno VARCHAR(3) PRIMARY KEY,
bn VARCHAR(20) UNIQUE NOT NULL,
num INT
)
--S表添加班级属性：
ALTER TABLE S ADD bno VARCHAR(3)
执行结果截图：
11. 创建触发器T_insertS，当向S表添加记录时，验证学生的班级号是否出现在B表中，如果不是则撤销S表添加记录的操作；，如果学生的班号在班级表中则自动修改相应班级的人数字段值。
触发器创建语句：
CREATE TRIGGER T_insertS
ON S
FOR INSERT
AS
BEGIN
DECLARE @bno VARCHAR(3)
DECLARE @sno VARCHAR(10)
SELECT @bno=bno FROM inserted
SELECT @sno=sno FROM inserted
IF EXISTS(SELECT NULL FROM B WHERE bno=@bno)
BEGIN
UPDATE B SET num=num+1 WHERE bno=@bno
END
PRINT '添加成功！'
END
BEGIN
DELETE FROM S WHERE sno=@sno
PRINT '添加失败！'
END
ELSE
向S表中插入一位学生，其班号不在B表中。测试触发器的功能截图：
插入记录前查询：
SELECT * FROM S WHERE sn='翠翠'
插入记录：
INSERT INTO S VALUES('S19','翠翠','女',18,'D2',3)
插入后查询：
SELECT * FROM S WHERE sn='翠翠'
向S表中插入一位学生，其班号在B表出现过。测试触发器的功能截图：
插入前查询：
SELECT * FROM S WHERE sn='翠翠'
插入记录：
INSERT INTO S VALUES('S19','翠翠','女',18,'D2',1)
插入后查询：
SELECT * FROM S WHERE sn='翠翠'
禁用T_insertS触发器语句；
ALTER TABLE S DISABLE TRIGGER T_insertS
向S表中插入一位学生，其班号不在B表中。测试触发器的功能截图：
INSERT INTO S VALUES('S20','翠花','女',18,'D2',3)
SELECT * FROM S WHERE sn='翠花'
启用T_insertS触发器语句；
ALTER TABLE S ENABLE TRIGGER T_insertS
向S表中插入一位学生，其班号不在B表中。测试触发器的功能截图：
INSERT INTO S VALUES('S21','翠花儿','女',19,'D2',3)
SELECT * FROM S WHERE sn='翠花儿'
12. 创建触发器T_updateS，当修改S表一位同学的班级字段值时，验证学生的班级号是否出现在B表中，如果不是则撤销对S表中该条记录的修改操作，如果修改后的班号在班级表中则自动修改相应班级的人数字段值。
触发器创建语句：
CREATE TRIGGER T_updateS
ON S
FOR UPDATE
AS
BEGIN
DECLARE @b# VARCHAR(3)
DECLARE @b#old VARCHAR(3)
DECLARE @numOld INT
SELECT @b#=bno FROM inserted
SELECT @b#old=bno FROM deleted
SELECT @numOld=num FROM B WHERE bno=@b#old
IF EXISTS(SELECT NULL FROM B WHERE bno=@b#)
END
BEGIN
END
BEGIN
END
UPDATE S SET bno=@b#old WHERE bno=@b#
PRINT '更新失败!'
IF (@numOld IS NOT NULL)
BEGIN
END
UPDATE B SET num=num+1 WHERE bno=@b#
UPDATE B SET num=num+1 WHERE bno=@b#
UPDATE B SET num=num-1 WHERE bno=@b#old
ELSE
PRINT '更新成功!'
ELSE
修改S表中一位学生的班级号，修改后的班号不在B表中。测试触发器的功能截图：
UPDATE S SET bno='88' WHERE sno='S1'
修改S表中一位学生的班级号，修改后的班号在B表中。测试触发器的功能截图：
SELECT sno AS '学号',bno AS '班级编号' FROM S WHERE sno='S18'
SELECT bno '班级编号',num AS '班级人数' FROM B
UPDATE S SET bno='8' WHERE sno='S18'
SELECT sno AS '学号',bno AS '班级编号' FROM S WHERE sno='S18'
SELECT bno '班级编号',num AS '班级人数' FROM B
4 深入思考与讨论
1） 请按自己的理解，说明一下触发器的工作原理。
触发器是对INSERT、UPDATE、DELETE等事件的处理。当满足触发器的触发条件时（进行插入、更新等操作），数据库系统就会执行触发器中定义好的程序语句。
2） 请定义一个视图V_T，该视图体现了职称是副教授的老师的编号、姓名、性别和所教课程编号、课程名的信息。尝试对视图V_T进行数据插入，例如，插入编号为t111的男教师李晨副教授讲授编
号为c11的“信息安全”课的信息，观察是否能成功。请自行编写一个该视图的触发器，使得定义触发器后，用户可以对视图V_T进行上述数据记录的插入。注意：所插入的教师和课程可能从未出现在教师表和课程表中。
创建视图：
CREATE VIEW V_T
AS
SELECT AS '教师编号' ,tn AS '姓名',sex AS '性别', AS '课程编号',cn AS '课程名'
FROM T,TC,C
WHERE = AND = AND ='副教授'
插入记录：
INSERT INTO V_T VALUES('T111','李晨','男','C11','信息安全')
由图可知，不能插入（影响多个表）。
创建触发器：
CREATE TRIGGER T_insertV_T
ON V_T
AS
BEGIN
END
IF NOT EXISTS(SELECT NULL FROM T,inserted WHERE tno=inserted.教师编号 AND tn=inserted.
INSERT INTO T(tno,tn,sex,prof) SELECT 教师编号,姓名,性别,'副教授' FROM inserted
INSERT INTO C(cno,cn) SELECT 课程编号,课程名 FROM inserted
INSTEAD OF INSERT
姓名)
IF NOT EXISTS(SELECT NULL FROM C,inserted WHERE cno=inserted.课程编号)
INSERT INTO TC(tno,cno) SELECT 教师编号,课程编号 FROM inserted
插入记录：
SELECT * FROM V_T
INSERT INTO V_T VALUES('T15','范冰冰','男','C11','信息安全')
SELECT * FROM V_T
由图可知，成功插入记录。
版权声明：本文标题：存储过程和触发器(数据库实验5) 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://roclinux.cn/p/1703437641a451368.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论