數(shù)據(jù)庫實驗報告計劃5

數(shù)據(jù)庫實驗報告計劃5數(shù)據(jù)庫實驗報告計劃5/數(shù)據(jù)庫實驗報告計劃5《數(shù)據(jù)庫原理》實驗報告題目：實驗五:觸發(fā)器、存學號姓名班級日期儲過程和函數(shù)xxxxxxxxxxxx.實驗內(nèi)容、步驟以及結(jié)果使用系統(tǒng)儲藏過程(sp_rename)將視圖“V_SPJ”更名為“V_SPJ_三建”。（5分）execsp_renamev_spj,v_spj_三建;2.針對SPJ數(shù)據(jù)庫，創(chuàng)辦并執(zhí)行以下的儲藏過程：（共計35分）創(chuàng)辦一個帶參數(shù)的儲藏過程—jsearch。該儲藏過程的作用是：當任意輸入一個工程代號時，將返回供應(yīng)該工程零件的供應(yīng)商的名稱(SNAME)和零件的名稱(PNAME)以及工程的名稱(JNAME)。執(zhí)行jsearch儲藏過程，盤問“J1”對應(yīng)的信息。（10分）createprocjsearch@jnochar(2)asselectsname,pname,jnamefroms,p,j,spjwheres.sno=spj.snoandp.pno=spj.pnoandj.jno=spj.jnoandspj.jno=@jno;執(zhí)行：execjsearch'J1'使用S表，為其創(chuàng)辦一個加密的儲藏過程—jmsearch。該儲藏過程的作用是：當執(zhí)行該儲藏過程時，將返回北京供應(yīng)商的所有信息。（10分）創(chuàng)辦加密儲藏過程：createprocjmsearchwithencryptionasselect*fromswheres.city='北京';sp_helptextjmsearch;(3)使用系統(tǒng)儲藏過程sp_helptext查察jsearch,jmsearch的文本信息。（5分）用系統(tǒng)儲藏過程sp_helptext查察jsearch：execsp_helpjsearch;execsp_helptextjsearch;用系統(tǒng)儲藏過程sp_helptext查察jmsearch：execsp_helpjmsearch;execsp_helptextjmsearch;執(zhí)行jmsearch儲藏過程，查察北京供應(yīng)商的情況。（5分）execjmsearch;刪除jmsearch儲藏過程。（5分）dropprocjmsearch;3.針對Student數(shù)據(jù)庫，創(chuàng)辦和執(zhí)行以下的觸發(fā)器：（共計40分）(1)刪除SC表上的外鍵拘束，針對SC表創(chuàng)辦一個名為insert_s的INSERT觸發(fā)器。該觸發(fā)器的功能：當用戶向SC表中插入記錄時，若是插入的cno值不是C表中Cno的已有值，則提示用戶“不能夠插入記錄這樣的紀錄”，否則提示“記錄插入成功”。觸發(fā)器創(chuàng)辦成功此后，向SC表插入記錄，考據(jù)觸發(fā)器可否正常工作。(5分)createtriggerinsert_sonSCinsteadofinsertasdeclare@cnochar(4)beginselect@cno=cnofrominsertedif@cno=any(selectcnofromC)beginselect'記錄插入成功'insertintoSCselect*frominsertedendelseselect'不能夠插入記錄這樣的記錄'endreturn考據(jù)：insertintoSCvalues('95007','5','99');(2)為S表創(chuàng)辦一個名為dele_s1的DELETE觸發(fā)器，該觸發(fā)器的作用是禁止刪除S表中的記錄。觸發(fā)器創(chuàng)辦成功此后，刪除S表中的記錄，考據(jù)觸發(fā)器可否正常工作。(5分)createtriggerdele_s1onSinsteadofdeleteasbeginrollbackprint'禁止刪除S表中的記錄'end考據(jù)：deletefromSwheresno='95007'(3)為S表創(chuàng)辦一個名為

