1、存储过程和触发器数据库实验5数据库基础与实践实验报告实验五 存储过程和触发器 班级:惠普测试142 学号:* *日期:2016-11-14 1 实验目的:1)掌握SQL进行存储过程创建和调用的方法;2)掌握SQL进行触发器定义的方法,理解触发器的工作原理;3)掌握触发器禁用和重新启用的方法。2 实验平台:操作系统:Windows xp。实验环境:SQL Server 2000以上版本。3 实验内容与步骤利用实验一创建的sch_id数据库完成下列实验内容。1.创建存储过程JSXX_PROC,调用该存储过程时可显示各任课教师姓名及其所教课程名称。存储过程定义代码:CREATE PROCEDURE
2、JSXX_PROCASSELECT tn 教师姓名,cn 所教课程 FROM T,TC,C WHERE T.tno=TC.tno AND TC.cno=C.cno存储过程执行语句与执行结果截图:EXECUTE JSXX_PROC2.创建存储过程XM_PROC,该存储过程可根据输入参数(学生姓名)查询并显示该学生的学号、所学课程名称和成绩;如果没有该姓名学生,则提示“无该姓名的同学”。存储过程定义代码:CREATE PROCEDURE XM_PROC sname VARCHAR(100)ASBEGINIF EXISTS(SELECT NULL FROM S WHERE sn=sname)SELE
3、CT S.sno 学号,cn 课程,score 成绩 FROM S,SC,C WHERE SC.cno=C.cno AND SC.sno=S.sno AND S.sn=snameELSEPRINT 无该姓名的同学。END运行截图:3.创建存储过程XBNL_PROC,该存储过程可根据输入参数(专业名词,默认值为计算机专业),统计并显示该专业各年龄段男、女生人数。如果没有该专业,则显示“无此专业”。存储过程定义代码:CREATE PROCEDURE XBNL_PROC departName VARCHAR(30)=计算机, begin INT, end INTAS DECLARE numOfBoy
4、s 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 SELECT numOfBoys = COUNT(sno) FROM S WHERE age BETWEEN begin AND end AND dno=d# AND sex=男 SELECT numOfGirls = COUNT(sno) FROM S WHERE age BETWEEN begi
5、n AND end AND dno=d# AND sex=女 SET result = departName+专业年龄在+ CAST(begin AS VARCHAR(3)+-+ CAST(end AS VARCHAR(3)+之间的男生有+ CAST(numOfBoys AS VARCHAR(3)+人,+女生有+ CAST(numOfGirls AS VARCHAR(3)+人 END ELSE SET result=无此专业。 PRINT resultEND执行结果:4.执行XM_PROC存储过程,查询“贾慧”同学的学号、所学课程名称和成绩。存储过程调用语句及其执行结果截图:EXEC XM_P
6、ROC 贾慧5.如果学生表中无“贾慧”同学,则另查询一位学生表中出现过的学生姓名;如果学生表中有“贾慧”同学,则另查询一位学生表中没有的学生姓名。存储过程调用语句及其执行结果截图:EXEC XM_PROC 张明6.执行XBNL_PROC存储过程,查询计算机专业各年龄的男、女生人数。(要求计算机专业必须有至少3个年龄段的男女学生)存储过程调用语句及其执行结果截图:EXEC XBNL_PROC begin=21,end=25 -默认:计算机EXEC XBNL_PROC 计算机,18,25EXEC XBNL_PROC 计算机,23,25EXEC XBNL_PROC 计算机,20,217.执行XBNL
7、_PROC存储过程,输入一个专业表中没有的专业。存储过程调用语句及其执行结果截图:EXEC XBNL_PROC 音乐,18,258.分别查看XBNL_PROC存储过程的一般信息。执行语句及结果截图:EXEC sp_help XM_PROC9.删除XM_PROC存储过程。执行语句及结果截图:DROP PROC XM_PROC10.创建班级表B(bno,bn,num),三个属性分别表示班号,班名,人数;在学生S表中增加一个属性班号。表创建语句:-创建班级表:CREATE TABLE B( bno VARCHAR(3) PRIMARY KEY, bn VARCHAR(20) UNIQUE NOT N
8、ULL, num INT)-S表添加班级属性:ALTER TABLE S ADD bno VARCHAR(3)执行结果截图:11.创建触发器T_insertS,当向S表添加记录时,验证学生的班级号是否出现在B表中,如果不是则撤销S表添加记录的操作;,如果学生的班号在班级表中则自动修改相应班级的人数字段值。触发器创建语句:CREATE TRIGGER T_insertS ON S FOR INSERTAS BEGIN DECLARE bno VARCHAR(3) DECLARE sno VARCHAR(10) SELECT bno=bno FROM inserted SELECT sno=sno
9、 FROM inserted IF EXISTS(SELECT NULL FROM B WHERE bno=bno) BEGIN UPDATE B SET num=num+1 WHERE bno=bno PRINT 添加成功! END ELSE BEGIN DELETE FROM S WHERE sno=sno PRINT 添加失败! END END向S表中插入一位学生,其班号不在B表中。测试触发器的功能截图:插入记录前查询:SELECT * FROM S WHERE sn=翠翠插入记录:INSERT INTO S VALUES(S19,翠翠,女,18,D2,3)插入后查询:SELECT *
10、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
11、 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_up
12、dateSON S FOR UPDATEASBEGIN 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#) BEGIN IF (numOld IS NOT NULL) BEGIN UPDATE B SET num=num+1 WHERE
13、 bno=b# UPDATE B SET num=num-1 WHERE bno=b#old END ELSE UPDATE B SET num=num+1 WHERE bno=b# PRINT 更新成功! END ELSE BEGIN UPDATE S SET bno=b#old WHERE bno=b# PRINT 更新失败! ENDEND修改S表中一位学生的班级号,修改后的班号不在B表中。测试触发器的功能截图:UPDATE S SET bno=88 WHERE sno=S1修改S表中一位学生的班级号,修改后的班号在B表中。测试触发器的功能截图:SELECT sno AS 学号,bno A
14、S 班级编号 FROM S WHERE sno=S18SELECT bno 班级编号,num AS 班级人数 FROM BUPDATE S SET bno=8 WHERE sno=S18SELECT sno AS 学号,bno AS 班级编号 FROM S WHERE sno=S18SELECT bno 班级编号,num AS 班级人数 FROM B4 深入思考与讨论1)请按自己的理解,说明一下触发器的工作原理。触发器是对INSERT、UPDATE、DELETE等事件的处理。当满足触发器的触发条件时(进行插入、更新等操作),数据库系统就会执行触发器中定义好的程序语句。2)请定义一个视图V_T,
15、该视图体现了职称是副教授的老师的编号、姓名、性别和所教课程编号、课程名的信息。尝试对视图V_T进行数据插入,例如,插入编号为t111的男教师李晨副教授讲授编号为c11的“信息安全”课的信息,观察是否能成功。请自行编写一个该视图的触发器,使得定义触发器后,用户可以对视图V_T进行上述数据记录的插入。注意:所插入的教师和课程可能从未出现在教师表和课程表中。创建视图:CREATE VIEW V_TAS SELECT T.tno AS 教师编号 ,tn AS 姓名,sex AS 性别,TC.cno AS 课程编号,cn AS 课程名 FROM T,TC,C WHERE T.tno=TC.tno AND
16、 TC.cno=C.cno AND T.prof=副教授插入记录:INSERT INTO V_T VALUES(T111,李晨,男,C11,信息安全)由图可知,不能插入(影响多个表)。创建触发器:CREATE TRIGGER T_insertV_TON V_T INSTEAD OF INSERTAS BEGIN IF NOT EXISTS(SELECT NULL FROM T,inserted WHERE tno=inserted.教师编号 AND tn=inserted.姓名) INSERT INTO T(tno,tn,sex,prof) SELECT 教师编号,姓名,性别,副教授 FROM inserted IF NOT EXISTS(SELECT NULL FROM C,inserted WHERE cno=inserted.课程编号) INSERT INTO C(cno,cn) SELECT 课程编号,课程名 FROM inserted INSERT INTO TC(tno,cno) SELECT 教师编号,课程编号 FROM inserted END插入记录:SELECT * FROM V_TINSERT INTO V_T VALUES(T15,范冰冰,男,C11,信息安全)SELECT * FROM V_T由图可知,成功插入记录。
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1