MySQL教程（新體系-綜合應(yīng)用實(shí)例視頻）（第4版） 習(xí)題-第09章-答案

參考答案習(xí)題-第09章一、選擇題 1.B 2.C 3.D 4.D 5.D 6.D 7.D 8.C二、說明題 1.存儲過程和存儲函數(shù)在服務(wù)器端運(yùn)行、執(zhí)行速度快，其執(zhí)行一次后，執(zhí)行規(guī)劃就駐留在高速緩沖存儲器，以后操作只需從高速緩存中調(diào)用已編譯好的二進(jìn)制代碼，提高了系統(tǒng)性能。另外，使用存儲過程和存儲函數(shù)可以完成所有數(shù)據(jù)庫操作，并可通過編程方式控制上述操作對數(shù)據(jù)庫信息訪問的權(quán)限，確保數(shù)據(jù)庫安全。 2.存儲函數(shù)與存儲過程的區(qū)別：（1）存儲函數(shù)不能擁有輸出參數(shù)，因?yàn)榇鎯瘮?shù)本身就是輸出參數(shù)；（2）不能用CALL語句來調(diào)用存儲函數(shù)；（3）存儲函數(shù)必須包含一條RETURN語句，而這條特殊的SQL語句不允許包含于存儲過程中。存儲函數(shù)多用于輔助數(shù)據(jù)庫操作（如克服生成列的局限性），但MySQL的存儲函數(shù)并不支持使用顯示或隱式事務(wù)，所以涉及基本表的更新操作一般不要采用存儲函數(shù)實(shí)現(xiàn)，而是用存儲過程結(jié)合事務(wù)的方法來保證更新數(shù)據(jù)的一致和完備性。 3.MySQL程序中處理SQL語句可能會出錯，導(dǎo)致系統(tǒng)停止對程序的處理，使用錯誤處理功能可以為不同的錯誤定義不同的處理程序，讓系統(tǒng)根據(jù)條件自動處理。在實(shí)際應(yīng)用中有時需要根據(jù)情況主動向MySQL系統(tǒng)提供信號，以便服務(wù)器向處理程序、客戶端提供當(dāng)前數(shù)據(jù)庫操作情況信息，這個時候就需要拋出錯誤信息。 4.存儲過程創(chuàng)建好后，可以在應(yīng)用程序、觸發(fā)器或者其他存儲過程中通過“CALL存儲過程名(參數(shù),...)”調(diào)用；而存儲函數(shù)創(chuàng)建好后，就如同系統(tǒng)提供的內(nèi)置函數(shù)一樣可供隨時調(diào)用使用，調(diào)用語句為“存儲函數(shù)名([參數(shù),...])”，調(diào)用存儲函數(shù)的方式與調(diào)用存儲過程相比，更加靈活多樣。存儲過程只能采用CALL語句直接調(diào)用，而存儲函數(shù)則可以出現(xiàn)在各種語句中。 5.仍然需要事務(wù)的，因?yàn)榧渔i解決并發(fā)問題，而事務(wù)解決多語句操作記錄的完整性。 6.事務(wù)處理是將一系列不可分割的增刪改查操作放在同一個事務(wù)中執(zhí)行以確保數(shù)據(jù)完整一致性，發(fā)生錯誤失敗時事務(wù)會回滾，不會對數(shù)據(jù)庫造成任何影響；而錯誤處理則是在出錯時根據(jù)錯誤類型和返回碼預(yù)先定義處理程序，發(fā)生錯誤時由MySQL自動執(zhí)行處理程序，而錯誤所造成對數(shù)據(jù)的影響仍然是作用在數(shù)據(jù)庫上的，只不過通過執(zhí)行錯誤處理程序可以加以彌補(bǔ)和善后，并提示用戶發(fā)生錯誤的原因以便排查。 7.幻讀（PhantomRead）是當(dāng)一個事務(wù)對某行執(zhí)行插入或刪除操作，而該行屬于某個事務(wù)正在讀取的行的范圍時發(fā)生的問題。事務(wù)第一次讀的行范圍顯示出其中一行已不復(fù)存在于第二次讀或后續(xù)讀中，因?yàn)樵撔幸驯黄渌聞?wù)刪除。同樣，由于其他事務(wù)的插入操作，事務(wù)的第二次讀或后續(xù)讀顯示有一行已不存在于原始讀中。 MVCC（MultiversionConcurrencyControl），即多版本并發(fā)控制技術(shù)。它通過數(shù)據(jù)行的多個版本管理來實(shí)現(xiàn)數(shù)據(jù)庫的并發(fā)控制，簡單來說就是保存數(shù)據(jù)的歷史版本。可以通過比較版本號決定數(shù)據(jù)是否顯示。讀取數(shù)據(jù)的時候不需要加鎖可以保證事務(wù)的隔離效果。 8.觸發(fā)器與表的關(guān)系密切，用于保護(hù)表中的數(shù)據(jù)。當(dāng)有操作影響到觸發(fā)器保護(hù)的數(shù)據(jù)時，觸發(fā)器自動執(zhí)行，例如，通過觸發(fā)器實(shí)現(xiàn)多個表間數(shù)據(jù)的一致性。當(dāng)對表執(zhí)行INSERT、DELETE或UPDATE操作時，將激活觸發(fā)器。 9.事件的主要作用是：關(guān)閉賬戶；打開或關(guān)閉數(shù)據(jù)庫指示器；使數(shù)據(jù)庫中的數(shù)據(jù)在某個間隔后刷新；執(zhí)行對進(jìn)入數(shù)據(jù)的復(fù)雜的檢查工作。定義的事件會被作為數(shù)據(jù)庫對象保存在系統(tǒng)中，在設(shè)定的時刻被啟動運(yùn)行。三、編程題1.存儲過程proc_area計算三角形面積，執(zhí)行語句：USEmydb;DROPPROCEDUREIFEXISTSproc_area;CREATEPROCEDUREproc_area(INaint(1),INbint(1),INcint(1),OUTareafloat)BEGIN SETarea=(1/4)*SQRT((a+b+c)*(a+b-c)*(a+c-b)*(b+c-a));END;SET@a=3;SET@b=4;SET@c=5;CALLproc_area(@a,@b,@c,@area);SELECTFORMAT(@area,2)AS'三角形面積';2.存儲函數(shù)func_area計算三角形面積，執(zhí)行語句：SETGLOBALlog_bin_trust_function_creators=1;USEmydb;DROPFUNCTIONIFEXISTSfunc_area;CREATEFUNCTIONfunc_area(aint(1),bint(1),cint(1)) RETURNSfloatBEGIN DECLAREareafloat; IFa<=0ORb<=0ORc<=0THEN RETURNNULL; ENDIF; SETarea=(1/4)*SQRT((a+b+c)*(a+b-c)*(a+c-b)*(b+c-a)); RETURNarea;END;SET@a=3;SET@b=4;SET@c=5;SELECTFORMAT(func_area(@a,@b,@c),2)AS'三角形面積';3.編寫插入成績表（cj）記錄存儲過程CJ_INSERT，執(zhí)行語句：USExscj;CREATEPROCEDURECJ_INSERT(INsidchar(6),INcidchar(3),INscoreint,OUTCodeint)label:BEGIN DECLARExfint(1)DEFAULTNULL; DECLARExhchar(6)DEFAULTNULL; DECLAREtransErrintDEFAULT0; DECLARECONTINUEHANDLERFORSQLEXCEPTIONSETtransErr=1; SETCode=0; SELECT學(xué)分INTOxfFROMkc WHERE課程號=cid; IFxfISNULLTHEN SETCode=-11; LEAVElabel; ENDIF; SELECT學(xué)號INTOxhFROMxs WHERE學(xué)號=sid; IFxhISNULLTHEN SETCode=-12; LEAVElabel; ENDIF; STARTTRANSACTION; INSERTINTOcj(學(xué)號,課程號,成績) VALUES(sid,cid,score); UPDATExsSET總學(xué)分=總學(xué)分+xf WHERE學(xué)號=xh; IFtransErr=1THEN ROLLBACK; SETCode=-1; ELSE COMMITWORK; SETCode=1; ENDIF;END;測試執(zhí)行語句：CALLCJ_INSERT('081102','100',99,@code);SELECT@code;CALLCJ_INSERT('091102','101',99,@code);SELECT@code;CALLCJ_INSERT('081102','101',99,@code);SELECT@code;4.編寫修改成績表（cj）記錄存儲過程CJ_UPDATE，執(zhí)行語句：USExscj;CREATEPROCEDURECJ_UPDATE(INsidchar(6),INcidchar(3),INscoreint,OUTCodeint)label:BEGIN DECLARExfint(1)DEFAULTNULL; DECLARExhchar(6)DEFAULTNULL; DECLAREcjint(1)DEFAULTNULL; DECLAREcodeint(1)DEFAULT0; DECLAREtransErrintDEFAULT0; DECLARECONTINUEHANDLERFORSQLEXCEPTIONSETtransErr=1; SETCode=0; SELECT學(xué)分INTOxfFROMkc WHERE課程號=cid; IFxfISNULLTHEN CALLCJ_INSERT(sid,cid,score,@code); SELECT@codeINTOcode; SETCode=code; LEAVElabel; ENDIF; SELECT學(xué)號INTOxhFROMxs WHERE學(xué)號=sid; IFxhISNULLTHEN CALLCJ_INSERT(sid,cid,score,@code); SELECT@codeINTOcode; SETCode=code; LEAVElabel; ENDIF; SELECT成績INTOcjFROMcj WHERE學(xué)號=sidAND課程號=cid; STARTTRANSACTION; IF(cj>=60ANDscore>=60)OR(cj<60ANDscore<60)THEN UPDATEcjSET成績=score WHERE學(xué)號=sidAND課程號=cid; ENDIF; IFcj>=60ANDscore<60THEN UPDATEcjSET成績=score WHERE學(xué)號=sidAND課程號=cid; UPDATExsSET總學(xué)分=總學(xué)分-xf WHERE學(xué)號=xh; ENDIF; IFcj<60ANDscore>=60THEN UPDATEcjSET成績=score WHERE學(xué)號=sidAND課程號=cid; UPDATExsSET總學(xué)分=總學(xué)分+xf WHERE學(xué)號=xh; ENDIF; IFtransErr=1THEN ROLLBACK; SETCode=-1; ELSE COMMITWORK; SETCode=1; ENDIF;END;測試執(zhí)行語句：CALLCJ_UPDATE('081102','101',95,@code);CALLCJ_UPDATE('081102','101',59,@code);CALLCJ_UPDATE('081102','101',90,@code);5.創(chuàng)建通過游標(biāo)實(shí)現(xiàn)學(xué)生表（xs）總學(xué)分初始化存儲過程XS_ZXF_RESET，執(zhí)行語句：USExscj;CREATEPROCEDUREXS_ZXF_RESET(INsidchar(6),OUTCodeint)label:BEGIN DECLARExhchar(6)DEFAULTNULL; DECLAREkchchar(3)DEFAULTNULL; DECLARExfint(1)DEFAULTNULL; DECLAREcjint(1)DEFAULTNULL; DECLAREmyfoundbooleanDEFAULTtrue; DECLAREtransErrintDEFAULT0; DECLAREcur_cjCURSOR FOR SELECT課程號,成績FROMcjWHERE學(xué)號=sid; DECLARECONTINUEHANDLERFORNOTFOUND SETmyfound=false; DECLARECONTINUEHANDLERFORSQLEXCEPTIONSETtransErr=1; SETCode=0; SELECTDISTINCT(學(xué)號)INTOxhFROMcj WHERE學(xué)號=sid; IFxhISNULLTHEN SETCode=-12; LEAVElabel; ENDIF; STARTTRANSACTION; SELECT*FROMxsWHERE學(xué)號=sidFORUPDATE; UPDATExsSET總學(xué)分=0WHERE學(xué)號=sid; SET@zxf=0; OPENcur_cj; mylabel:LOOP FETCHcur_cjINTOkch,cj; IFNOTmyfoundTHEN LEAVEmylabel; ELSE SELECT學(xué)分INTOxfFROMkc WHERE課程號=kch; IFcj>=60THEN SET@zxf=@zxf+xf; ENDIF; ENDIF; ENDLOOPmylabel; CLOSEcur_cj; UPDATExsSET總學(xué)分=@zxfWHERE學(xué)號=sid; IFtransErr=1THEN ROLLBACK; SETCode=-1; ELSE COMMITWORK; SETCode=1; ENDIF;END;測試執(zhí)行語句：CALLXS_ZXF_RESET