dele_s2

的DELETE觸發(fā)器，該觸發(fā)器的作用是刪除

S表中的記錄時刪除

SC表中該學生的選課紀錄

。觸發(fā)器創(chuàng)辦成功此后

，刪除

S表中的記錄刪除S表中的記錄，考據(jù)觸發(fā)器可否正常工作

(SC表中的數(shù)據(jù)被正常刪除

)。(5分)createtriggerdele_s2onSafterdeleteasdeclare@snonchar(9)beginselect@sno=snofromdeleteddeleteSCwheresno=@snoend刪除考據(jù)：deleteSwheresno='95007'(4)為S表創(chuàng)辦一個名為update_s的UPDATE觸發(fā)器，該觸發(fā)器的作用是禁止更新S表中“sdept”字段的內(nèi)容。觸發(fā)器創(chuàng)辦成功此后，更新S表中“sdept”字段的內(nèi)容，考據(jù)觸發(fā)器可否正常工作。(5分)createtriggerupdate_sonSafterupdateasbeginifUPDATE(sdept)beginROLLBACKPRINT'禁止更新sdept字段'EndEND考據(jù)：updateSsetsdept=’cf’wheresno='95001'無法刪除。禁用update_s觸發(fā)器。禁用此后，更新S表中的“sdept”字段的內(nèi)容，考據(jù)觸發(fā)器可否還連續(xù)正常工作。(5分)disabletriggerupdate_sonSdisabletriggerupdate_sonS考據(jù)：updateSsetsdept='cf'wheresno='95001'(6)刪除update_s觸發(fā)器。(5分)droptriggerupdate_s(7)創(chuàng)辦一個新的課程成績統(tǒng)計表CAvgGrade(Cno,Snum,examSNum,avgGrade)，分別表示課號，選該課程的學生人數(shù)，參加考試人數(shù)，該門課程的平均成績。利用觸發(fā)器實現(xiàn)以下的功能：當SC表中有記錄插入、刪除也許某個人的成績更新時，自動更新表CAvgGrade。注意SC表中的grade為NULL時表示該學生還未參加考試，計算平均成績時不需要計算該成績，但是grade為0即考試成績?yōu)?時，需要計算該成績。(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創(chuàng)辦觸發(fā)器：createtriggertri_sconSCafterdelete,update,insertasdeclare@gradesmallintdeclare@numsmallintdeclare@old_cnosmallintdeclare@examsnumsmallintdeleteCAvgGradeDECLAREmycursorCURSORforselectcnofromC--游標是緩沖區(qū)?。?！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考據(jù)：插入：insertintoSCVALUES('95007',6,100);刪除：deleteSCWHEREsno='95007'更新：UPDATESCSETcno=4wheresno='95001'andcno=14.創(chuàng)辦一個works數(shù)據(jù)庫，其中包含員工表empoyee(eID,eName,salary)，假設(shè)該表中有1000條員工數(shù)據(jù)，完成以下要求（總計20分，每題10分）。(1)為了協(xié)助本題自動生成1000條員工數(shù)據(jù)，創(chuàng)辦一個自動生成員工ID的用戶自定義函數(shù)generateEID。其中員工ID要求是一個8位的數(shù)字，前四位表示插入員工數(shù)據(jù)的當前年份，后四位依照從0001到9999的序次增添。比方2015年插入的第一條數(shù)據(jù)是20050001，所有1000條員工ID分別是20150001-20151000。調(diào)用該函數(shù)實現(xiàn)自動插入1000條數(shù)據(jù)。（注意插入數(shù)據(jù)的時候員工姓名能夠為任意值，薪水是2000-5000之間的數(shù)字）自定義函數(shù)：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)),2000+floor(RAND()*3000)select@i=@i+1end該公司計劃為員工依照必然的規(guī)則漲薪水，請使用游標創(chuàng)辦一個儲藏過程，執(zhí)行該儲藏過程完成本次薪水調(diào)整：薪水增添規(guī)則以下：薪水在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二.實驗中出現(xiàn)的問題以及解決方案(對于未解決問題請將問題列出來)除了標題內(nèi)容以外，該部分內(nèi)容中還可以夠?qū)憣τ趯嶒灥囊恍└杏X，建議，建議等。此次實驗時寫觸發(fā)器遇到了不小難度，考據(jù)觸發(fā)器的時候也遇到了很多問題。在運行觸發(fā)器時有時發(fā)現(xiàn)觸發(fā)器無效，此后發(fā)現(xiàn)是從前題的觸發(fā)器對這道題的觸發(fā)器帶來了影響，需要關(guān)了從前的觸發(fā)器再運行。批閱者：批閱日期：實驗成績：標明：使用系統(tǒng)儲藏過程(sp_rename)將視圖“V_SPJ”更名為“V_SPJ_三建”。（5分）6.針對SPJ數(shù)據(jù)庫，創(chuàng)辦并執(zhí)行以下的儲藏過程：（共計35分）創(chuàng)辦一個帶參數(shù)的儲藏過程—jsearch。該儲藏過程的作用是：當任意輸入一個工程代號時，將返回供應(yīng)該工程零件的供應(yīng)商的名稱(SNAME)和零件的名稱(PNAME)以及工程的名稱(JNAME)。執(zhí)行jsearch儲藏過程，盤問“J1”對應(yīng)的信息。（10分）使用S表，為其創(chuàng)辦一個加密的儲藏過程—jmsearch。該儲藏過程的作用是：當(8)

