《数据库原理》实验报告题目:实验一数据库和表的创建与管理学号姓名班级日期一.实验内容、步骤以及结果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))asbeginselectj.jname,s.sname,p.pnamefroms,p,j,spjwherespj.jno=@search_jnoandspj.jno=j.jnoandspj.sno=s.snoandspj.pno=p.pnoend执行:EXECjsearch@search_jno='J1'(2)使用S表,为其创建一个加密的存储过程—jmsearch。该存储过程的作用是:当执行该存储过程时,将返回北京供应商的所有信息。(10分)创建:createprocedurejmsearchwithencryptionasbeginselect*fromSwherecity='北京'end使用系统存储过程sp_helptext查看jsearch,jmsearch的文本信息。(5分)execsp_helptext'jsearch'execsp_helptext'jmsearch'(3)执行jmsearch存储过程,查看北京供应商的情况。(5分)执行:execjmsearch(4)删除jmsearch存储过程。(5分)dropprocedurejmsearch3.针对Student数据库,创建和执行如下的触发器:(共计40分)(1)删除SC表上的外键约束,针对SC表创建一个名为insert_s的INSERT触发器。该触发器的功能:当用户向SC表中插入记录时,如果插入的cno值不是C表中Cno的已有值,则提示用户“不能插入记录这样的纪录”,否则提示“记录插入成功”。触发器创建成功之后,向SC表插入记录,验证触发器是否正常工作。(5分)createtriggerinsert_sonSCinsteadofinsertasif(exists(select*frominsertedwherecnonotin(selectcnofromC)))beginprint'不能插入这样的记录!!'rollbacktransactionendelseprint'记录插入成功!!'验证:insertintoscvalues(95009,8,98)更改数据:insertintoscvalues(95009,5,98)(2)为S表创建一个名为dele_s1的DELETE触发器,该触发器的作用是禁止删除S表中的记录。触发器创建成功之后,删除S表中的记录,验证触发器是否正常工作。(5分)创建:createtriggerdele_s1onsinsteadofdeleteasrollbacktransactionprint'禁止删除'验证:deletefromswheresno='95001'(3)为S表创建一个名为dele_s2的DELETE触发器,该触发器的作用是删除S表中的记录时删除SC表中该学生的选课纪录。触发器创建成功之后,删除S表中的记录删除S表中的记录,验证触发器是否正常工作(SC表中的数据被正常删除)。(5分)创建:createtriggerdele_s2onsafterdeleteasdeletefromscwheresnoin(selectsnofromdeleted)验证:deletefromswheresno='95001'S表中:Sc表中:(4)为S表创建一个名为update_s的UPDATE触发器,该触发器的作用是禁止更新S表中“sdept”字段的内容。触发器创建成功之后,更新S表中“sdept”字段的内容,验证触发器是否正常工作。(5分)创建:createtriggerupdate_sonsinsteadofupdateasifupdate(sdept)beginraiserror('sdept不能被修改',10,1)end验证:(5)禁用update_s触发器。禁用之后,更新S表中的“sdept”字段的内容,验证触发器是否还继续正常工作。(5分)禁用触发器:disabletriggerupdate_sons验证:updatessetsdept='CS1'wheresno='95002'(6)删除update_s触发器。(5分)droptriggerupdate_s(7)创建一个新的课程成绩统计表CAvgGrade(Cno,Snum,examSNum,avgGrade),分别表示课号,选该课程的学生人数,参加考试人数,该门课程的平均成绩。利用触发器实现如下的功能:当SC表中有记录插入、删除或者某个人的成绩更新时,自动更新表CAvgGrade。注意SC表中的grade为NULL时表明该学生还未参加考试,计算平均成绩时不需要计算该成绩,但是grade为0即考试成绩为0时,需要计算该成绩。(10分)创建表:创建触发器:usestudentgocreatetriggerupdate_sc_cavggradeonscforinsert,delete,updateasbegindeclare@cnochar(10)declare@ssumintdeclare@examssumintdeclare@avggradeintselect@cnofrominsertedselect@cnofromdeletedselect@ssum=count(*)fromscwhere@cno=cnoselect@examssum=count(*)fromscwhere@cno=cnoandcgrade=0select@avggrade=AVG(cgrade)fromscwhere@cno=cnoandcgrade=0updateCAvgGradesetSsum=@ssum,examSsum=@examssum,avgGrade=@avggradewherecno=@cnoend验证触发器:初始的CAvgGrade表插入数据时:insertintoscvalues('95004','1',65)Sc表:CAvgGrade表:删除数据时:deletefromscwheresno='95001'sc表的变化:CAvgGrade表变化:更新数据时:updatescsetcgrade=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之间的数字)创建了一个存储过程来完成:useworkgocreateproceduregenerateEIDasbegindeclare@iintset@i=0while(@i1000)begininsertintodbo.employeevalues(20160001+@i,'name'+CAST(@iasnchar(20)),2000+CAST(FLOOR(rand()*3001)asint))set@i=@i+1returnendend(2)该公司计划为员工按照一定的规则涨工资,请使用游标创建一个存储过程,执行该存储过程完成本次工资调整:工资增长规则如下:工资在3000元以下,每月涨300元;工资在3000-4000元之间,每月涨200元;工资大于或者等于4000元,每月涨50元;useworkgodeclaremycursorCURSORforselectsalaryfromemployeeopenmycursordeclare@salaryintfetchnextfrommycursorinto@salarywhile@@FETCH_STATUS=0beginif(@salary3000)beginupdateemployeesetsalary=@salary+300wherecurrentofmycursorendelseif(@salary4000)beginupdateemployeesetsalary=@salary+200wherecurrentofmycursorendelsebeginupdateemployeesetsalary=@salary+50wherecurrentofmycursorendfetchnextfrommycursorinto@salaryendclosemycursordeallocatemycursor未改变时:改变后:二.实验中出现的问题以及解决方案(对于未解决问题请将问题列出来)除了标题内容以外,该部分内容中还可以写对于实验的一些感受,建议,意见等。触发器是对表进行插入、更新、删除的时候会自动执行的特殊存储过程,本章学习的触发器类型是after触发器,即只有当执行update,delete,insert的时候这个触发器才会被使用到,触发器的使用为数据库操作真的提供了很多便利批阅者:批阅日期:实验成绩:批注: