数据库实验报告5.docx
- 文档编号:7866387
- 上传时间:2023-01-26
- 格式:DOCX
- 页数:16
- 大小:352.38KB
数据库实验报告5.docx
《数据库实验报告5.docx》由会员分享,可在线阅读,更多相关《数据库实验报告5.docx(16页珍藏版)》请在冰豆网上搜索。
数据库实验报告5
《数据库原理》实验报告
题目:
实验五:
触发器、存储过程和函数
学号
姓名
班级
日期
xxxxx
xx
xxxxx
2016.10.29
1.实验内容、步骤以及结果
1.使用系统存储过程(sp_rename)将视图“V_SPJ”更名为“V_SPJ_三建”。
(5分)
execsp_renamev_spj,v_spj_三建;
2.针对SPJ数据库,创建并执行如下的存储过程:
(共计35分)
(1)创建一个带参数的存储过程—jsearch。
该存储过程的作用是:
当任意输入一个工程代号时,将返回供应该工程零件的供应商的名称(SNAME)和零件的名称(PNAME)以及工程的名称(JNAME)。
执行jsearch存储过程,查询“J1”对应的信息。
(10分)
createprocjsearch@jnochar
(2)
asselectsname,pname,jnamefroms,p,j,spj
wheres.sno=spj.snoandp.pno=spj.pnoandj.jno=spj.jnoandspj.jno=@jno;
执行:
execjsearch'J1'
(2)使用S表,为其创建一个加密的存储过程—jmsearch。
该存储过程的作用是:
当执行该存储过程时,将返回北京供应商的所有信息。
(10分)
创建加密存储过程:
createprocjmsearchwithencryptionas
select*fromswheres.city='北京';
sp_helptextjmsearch;
(3)使用系统存储过程sp_helptext查看jsearch,jmsearch的文本信息。
(5分)
用系统存储过程sp_helptext查看jsearch:
execsp_helpjsearch;
execsp_helptextjsearch;
用系统存储过程sp_helptext查看jmsearch:
execsp_helpjmsearch;
execsp_helptextjmsearch;
(4)执行jmsearch存储过程,查看北京供应商的情况。
(5分)
execjmsearch;
(5)删除jmsearch存储过程。
(5分)
drop proc jmsearch;
3.针对Student数据库,创建和执行如下的触发器:
(共计40分)
(1)删除SC表上的外键约束,针对SC表创建一个名为insert_s的INSERT触发器。
该触发器的功能:
当用户向SC表中插入记录时,如果插入的cno值不是C表中Cno的已有值,则提示用户“不能插入记录这样的纪录”,否则提示“记录插入成功”。
触发器创建成功之后,向SC表插入记录,验证触发器是否正常工作。
(5分)
createtriggerinsert_s
onSC
insteadofinsert
as
declare@cnochar(4)
begin
select@cno=cnofrominserted
if@cno=any(selectcnofromC)
begin
select'记录插入成功'
insertintoSC
select*frominserted
end
else
select'不能插入记录这样的记录'
end
return
验证:
insertintoSC
values('95007','5','99');
(2)为S表创建一个名为dele_s1的DELETE触发器,该触发器的作用是禁止删除S表中的记录。
触发器创建成功之后,删除S表中的记录,验证触发器是否正常工作。
(5分)
createtriggerdele_s1
onS
insteadofdelete
as
begin
rollback
print'禁止删除S表中的记录'
end
验证:
delete
fromS
wheresno='95007'
(3)为S表创建一个名为dele_s2的DELETE触发器,该触发器的作用是删除S表中的记录时删除SC表中该学生的选课纪录。
触发器创建成功之后,删除S表中的记录删除S表中的记录,验证触发器是否正常工作(SC表中的数据被正常删除)。
(5分)
createtriggerdele_s2onS
afterdelete
as
declare@snonchar(9)
begin
select@sno=snofromdeleted
deleteSC
wheresno=@sno
end
删除验证:
deleteS
wheresno='95007'
(4)为S表创建一个名为update_s的UPDATE触发器,该触发器的作用是禁止更新S表中“sdept”字段的内容。
触发器创建成功之后,更新S表中“sdept”字段的内容,验证触发器是否正常工作。
(5分)
createtriggerupdate_sonS
afterupdate
as
begin
ifUPDATE(sdept)
begin
ROLLBACK
PRINT'禁止更新sdept字段'
End
END
验证:
updateS
setsdept=’cf’
wheresno='95001'
无法删除。
(5)禁用update_s触发器。
禁用之后,更新S表中的“sdept”字段的内容,验证触发器是否还继续正常工作。
(5分)
disabletriggerupdate_sonSdisabletriggerupdate_sonS
验证:
updateS
setsdept='cf'
wheresno='95001'
(6)删除update_s触发器。
(5分)
droptriggerupdate_s
(7)创建一个新的课程成绩统计表CAvgGrade(Cno,Snum,examSNum,avgGrade),分别表示课号,选该课程的学生人数,参加考试人数,该门课程的平均成绩。
利用触发器实现如下的功能:
当SC表中有记录插入、删除或者某个人的成绩更新时,自动更新表CAvgGrade。
注意SC表中的grade为NULL时表明该学生还未参加考试,计算平均成绩时不需要计算该成绩,但是grade为0即考试成绩为0时,需要计算该成绩。
(10分)
createtableCAvgGrade
(Cnosmallint,
Snumsmallint,
examSNumsmallint,
avgGradesmallint
);
declare@nosmallint
declare@snumsmallint
declare@examsnumsmallint
declare@avggradesmallint
set@no=1;
while(@no<7)
begin
select@snum=count(distinctsno)
fromSC
wherecno=@no;
select@examsnum=count(distinctsno)
fromSC
wherecno=@noandgradeisnotnull;
select@avgGrade=AVG(grade)
fromSC
whereSC.cno=@noandgradeisnotnull
insertCAvgGrade
values(@no,@snum,@examsnum,@avggrade)
set@no=@no+1
end
创建触发器:
createtriggertri_sconSC
afterdelete,update,insert
as
declare@gradesmallint
declare@numsmallint
declare@old_cnosmallint
declare@examsnumsmallint
deleteCAvgGrade
DECLAREmycursorCURSORforselectcnofromC--游标是缓冲区!
!
!
openmycursor
fetchnextfrommycursorinto@old_cno
while(@@fetch_status=0)
begin
select@grade=AVG(grade),@num=COUNT(*)fromSCwherecno=@old_cnogroupbycno
select@examsnum=count(distinctsno)
fromSC
wherecno=@old_cnoandgradeisnotnull;
insertintoCAvgGrade
values(@old_cno,@num,@num,@grade)
fetchnextfrommycursorinto@old_cno
set@grade=null
set@num=null
end
closemycursor
验证:
插入:
insertintoSC
VALUES('95007',6,100);
删除:
deleteSC
WHEREsno='95007'
更新:
UPDATESC
SETcno=4
wheresno='95001'andcno=1
4.创建一个works数据库,其中包含员工表empoyee(eID,eName,salary),假设该表中有1000条员工数据,完成下列要求(总计20分,每题10分)。
(1)为了协助本题自动生成1000条员工数据,创建一个自动生成员工ID的用户自定义函数generateEID。
其中员工ID要求是一个8位的数字,前四位表示插入员工数据的当前年份,后四位按照从0001到9999的顺序增长。
例如2015年插入的第一条数据是20050001,所有1000条员工ID分别是20150001-20151000。
调用该函数实现自动插入1000条数据。
(注意插入数据的时候员工姓名可以为任意值,工资是2000-5000之间的数字)
自定义函数:
createfunctiongenerateEID()returnsint
as
begindeclare@tempint
select@temp=count(eID)
fromempoyee
whereeidbetweenyear(GETDATE())*10000
andyear(GETDATE())*10000+9999;
select
@temp=year(GETDATE())*10000+@temp+1return@tempend
declare@iint
set@i=0
while@i<1000begin
insertintoempoyee
select[dbo].generateEID(),
char(65+floor(RAND()*26))+char(65+floor(RAND()*26))+char(65+floor(RAND()*26)),2000+floor(RAND()*3000)
select@i=@i+1end
(2)该公司计划为员工按照一定的规则涨工资,请使用游标创建一个存储过程,执行该存储过程完成本次工资调整:
工资增长规则如下:
●工资在3000元以下,每月涨300元;
●工资在3000-4000元之间,每月涨200元;
●工资大于或者等于4000元,每月涨50元;
createprocedurechange_salary
asbegindeclare@salaryint;
declareempoyee_cursorcursor
forselectsalaryfromempoyee;
openempoyee_cursorfetchnext
fromempoyee_cursorinto@salary;
while(@@FETCH_STATUS=0)begin
if(@salary<3000)set@salary=@salary+300
elseif(@salary>4000)set@salary=@salary+50
elseset@salary=@salary+200
updateempoyeesetsalary=@salary
wherecurrentofempoyee_cursor
fetchnext
from
empoyee_cursorinto@salaryend
close
empoyee_cursor
deallocateempoyee_cursorend
2.实验中出现的问题以及解决方案(对于未解决问题请将问题列出来)
除了标题内容以外,该部分内容中还可以写对于实验的一些感受,建议,意见等。
这次实验时写触发器遇到了不小难度,验证触发器的时候也遇到了很多问题。
在运行触发器时有时候发现触发器无效,后来发现是之前题的触发器对这道题的触发器带来了影响,需要关了之前的触发器再运行。
批阅者:
批阅日期:
实验成绩:
批注:
5.使用系统存储过程(sp_rename)将视图“V_SPJ”更名为“V_SPJ_三建”。
(5分)
6.针对SPJ数据库,创建并执行如下的存储过程:
(共计35分)
(6)创建一个带参数的存储过程—jsearch。
该存储过程的作用是:
当任意输入一个工程代号时,将返回供应该工程零件的供应商的名称(SNAME)和零件的名称(PNAME)以及工程的名称(JNAME)。
执行jsearch存储过程,查询“J1”对应的信息。
(10分)
(7)使用S表,为其创建一个加密的存储过程—jmsearch。
该存储过程的作用是:
当执行该存储过程时,将返回北京供应商的所有信息。
(10分)
(8)使用系统存储过程sp_helptext查看jsearch,jmsearch的文本信息。
(5分)
(9)执行jmsearch存储过程,查看北京供应商的情况。
(5分)
(10)删除jmsearch存储过程。
(5分)
7.针对Student数据库,创建和执行如下的触发器:
(共计40分)
(8)删除SC表上的外键约束,针对SC表创建一个名为insert_s的INSERT触发器。
该触发器的功能:
当用户向SC表中插入记录时,如果插入的cno值不是C表中Cno的已有值,则提示用户“不能插入记录这样的纪录”,否则提示“记录插入成功”。
触发器创建成功之后,向SC表插入记录,验证触发器是否正常工作。
(5分)
(9)为S表创建一个名为dele_s1的DELETE触发器,该触发器的作用是禁止删除S表中的记录。
触发器创建成功之后,删除S表中的记录,验证触发器是否正常工作。
(5分)
(10)为S表创建一个名为dele_s2的DELETE触发器,该触发器的作用是删除S表中的记录时删除SC表中该学生的选课纪录。
触发器创建成功之后,删除S表中的记录删除S表中的记录,验证触发器是否正常工作(SC表中的数据被正常删除)。
(5分)
(11)为S表创建一个名为update_s的UPDATE触发器,该触发器的作用是禁止更新S表中“sdept”字段的内容。
触发器创建成功之后,更新S表中“sdept”字段的内容,验证触发器是否正常工作。
(5分)
(12)禁用update_s触发器。
禁用之后,更新S表中的“sdept”字段的内容,验证触发器是否还继续正常工作。
(5分)
(13)删除update_s触发器。
(5分)
(14)创建一个新的课程成绩统计表CAvgGrade(Cno,Snum,examSNum,avgGrade),分别表示课号,选该课程的学生人数,参加考试人数,该门课程的平均成绩。
利用触发器实现如下的功能:
当SC表中有记录插入、删除或者某个人的成绩更新时,自动更新表CAvgGrade。
注意SC表中的grade为NULL时表明该学生还未参加考试,计算平均成绩时不需要计算该成绩,但是grade为0即考试成绩为0时,需要计算该成绩。
(10分)
8.创建一个works数据库,其中包含员工表empoyee(eID,eName,salary),假设该表中有1000条员工数据,完成下列要求(总计20分,每题10分)。
(3)为了协助本题自动生成1000条员工数据,创建一个自动生成员工ID的用户自定义函数generateEID。
其中员工ID要求是一个8位的数字,前四位表示插入员工数据的当前年份,后四位按照从0001到9999的顺序增长。
例如2015年插入的第一条数据是20050001,所有1000条员工ID分别是20150001-20151000。
调用该函数实现自动插入1000条数据。
(注意插入数据的时候员工姓名可以为任意值,工资是2000-5000之间的数字)
(4)该公司计划为员工按照一定的规则涨工资,请使用游标创建一个存储过程,执行该存储过程完成本次工资调整:
工资增长规则如下:
●工资在3000元以下,每月涨300元;
●工资在3000-4000元之间,每月涨200元;
●工资大于或者等于4000元,每月涨50元;
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 实验 报告