数据库实验报告.docx
- 文档编号:16615276
- 上传时间:2023-07-15
- 格式:DOCX
- 页数:19
- 大小:380.29KB
数据库实验报告.docx
《数据库实验报告.docx》由会员分享,可在线阅读,更多相关《数据库实验报告.docx(19页珍藏版)》请在冰点文库上搜索。
数据库实验报告
《数据库原理》实验报告
题目:
实验五:
触发器、存储过程和函数
学号
班级
日期
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分)
createprocjsearchjnochar
(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
declareochar(4)
begin
selecto=ofrominserted
ifo=any(selectofromC)
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
declaresnonchar(9)
begin
selectsno=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
);
declarenosmallint
declaresnumsmallint
declareexamsnumsmallint
declareavggradesmallint
setno=1;
while(no<7)
begin
selectsnum=count(distinctsno)
fromSC
whereo=no;
selectexamsnum=count(distinctsno)
fromSC
whereo=noandgradeisnotnull;
selectavgGrade=AVG(grade)
fromSC
whereSC.cno=noandgradeisnotnull
insertCAvgGrade
values(no,snum,examsnum,avggrade)
setno=no+1
end
创建触发器:
createtriggertri_sconSC
afterdelete,update,insert
as
declaregradesmallint
declarenumsmallint
declareold_cnosmallint
declareexamsnumsmallint
deleteCAvgGrade
DECLAREmycursorCURSORforselectofromC--游标是缓冲区!
!
!
openmycursor
fetchnextfrommycursorintoold_cno
while(fetch_status=0)
begin
selectgrade=AVG(grade),num=COUNT(*)fromSCwhereo=old_cnogroupbyo
selectexamsnum=count(distinctsno)
fromSC
whereo=old_cnoandgradeisnotnull;
insertintoCAvgGrade
values(old_cno,num,num,grade)
fetchnextfrommycursorintoold_cno
setgrade=null
setnum=null
end
closemycursor
验证:
插入:
insertintoSC
VALUES('95007',6,100);
删除:
deleteSC
WHEREsno='95007'
更新:
UPDATESC
SETo=4
wheresno='95001'ando=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
begindeclaretempint
selecttemp=count(eID)
fromempoyee
whereeidbetweenyear(GETDATE())*10000
andyear(GETDATE())*10000+9999;
select
temp=year(GETDATE())*10000+temp+1returntempend
declareiint
seti=0
whilei<1000begin
insertintoempoyee
select[dbo].generateEID(),
char(65+floor(RAND()*26))+char(65+floor(RAND()*26))+char(65+floor(RAND()*26)),2000+floor(RAND()*3000)
selecti=i+1end
(2)该公司计划为员工按照一定的规则涨工资,请使用游标创建一个存储过程,执行该存储过程完成本次工资调整:
工资增长规则如下:
●工资在3000元以下,每月涨300元;
●工资在3000-4000元之间,每月涨200元;
●工资大于或者等于4000元,每月涨50元;
createprocedurechange_salary
asbegindeclaresalaryint;
declareempoyee_cursorcursor
forselectsalaryfromempoyee;
openempoyee_cursorfetchnext
fromempoyee_cursorintosalary;
while(FETCH_STATUS=0)begin
if(salary<3000)setsalary=salary+300
elseif(salary>4000)setsalary=salary+50
elsesetsalary=salary+200
updateempoyeesetsalary=salary
wherecurrentofempoyee_cursor
fetchnext
from
empoyee_cursorintosalaryend
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文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 实验 报告