2024年数据库实验报告

《数据库原理》试验汇报題目：试验五:触发器、存储過程和函数學号姓名班级曰期xxxxxxxxxxxx.10.29试验内容、环节以及成果使用系统存储過程(sp_rename)将视图“V_SPJ”更名為“V_SPJ_三建”。（5分）execsp_renamev_spj,v_spj_三建;针對SPJ数据库，创立并执行如下的存储過程：（合计35分）创立一种带参数的存储過程—jsearch。该存储過程的作用是：當任意输入一种工程代号時，将返回供应當工程零件的供应商的名称(SNAME)和零件的名称(PNAME)以及工程的名称(JNAME)。执行jsearch存储過程，查询“J1”對应的信息。（10分）createprocjsearch@jnochar(2)asselectsname,pname,jnamefroms,p,j,spjwheres.sno=spj.snoandp.pno=spj.pnoandj.jno=spj.jnoandspj.jno=@jno;执行：execjsearch'J1'使用S表，為其创立一种加密的存储過程—jmsearch。该存储過程的作用是：當执行该存储過程時，将返回北京供应商的所有信息。（10分）创立加密存储過程：createprocjmsearchwithencryptionasselect*fromswheres.city='北京';sp_helptextjmsearch;使用系统存储過程sp_helptext查看jsearch,jmsearch的文本信息。（5分）用系统存储過程sp_helptext查看jsearch：execsp_helpjsearch;execsp_helptextjsearch;用系统存储過程sp_helptext查看jmsearch：execsp_helpjmsearch;execsp_helptextjmsearch;执行jmsearch存储過程，查看北京供应商的状况。（5分）execjmsearch;删除jmsearch存储過程。（5分）drop

proc

