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

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

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

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

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

《数据库原理》实验报告

题目：

实验一

数据库和表的创建与管理

学号

姓名

班级

日期

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分）

创建：

createprocedurejsearch（@search_jnonchar（20））

as

begin

selectj.jname,s.sname,p.pname

froms,p,j,spj

where

spj.jno=@search_jnoandspj.jno=j.jnoandspj.sno=s.snoandspj.pno=p.pno

end

执行：

EXECjsearch@search_jno='J1'

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

该存储过程的作用是：

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

（10分）

创建：

createprocedurejmsearch

withencryption

as

begin

select*fromS

wherecity='北京'

end

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

（5分）

execsp_helptext'jsearch'

execsp_helptext'jmsearch'

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

（5分）

执行：

execjmsearch

（4）删除jmsearch存储过程。

（5分）

dropprocedurejmsearch

3.针对Student数据库，创建和执行如下的触发器：

（共计40分）

（1）删除SC表上的外键约束，针对SC表创建一个名为insert_s的INSERT触发器。

该触发器的功能：

当用户向SC表中插入记录时，如果插入的cno值不是C表中Cno的已有值，则提示用户“不能插入记录这样的纪录”，否则提示“记录插入成功”。

触发器创建成功之后，向SC表插入记录，验证触发器是否正常工作。

（5分）

createtriggerinsert_s

onSC

insteadofinsert

as

if（exists（select*frominsertedwherecnonotin（selectcnofromC）））

begin

print'不能插入这样的记录！

！

'

rollbacktransaction

end

else

print'记录插入成功！

！

'

验证：

insertintoscvalues（95009,8,98）

更改数据：

insertintoscvalues（95009,5,98）

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

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

（5分）

创建：

createtriggerdele_s1

ons

insteadofdelete

as

rollbacktransaction

print'禁止删除'

验证：

deletefromswheresno='95001'

（3）为S表创建一个名为dele_s2的DELETE触发器，该触发器的作用是删除S表中的记录时删除SC表中该学生的选课纪录。

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

（5分）

创建：

createtriggerdele_s2

ons

afterdelete

as

deletefromsc

wheresnoin（selectsnofromdeleted）

验证：

deletefromswheresno='95001'

S表中：

Sc表中：

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

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

（5分）

创建:

createtriggerupdate_s

ons

insteadofupdate

as

ifupdate（sdept）

begin

raiserror（'sdept不能被修改',10,1）

end

验证：

（5）禁用update_s触发器。

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

（5分）

禁用触发器：

disabletriggerupdate_sons

验证：

updates

setsdept='CS1'

wheresno='95002'

（6）删除update_s触发器。

（5分）

droptriggerupdate_s

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

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

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

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

（10分）

创建表：

创建触发器：

usestudent

go

createtriggerupdate_sc_cavggrade

onsc

forinsert,delete,update

as

begin

declare@cnochar（10）

declare@ssumint

declare@examssumint

declare@avggradeint

select@cnofrominserted

select@cnofromdeleted

select@ssum=count（*）fromscwhere@cno=cno

select@examssum=count（*）fromscwhere@cno=cnoandcgrade>=0

select@avggrade=AVG（cgrade）fromscwhere@cno=cnoandcgrade>=0

updateCAvgGrade

setSsum=@ssum,examSsum=@examssum,avgGrade=@avggrade

wherecno=@cno

end

验证触发器：

初始的CAvgGrade表

插入数据时：

insertintoscvalues（'95004','1',65）

Sc表：

CAvgGrade表：

删除数据时：

deletefromscwheresno='95001'

sc表的变化：

CAvgGrade表变化：

更新数据时：

updatesc

setcgrade=99wheresno='95004'

SC表的变化：

CAvgGrade表变化：

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之间的数字）

创建了一个存储过程来完成：

usework

go

createproceduregenerateEID

as

begin

declare@iint

set@i=0

while（@i<1000）

begin

insertintodbo.employeevalues（20160001+@i,'name'+CAST（@iasnchar（20））,2000+CAST（FLOOR（rand（）*3001）asint））

set@i=@i+1

return

end

end

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

工资增长规则如下：

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

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

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

usework

go

declaremycursorCURSORfor

selectsalaryfromemployee

openmycursor

declare@salaryint

fetchnextfrommycursorinto@salary

while@@FETCH_STATUS=0

begin

if（@salary<3000）

begin

updateemployeesetsalary=@salary+300wherecurrentofmycursor

end

elseif（@salary<4000）

begin

updateemployeesetsalary=@salary+200wherecurrentofmycursor

end

else

begin

updateemployeesetsalary=@salary+50wherecurrentofmycursor

end

fetchnextfrommycursorinto@salary

end

closemycursor

deallocatemycursor

未改变时：

改变后：

2.实验中出现的问题以及解决方案（对于未解决问题请将问题列出来）

除了标题内容以外，该部分内容中还可以写对于实验的一些感受，建议，意见等。

触发器是对表进行插入、更新、删除的时候会自动执行的特殊存储过程，本章学习的触发器类型是after触发器，即只有当执行update,delete,insert的时候这个触发器才会被使用到，触发器的使用为数据库操作真的提供了很多便利

批阅者：

批阅日期：

实验成绩：

批注：