存储过程和触发器数据库实验5.docx
- 文档编号:2467796
- 上传时间:2023-05-03
- 格式:DOCX
- 页数:19
- 大小:519.71KB
存储过程和触发器数据库实验5.docx
《存储过程和触发器数据库实验5.docx》由会员分享,可在线阅读,更多相关《存储过程和触发器数据库实验5.docx(19页珍藏版)》请在冰点文库上搜索。
存储过程和触发器数据库实验5
数据库基础与实践实验报告
实验五存储过程和触发器
班级:
惠普测试142
学号:
**********
*******
日期:
2016-11-14
1实验目的:
1)掌握SQL进行存储过程创建和调用的方法;
2)掌握SQL进行触发器定义的方法,理解触发器的工作原理;
3)掌握触发器禁用和重新启用的方法。
2实验平台:
操作系统:
Windowsxp。
实验环境:
SQLServer2000以上版本。
3实验内容与步骤
利用实验一创建的sch_id数据库完成下列实验内容。
1.创建存储过程JSXX_PROC,调用该存储过程时可显示各任课教师姓名及其所教课程名称。
存储过程定义代码:
CREATEPROCEDUREJSXX_PROC
AS
SELECTtn教师姓名,cn所教课程FROMT,TC,CWHERET.tno=TC.tnoANDTC.cno=C.cno
存储过程执行语句与执行结果截图:
EXECUTEJSXX_PROC
2.创建存储过程XM_PROC,该存储过程可根据输入参数(学生姓名)查询并显示该学生的学号、所学课程名称和成绩;如果没有该姓名学生,则提示“无该姓名的同学”。
存储过程定义代码:
CREATEPROCEDUREXM_PROC@snameVARCHAR(100)
AS
BEGIN
IFEXISTS(SELECTNULLFROMSWHEREsn=@sname)
SELECTS.sno学号,cn课程,score成绩FROMS,SC,CWHERESC.cno=C.cnoANDSC.sno=S.snoANDS.sn=@sname
ELSE
PRINT'无该姓名的同学。
'
END
运行截图:
3.创建存储过程XBNL_PROC,该存储过程可根据输入参数(专业名词,默认值为计算机专业),统计并显示该专业各年龄段男、女生人数。
如果没有该专业,则显示“无此专业”。
存储过程定义代码:
CREATEPROCEDUREXBNL_PROC
@departNameVARCHAR(30)='计算机',
@beginINT,
@endINT
AS
DECLARE@numOfBoysINT
DECLARE@numOfGirlsINT
DECLARE@d#VARCHAR(3)
DECLARE@resultVARCHAR(50)
BEGIN
SELECT@d#=dnoFROMDWHEREdn=@departName
IF@d#ISNOTNULL
BEGIN
SELECT@numOfBoys=COUNT(sno)FROMSWHEREageBETWEEN@beginAND@endANDdno=@d#ANDsex='男'
SELECT@numOfGirls=COUNT(sno)FROMSWHEREageBETWEEN@beginAND@endANDdno=@d#ANDsex='女'
SET@result=@departName+'专业年龄在'+
CAST(@beginASVARCHAR(3))+'-'+
CAST(@endASVARCHAR(3))+'之间的男生有'+
CAST(@numOfBoysASVARCHAR(3))+'人,'+'女生有'+
CAST(@numOfGirlsASVARCHAR(3))+'人'
END
ELSE
SET@result='无此专业。
'
PRINT@result
END
执行结果:
4.执行XM_PROC存储过程,查询“贾慧”同学的学号、所学课程名称和成绩。
存储过程调用语句及其执行结果截图:
EXECXM_PROC'贾慧'
5.如果学生表中无“贾慧”同学,则另查询一位学生表中出现过的学生姓名;如果学生表中有“贾慧”同学,则另查询一位学生表中没有的学生姓名。
存储过程调用语句及其执行结果截图:
EXECXM_PROC'张明'
6.执行XBNL_PROC存储过程,查询计算机专业各年龄的男、女生人数。
(要求计算机专业必须有至少3个年龄段的男女学生)
存储过程调用语句及其执行结果截图:
EXECXBNL_PROC@begin=21,@end=25--默认:
计算机
EXECXBNL_PROC'计算机',18,25
EXECXBNL_PROC'计算机',23,25
EXECXBNL_PROC'计算机',20,21
7.执行XBNL_PROC存储过程,输入一个专业表中没有的专业。
存储过程调用语句及其执行结果截图:
EXECXBNL_PROC'音乐',18,25
8.分别查看XBNL_PROC存储过程的一般信息。
执行语句及结果截图:
EXECsp_helpXM_PROC
9.删除XM_PROC存储过程。
执行语句及结果截图:
DROPPROCXM_PROC
10.创建班级表B(bno,bn,num),三个属性分别表示班号,班名,人数;在学生S表中增加一个属性班号。
表创建语句:
--创建班级表:
CREATETABLEB(
bnoVARCHAR(3)PRIMARYKEY,
bnVARCHAR(20)UNIQUENOTNULL,
numINT
)
--S表添加班级属性:
ALTERTABLESADDbnoVARCHAR(3)
执行结果截图:
11.创建触发器T_insertS,当向S表添加记录时,验证学生的班级号是否出现在B表中,如果不是则撤销S表添加记录的操作;,如果学生的班号在班级表中则自动修改相应班级的人数字段值。
触发器创建语句:
CREATETRIGGERT_insertS
ONS
FORINSERT
AS
BEGIN
DECLARE@bnoVARCHAR(3)
DECLARE@snoVARCHAR(10)
SELECT@bno=bnoFROMinserted
SELECT@sno=snoFROMinserted
IFEXISTS(SELECTNULLFROMBWHEREbno=@bno)
BEGIN
UPDATEBSETnum=num+1WHEREbno=@bno
PRINT'添加成功!
'
END
ELSE
BEGIN
DELETEFROMSWHEREsno=@sno
PRINT'添加失败!
'
END
END
向S表中插入一位学生,其班号不在B表中。
测试触发器的功能截图:
插入记录前查询:
SELECT*FROMSWHEREsn='翠翠'
插入记录:
INSERTINTOSVALUES('S19','翠翠','女',18,'D2',3)
插入后查询:
SELECT*FROMSWHEREsn='翠翠'
向S表中插入一位学生,其班号在B表出现过。
测试触发器的功能截图:
插入前查询:
SELECT*FROMSWHEREsn='翠翠'
插入记录:
INSERTINTOSVALUES('S19','翠翠','女',18,'D2',1)
插入后查询:
SELECT*FROMSWHEREsn='翠翠'
禁用T_insertS触发器语句;
ALTERTABLESDISABLETRIGGERT_insertS
向S表中插入一位学生,其班号不在B表中。
测试触发器的功能截图:
INSERTINTOSVALUES('S20','翠花','女',18,'D2',3)
SELECT*FROMSWHEREsn='翠花'
启用T_insertS触发器语句;
ALTERTABLESENABLETRIGGERT_insertS
向S表中插入一位学生,其班号不在B表中。
测试触发器的功能截图:
INSERTINTOSVALUES('S21','翠花儿','女',19,'D2',3)
SELECT*FROMSWHEREsn='翠花儿'
12.创建触发器T_updateS,当修改S表一位同学的班级字段值时,验证学生的班级号是否出现在B表中,如果不是则撤销对S表中该条记录的修改操作,如果修改后的班号在班级表中则自动修改相应班级的人数字段值。
触发器创建语句:
CREATETRIGGERT_updateS
ONS
FORUPDATE
AS
BEGIN
DECLARE@b#VARCHAR(3)
DECLARE@b#oldVARCHAR(3)
DECLARE@numOldINT
SELECT@b#=bnoFROMinserted
SELECT@b#old=bnoFROMdeleted
SELECT@numOld=numFROMBWHEREbno=@b#old
IFEXISTS(SELECTNULLFROMBWHEREbno=@b#)
BEGIN
IF(@numOldISNOTNULL)
BEGIN
UPDATEBSETnum=num+1WHEREbno=@b#
UPDATEBSETnum=num-1WHEREbno=@b#old
END
ELSE
UPDATEBSETnum=num+1WHEREbno=@b#
PRINT'更新成功!
'
END
ELSE
BEGIN
UPDATESSETbno=@b#oldWHEREbno=@b#
PRINT'更新失败!
'
END
END
修改S表中一位学生的班级号,修改后的班号不在B表中。
测试触发器的功能截图:
UPDATESSETbno='88'WHEREsno='S1'
修改S表中一位学生的班级号,修改后的班号在B表中。
测试触发器的功能截图:
SELECTsnoAS'学号',bnoAS'班级编号'FROMSWHEREsno='S18'
SELECTbno'班级编号',numAS'班级人数'FROMB
UPDATESSETbno='8'WHEREsno='S18'
SELECTsnoAS'学号',bnoAS'班级编号'FROMSWHEREsno='S18'
SELECTbno'班级编号',numAS'班级人数'FROMB
4深入思考与讨论
1)请按自己的理解,说明一下触发器的工作原理。
触发器是对INSERT、UPDATE、DELETE等事件的处理。
当满足触发器的触发条件时(进行插入、更新等操作),数据库系统就会执行触发器中定义好的程序语句。
2)请定义一个视图V_T,该视图体现了职称是副教授的老师的编号、姓名、性别和所教课程编号、课程名的信息。
尝试对视图V_T进行数据插入,例如,插入编号为t111的男教师李晨副教授讲授编号为c11的“信息安全”课的信息,观察是否能成功。
请自行编写一个该视图的触发器,使得定义触发器后,用户可以对视图V_T进行上述数据记录的插入。
注意:
所插入的教师和课程可能从未出现在教师表和课程表中。
创建视图:
CREATEVIEWV_T
AS
SELECTT.tnoAS'教师编号',tnAS'姓名',sexAS'性别',TC.cnoAS'课程编号',cnAS'课程名'
FROMT,TC,C
WHERET.tno=TC.tnoANDTC.cno=C.cnoANDT.prof='副教授'
插入记录:
INSERTINTOV_TVALUES('T111','李晨','男','C11','信息安全')
由图可知,不能插入(影响多个表)。
创建触发器:
CREATETRIGGERT_insertV_T
ONV_T
INSTEADOFINSERT
AS
BEGIN
IFNOTEXISTS(SELECTNULLFROMT,insertedWHEREtno=inserted.教师编号ANDtn=inserted.姓名)
INSERTINTOT(tno,tn,sex,prof)SELECT教师编号,姓名,性别,'副教授'FROMinserted
IFNOTEXISTS(SELECTNULLFROMC,insertedWHEREcno=inserted.课程编号)
INSERTINTOC(cno,cn)SELECT课程编号,课程名FROMinserted
INSERTINTOTC(tno,cno)SELECT教师编号,课程编号FROMinserted
END
插入记录:
SELECT*FROMV_T
INSERTINTOV_TVALUES('T15','范冰冰','男','C11','信息安全')
SELECT*FROMV_T
由图可知,成功插入记录。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 存储 过程 触发器 数据库 实验