西北工业大学数据库实验报告DB5.docx

西北工业大学数据库实验报告DB5.docx

《西北工业大学数据库实验报告DB5.docx》由会员分享，可在线阅读，更多相关《西北工业大学数据库实验报告DB5.docx（18页珍藏版）》请在冰豆网上搜索。

西北工业大学数据库实验报告DB5

《数据库原理》实验报告

题目：

实验五

触发器、存储过程和函数

学号

姓名

班级

日期

一、实验内容、步骤以及结果

1.使用系统存储过程（sp_rename）将视图“V_SPJ”更名为“V_SPJ_三建”。

（5分）

sp_rename'V_SPJ','V_SPJ_三建'

2.针对SPJ数据库，创建并执行如下的存储过程：

（共计35分）

（1）创建一个带参数的存储过程—jsearch。

该存储过程的作用是：

当任意输入一个工程代号时，将返回供应该工程零件的供应商的名称（SNAME）和零件的名称（PNAME）以及工程的名称（JNAME）。

执行jsearch存储过程，查询“J1”对应的信息。

（10分）

CREATEPROCEDUREjsearch（@SPJ_JNOCHAR（10））

AS

BEGINSELECTSNAME,PNAME,JNAME

FROMSPJ,S,P,J

WHERE@SPJ_JNO=SPJ.JNOANDSPJ.JNO=J.JNOANDSPJ.PNO=P.PNOANDSPJ.SNO=S.SNO

END;

（2）使用S表，为其创建一个加密的存储过程—jmsearch。

该存储过程的作用是：

当执行该存储过程时，将返回北京供应商的所有信息。

（10分）

CREATEPROCEDUREjmsearchWITHENCRYPTION

AS

SELECT*

FROMS

WHERES.CITY='北京';

（3）使用系统存储过程sp_helptext查看jsearch,jmsearch的文本信息。

（5分）

EXECsp_helptextjsearch

EXECsp_helptextjmsearch

（4）执行jmsearch存储过程，查看北京供应商的情况。

（5分）

EXECjmsearch;

（5）删除jmsearch存储过程。

（5分）

DROPprocedurejmsearch

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

elseselect'不能插入记录这样的记录'

end

return

验证是否正常工作：

插入cno已经有的数据：

insertintoSC

values（'95006','6','99'）;

插入cno，没有的数据：

insertintoSC

values（'95006','7','99'）;

（2）为S表创建一个名为dele_s1的DELETE触发器，该触发器的作用是禁止删除S表中的记录。

触发器创建成功之后，删除S表中的记录，验证触发器是否正常工作。

（5分）

createtriggerdele_s1

onS

insteadofdelete

as

begin

rollback

print'禁止删除S表中的记录'

end

验证是否正常工作：

delete

fromS

wheresno='95004'

（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='95001'

执行完成后SC表相关数据已经被删除。

（4）为S表创建一个名为update_s的UPDATE触发器，该触发器的作用是禁止更新S表中“sdept”字段的内容。

触发器创建成功之后，更新S表中“sdept”字段的内容，验证触发器是否正常工作。

（5分）

createtriggerupdate_sonS

afterupdate

as

begin

ifupdate（sdept）

begin

rollbackprint'禁止更新sdept字段'

end

end

验证：

updateSsetsdept='cf'

wheresno='95002'

（5）禁用update_s触发器。

禁用之后，更新S表中的“sdept”字段的内容，验证触发器是否还继续正常工作。

（5分）

disabletriggerupdate_sonS

验证：

updateSsetsdept='cf'

wheresno='95002'

（6）删除update_s触发器。

（5分）

droptriggerupdate_s

（7）创建一个新的课程成绩统计表CAvgGrade（Cno,Snum,examSNum,avgGrade），分别表示课号，选该课程的学生人数，参加考试人数，该门课程的平均成绩。

利用触发器实现如下的功能：

当SC表中有记录插入、删除或者某个人的成绩更新时，自动更新表CAvgGrade。

注意SC表中的grade为NULL时表明该学生还未参加考试，计算平均成绩时不需要计算该成绩，但是grade为0即考试成绩为0时，需要计算该成绩。