jmsearch;针對Student数据库，创立和执行如下的触发器：（合计40分）删除SC表上的外键约束，针對SC表创立一种名為insert_s的INSERT触发器。该触发器的功能：當顾客向SC表中插入记录時，假如插入的cno值不是C表中Cno的已經有值，则提醒顾客“不能插入记录這样的纪录”，否则提醒“记录插入成功”。触发器创立成功之後，向SC表插入记录，验证触发器与否正常工作。(5分)createtriggerinsert_sonSCinsteadofinsertasdeclare@cnochar(4)begin select@cno=cnofrominserted if@cno=any(selectcnofromC) begin select'记录插入成功' insertintoSC select*frominserted end else select'不能插入记录這样的记录' endreturn验证：insertintoSCvalues('95007','5','99');為S表创立一种名為dele_s1的DELETE触发器，该触发器的作用是严禁删除S表中的记录。触发器创立成功之後，删除S表中的记录，验证触发器与否正常工作。(5分)createtriggerdele_s1onSinsteadofdeleteasbegin rollback print'严禁删除S表中的记录'end验证：deletefromSwheresno='95007'為S表创立一种名為dele_s2的DELETE触发器，该触发器的作用是删除S表中的记录時删除SC表中该學生的选課纪录。触发器创立成功之後，删除S表中的记录删除S表中的记录，验证触发器与否正常工作(SC表中的数据被正常删除)。(5分)createtriggerdele_s2onSafterdeleteasdeclare@snonchar(9)beginselect@sno=snofromdeleteddeleteSCwheresno=@snoend删除验证：deleteSwheresno='95007'為S表创立一种名為update_s的UPDATE触发器，该触发器的作用是严禁更新S表中“sdept”字段的内容。触发器创立成功之後，更新S表中“sdept”字段的内容，验证触发器与否正常工作。(5分)createtriggerupdate_sonSafterupdateasbeginifUPDATE(sdept)begin ROLLBACK PRINT'严禁更新sdept字段' EndEND验证：updateSsetsdept=’cf’wheresno='95001'無法删除。禁用update_s触发器。禁用之後，更新S表中的“sdept”字段的内容，验证触发器与否還继续正常工作。(5分)disabletriggerupdate_sonSdisabletriggerupdate_sonS验证：updateSsetsdept='cf'wheresno='95001'删除update_s触发器。(5分)droptriggerupdate_s创立一种新的課程成绩登记表CAvgGrade(Cno,Snum,examSNum,avgGrade)，分别表达課号，选该課程的學生人数，参与考试人数，该门課程的平均成绩。运用触发器实現如下的功能：當SC表中有记录插入、删除或者某個人的成绩更新時，自動更新表CAvgGrade。注意SC表中的grade為NULL時表明该學生尚未参与考试，计算平均成绩時不需要计算该成绩，不過grade為0即考试成绩為0時，需要计算该成绩。(10分)createtableCAvgGrade(Cnosmallint,Snumsmallint,examSNumsmallint,avgGradesmallint);declare@nosmallintdeclare@snumsmallintdeclare@examsnumsmallintdeclare@avggradesmallintset@no=1;while(@no<7)beginselect@snum=count(distinctsno)fromSCwherecno=@no;select@examsnum=count(distinctsno)fromSCwherecno=@noandgradeisnotnull;select@avgGrade=AVG(grade)fromSCwhereSC.cno=@noandgradeisnotnullinsertCAvgGradevalues(@no,@snum,@examsnum,@avggrade)set@no=@no+1end创立触发器：createtriggertri_sconSCafterdelete,update,insertasdeclare@gradesmallintdeclare@numsmallintdeclare@old_cnosmallintdeclare@examsnumsmallintdeleteCAvgGradeDECLAREmycursorCURSORforselectcnofromC--游標是缓冲区！！！openmycursorfetchnextfrommycursorinto@old_cnowhile(@@fetch_status=0)beginselect@grade=AVG(grade),@num=COUNT(*)fromSCwherecno=@old_cnogroupbycnoselect@examsnum=count(distinctsno)fromSCwherecno=@old_cnoandgradeisnotnull;insertintoCAvgGradevalues(@old_cno,@num,@num,@grade)fetchnextfrommycursorinto@old_cnoset@grade=nullset@num=nullendclosemycursor验证：插入：insertintoSCVALUES('95007',6,100);删除：deleteSCWHEREsno='95007'更新：UPDATESCSETcno=4wheresno='95001'andcno=1创立一种works数据库，其中包括员工表empoyee(eID,eName,salary)，假设该表中有1000条员工数据，完毕下列规定（總计20分，每題10分）。為了协助本題自動生成1000条员工数据，创立一种自動生组员工ID的顾客自定义函数generateEID。其中员工ID规定是一种8位的数字，前四位表达插入员工数据的目前年份，後四位按照從0001到9999的次序增長。例如插入的第一条数据是0001，所有1000条员工ID分别是0001-1000。调用该函数实現自動插入1000条数据。（注意插入数据的時候员工姓名可认為任意值，工资是-5000之间的数字）自定义函数：createfunctiongenerateEID()returnsintasbegindeclare@tempintselect@temp=count(eID)fromempoyeewhereeidbetweenyear(GETDATE())*10000andyear(GETDATE())*10000+9999;select@temp=year(GETDATE())*10000+@temp+1return@tempenddeclare@iintset@i=0while@i<1000begininsertintoempoyeeselect[dbo].generateEID(),char(65+floor(RAND()*26))+char(65+floor(RAND()*26))+char(65+floor(RAND()*26)),+floor(RAND()*3000)select@i=@i+1end该企业计划為员工按照一定的规则涨工资，請使用游標创立一种存储過程，执行该存储過程完毕本次工资调整：工资增長规则如下：工资在3000元如下，每月涨300元；工资在3000-4000元之间，每月涨200元；工资不小于或者等于4000元，每月涨50元；createprocedurechange_salaryasbegindeclare@salaryint;declareempoyee_cursorcursorforselectsalaryfromempoyee;openempoyee_cursorfetchnextfromempoyee_cursorinto@salary;while(@@FETCH_STATUS=0)beginif(@salary<3000)set@salary=@salary+300elseif(@salary>4000)set@salary=@salary+50elseset@salary=@salary+200updateempoyeesetsalary=@salarywherecurrentofempoyee_cursorfetchnextfromempoyee_cursorinto@salaryendcloseempoyee_cursordeallocateempoyee_cursorend试验中出現的問題以及处理方案(對于未处理問題請将問題列出来)除了標題内容以外，该部分内容中還可以写對于试验的某些感受，提议，意見等。這次试验時写触发器碰到了不小难度，验证触发器的時候也碰到了诸多問題。在运行触发器時有時候发現触发器無效，後来发現是之前題的触发器對這道題的触发器带来了影响，需要关了之前的触发器再运行。批阅者：批阅曰期：试验成绩：批注：使用系统存储過程(sp_rename)将视图“V_SPJ”更名為“V_SPJ_三建”。（5分）针對SPJ数据库，创立并执行如下的存储過程：（合计35分）创立一种带参数的存储過程—jsearch。该存储過程的作用是：當任意输入一种工程代号時，将返回供应當工程零件的供应商的名称(SNAME)和零件的名称(PNAME)以及工程的名称(JNAME)。执行jsearch存储過程，查询“J1”對应的信息。（10分）使用S表，為其创立一种加密的存储過程—jmsearch。该存储過程的作用是：當执行该存储過程時，将返回北京供应商的所有信息。（10分）使用系统存储過程sp_helptext查看jsearch,jmsearch的文本信息。（5分）执行jmsearch存储過程，查看北京供应商的状况。（5分）删除jmsearch存储過程。（5分）针對Student数据库，创立和执行如下的触发器：（合计40分）删除SC表上的外键约束，针對SC表创立一种名為insert_s的INSERT触发器。该触发器的功能：當顾客向SC表中插入记录時，假如插入的cno值不是C表中Cno的已經有值，则提醒顾客“不能插入记录這样的纪录”，否则提醒“记录插入成功”。触发器创立成功之後，向SC表插入记录，验证触发器与否正常工作。(5分)為S表创立一种名為dele_s1的DELETE触发器，该触发器的作用是严禁删除S表中的记录。触发器创立成功之後，删除S表中的记录，验证触发器与否正常工作。(5分)為S表创立一种名為dele_s2的DELETE触发器，该触发器的作用是删除S表中的记录時删除SC表中该學生的选課纪录。触发器创立成功之後，删除S表中的记录删除S表中的记录，验证触发器与否正常工作(SC表中的数据被正常删除)。(5分)為S表创立一种名為update_s的UPDATE触发器，该触发器的作用是严禁更新S表中“sdept”字段的内容。触发器创立成功之後，更新S表中“sdept”字段的内容，验证触发器与否正常工作。(5分)禁用update_s触发器。禁用之後，更