執(zhí)行該儲藏過程時，將返回北京供應(yīng)商的所有信息。（10分）使用系統(tǒng)儲藏過程sp_helptext查察jsearch,jmsearch的文本信息。（5分）執(zhí)行jmsearch儲藏過程，查察北京供應(yīng)商的情況。（5分）刪除jmsearch儲藏過程。（5分）7.針對Student數(shù)據(jù)庫，創(chuàng)辦和執(zhí)行以下的觸發(fā)器：（共計40分）(8)刪除SC表上的外鍵拘束，針對SC表創(chuàng)辦一個名為insert_s的INSERT觸發(fā)器。該觸發(fā)器的功能：當用戶向SC表中插入記錄時，若是插入的cno值不是C表中Cno的已有值，則提示用戶“不能夠插入記錄這樣的紀錄”，否則提示“記錄插入成功”。觸發(fā)器創(chuàng)辦成功此后，向SC表插入記錄，考據(jù)觸發(fā)器可否正常工作。(5分)(9)為S表創(chuàng)辦一個名為dele_s1的DELETE觸發(fā)器，該觸發(fā)器的作用是禁止刪除S表中的記錄。觸發(fā)器創(chuàng)辦成功此后，刪除S表中的記錄，考據(jù)觸發(fā)器可否正常工作。(5分)(10)為S表創(chuàng)辦一個名為dele_s2的DELETE觸發(fā)器，該觸發(fā)器的作用是刪除S表中的記錄時刪除SC表中該學生的選課紀錄。觸發(fā)器創(chuàng)辦成功此后，刪除S表中的記錄刪除S表中的記錄，考據(jù)觸發(fā)器可否正常工作(SC表中的數(shù)據(jù)被正常刪除)。(5分)(11)為S表創(chuàng)辦一個名為update_s的UPDATE觸發(fā)器，該觸發(fā)器的作用是禁止更新S表中“sdept”字段的內(nèi)容。觸發(fā)器創(chuàng)辦成功此后，更新S表中“sdept”字段的內(nèi)容，考據(jù)觸發(fā)器可否正常工作。(5分)禁用update_s觸發(fā)器。禁用此后，更新S表中的“sdept”字段的內(nèi)容，考據(jù)觸發(fā)器可否還連續(xù)正常工作。(5分)刪除update_s觸