（10分）

首先使用SQL语句创建表CAvgGrade（并同时把SC表原有的数据记录插入）：

createtableCAvgGrade（Cnosmallint,Snumsmallint,examSNumsmallint,avgGradesmallint）;

declare@noint

declare@snumint

declare@examsnumint

declare@avggradeint

set@no=1;

while（@no<7）

begin

select@snum=count（distinctsno）fromSCwherecno=@no;

select@examsnum=count（distinctsno）fromSCwherecno=@noandgradeisnotnull

select@avgGrade=AVG（grade）fromSCwhereSC.cno=@noandgradeisnotnull

insertCAvgGradevalues（@no,@snum,@examsnum,@avggrade）

set@no=@no+1

end

创建后的表如下：

创建触发器：

createtriggertri_sconSC

afterdelete,update,insert

as

declare@gradeint

declare@numint

declare@old_cnoint

declare@examsnumint

deleteCAvgGrade

DECLAREmycursorCURSORforselectcnofromC

openmycursor

fetchnextfrommycursorinto@old_cno

while（@@fetch_status=0）

begin

select@grade=AVG（grade）,@num=COUNT（*）fromSC

wherecno=@old_cno

groupbycno

select@examsnum=count（distinctsno）fromSC

wherecno=@old_cnoandgradeisnotnull

insertintoCAvgGradevalues（@old_cno,@num,@num,@grade）

fetchnextfrommycursorinto@old_cno

set@grade=null

set@num=null

end

closemycursor

验证：

插入：

insertintoSCVALUES（95006,6,100）;

删除：

deleteSCWHEREsno=95006

更新：

UPDATESCSETcno=4wheresno=95001andcno=1

4.创建一个works数据库，其中包含员工表employee（eID,eName,salary），假设该表中有1000条员工数据，完成下列要求（总计20分，每题10分）。

（1）为了协助本题自动生成1000条员工数据，创建一个自动生成员工ID的用户自定义函数generateEID。

其中员工ID要求是一个8位的数字，前四位表示插入员工数据的当前年份，后四位按照从0001到9999的顺序增长。

例如2015年插入的第一条数据是20050001，所有1000条员工ID分别是20150001-20151000。

调用该函数实现自动插入1000条数据。

（注意插入数据的时候员工姓名可以为任意值，工资是2000-5000之间的数字）

自定义函数：

createproceduregenerateEID

as

begin

declare@iint

set@i=0

while（@i<1000）

begin

insertintoemployeevalues（20180001+@i,'name'+CAST（@ias

nchar（20））,2000+CAST（FLOOR（rand（）*3001）asint））

set@i=@i+1

end

end

执行：

execgenerateEID

插入结果见下图：

（2）该公司计划为员工按照一定的规则涨工资，请使用游标创建一个存储过程，执行该存储过程完成本次工资调整：

工资增长规则如下：

工资在3000元以下，每月涨300元；

工资在3000-4000元之间，每月涨200元；

工资大于或者等于4000元，每月涨50元；

代码：

declaremycursorCURSOR

forselectsalaryfromemployee

openmycursor

declare@salaryint

fetchnextfrommycursorinto@salary

while@@FETCH_STATUS=0

begin

if（@salary<3000）

begin

updateemployee

setsalary=@salary+300

wherecurrentofmycursor

end

else

if（@salary<4000）

begin

updateemployee

setsalary=@salary+200

wherecurrentofmycursor

end

else

begin

updateemployee

setsalary=@salary+50

wherecurrentofmycursor

end

fetchnextfrommycursorinto@salary

end

closemycursor

deallocatemycursor

执行结果：

二、实验中出现的问题以及解决方案（对于未解决问题请将问题列出来）除了标题内容以外，该部分内容中还可以写对于实验的一些感受，建议，意见等。

通过本次实验，加深了对触发器、存储过程和函数的理解，提高了动手能力，尤其是最后一问，通过函数实现1000条数据的插入，学到了增加数据的方法。

希望可以适当减少一些实验题目，感觉较多的代码量导致课堂时间不足。

批阅者：

批阅日期：

实验成绩：

批注：