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

第9章過程式對(duì)象程序設(shè)計(jì)一、選擇題1．下列說法錯(cuò)誤的是（）。A．語(yǔ)句體包含一個(gè)以上語(yǔ)句需要采用BEGIN…ENDB．多重分支只能用CASE語(yǔ)句C．WHILE中循環(huán)體可以一次不執(zhí)行D．注釋內(nèi)容不會(huì)產(chǎn)生任何動(dòng)作2．關(guān)于存儲(chǔ)過程的說法，說法錯(cuò)誤的是（）。A．方便用戶完成某些功能B．存儲(chǔ)過程方便用戶批量執(zhí)行-SQL命令C．存儲(chǔ)過程不能調(diào)用存儲(chǔ)過程D．應(yīng)用程序可以調(diào)用存儲(chǔ)過程3．存儲(chǔ)過程與外界的交互不能通過（）。A．表 B．輸入?yún)?shù) C．輸出參數(shù) D．游標(biāo)4.關(guān)于存儲(chǔ)函數(shù)，說法錯(cuò)誤的是（）。A.函數(shù)值只能一個(gè) B.不能采用事務(wù)C.不能包含輸出參數(shù) D.不能使用用戶變量5.關(guān)于游標(biāo)，說法錯(cuò)誤的是（）。A.游標(biāo)用于處理多條記錄 B.一次只能讀取一條記錄C.只能順序讀取 D.游標(biāo)定義不能保存6.關(guān)于事務(wù)，說法錯(cuò)誤的是（）。A.事務(wù)一般用于一條以上增刪改語(yǔ)句中 B.回滾后事務(wù)中記錄的變化均不會(huì)發(fā)生C.必須關(guān)閉自動(dòng)提交 D.同一個(gè)事務(wù)中不同查詢的相同項(xiàng)可能檢索到不同的結(jié)果7．關(guān)于觸發(fā)器的錯(cuò)誤說法是（）。A．INSERT觸發(fā)器先插入記錄后判斷 B．DELETE觸發(fā)器先刪除記錄后判斷C．UPDATE觸發(fā)器先判斷后修改記錄 D．A和B8．關(guān)于觸發(fā)器的錯(cuò)誤說法是（）。A．游標(biāo)一般用于存儲(chǔ)過程 B．游標(biāo)也可用于觸發(fā)器C．應(yīng)用程序可以調(diào)用觸發(fā)器 D．觸發(fā)器一般針對(duì)表二、說明題1．為什么需要使用存儲(chǔ)過程和存儲(chǔ)函數(shù)？2．存儲(chǔ)函數(shù)和存儲(chǔ)過程有什么不同，它們分別應(yīng)用在什么場(chǎng)合？3.為什么需要錯(cuò)誤處理？什么情況下需要拋出錯(cuò)誤信息？4.如何指定存儲(chǔ)函數(shù)和存儲(chǔ)過程調(diào)用者？5.加鎖后是否不需要事務(wù)？6.事務(wù)處理和錯(cuò)誤處理功能有什么不同？7.什么是幻讀？什么是MVCC？8.為什么需要使用觸發(fā)器？。9.為什么需要使用事件？定義的事件是否一定保存？三、編程題（基于xscj數(shù)據(jù)庫(kù)）1.創(chuàng)建存儲(chǔ)過程proc_area，計(jì)算三角形面積。輸入?yún)?shù)為三邊長(zhǎng)（a,b,c），輸出參數(shù)為三角形面積。調(diào)用存儲(chǔ)過程，顯示計(jì)算結(jié)果。2.創(chuàng)建存儲(chǔ)函數(shù)func_area，計(jì)算三角形面積。輸入?yún)?shù)為三邊長(zhǎng)（a,b,c），函數(shù)返回為三角形面積。調(diào)用存儲(chǔ)函數(shù)，顯示計(jì)算結(jié)果。邊長(zhǎng)小于等于0，返回NULL。3.編寫插入成績(jī)表（cj）記錄存儲(chǔ)過程CJ_INSERT，輸入?yún)?shù)為學(xué)號(hào)、課程號(hào)、成績(jī)，輸出參數(shù)為狀態(tài)。如果對(duì)應(yīng)學(xué)號(hào)和課程號(hào)存在，返回狀態(tài)0，否則進(jìn)行插入操作。包括：（1）查找課程表（kc）對(duì)應(yīng)該課程的學(xué)分，如果找不到該課程，課程號(hào)輸入?yún)?shù)錯(cuò)誤，狀態(tài)為-11。（2）查找學(xué)生表（xs）對(duì)應(yīng)學(xué)號(hào)，如果找不到該學(xué)號(hào)，學(xué)號(hào)輸入?yún)?shù)錯(cuò)誤，狀態(tài)為-12。（3）如果輸入?yún)?shù)正確，用輸入?yún)?shù)對(duì)成績(jī)表（cj）插入記錄，同時(shí)更新（累加）學(xué)號(hào)對(duì)應(yīng)記錄總學(xué)分，返回狀態(tài)1。插入操作需要采用事務(wù)，如果事務(wù)操作不成功，狀態(tài)為-1。4.編寫修改成績(jī)表（cj）記錄存儲(chǔ)過程CJ_UPDATE，輸入?yún)?shù)為學(xué)號(hào)、課程號(hào)、成績(jī)，輸出參數(shù)為狀態(tài)。如果對(duì)應(yīng)學(xué)號(hào)和課程號(hào)不存在，調(diào)用插入成績(jī)表（cj）記錄存儲(chǔ)過程CJ_INSERT，將CJ_INSERT存儲(chǔ)過程返回狀態(tài)作為本存儲(chǔ)過程狀態(tài)返回。否則進(jìn)行修改操作。包括：（1）讀取課程表（kc）對(duì)應(yīng)課程的學(xué)分。（2）如果原來成績(jī)和新成績(jī)均>=60或者均<60，修改成績(jī)表對(duì)應(yīng)成績(jī)。返回狀態(tài)1。（3）如果原來成績(jī)>=60而新成績(jī)<60，修改成績(jī)表對(duì)應(yīng)成績(jī)，同時(shí)學(xué)生表（xs）總學(xué)分減該課程學(xué)分。返回狀態(tài)1。（4）如果原來成績(jī)<60而新成績(jī)>=60，修改成績(jī)表對(duì)應(yīng)成績(jī)，同時(shí)學(xué)生表（xs）總學(xué)分加該課程學(xué)分。返回狀態(tài)1。修改操作需要采用事務(wù)，如果事務(wù)操作不成功，狀態(tài)為-1。5.創(chuàng)建通過游標(biāo)實(shí)現(xiàn)學(xué)生表（xs）總學(xué)分初始化存儲(chǔ)過程XS_ZXF_RESET。輸入?yún)?shù)為學(xué)號(hào)，輸出參數(shù)為狀態(tài)。成績(jī)表（cj）對(duì)應(yīng)學(xué)號(hào)查詢定義作為游標(biāo)，學(xué)生表（xs）對(duì)應(yīng)學(xué)號(hào)學(xué)生記錄加鎖，總學(xué)分置0，循環(huán)讀取游標(biāo)記錄，根據(jù)成績(jī)，讀取對(duì)應(yīng)課程學(xué)分，累加學(xué)生表（xs）學(xué)號(hào)記錄總學(xué)分。循環(huán)完成后，學(xué)生表（xs）對(duì)應(yīng)學(xué)號(hào)學(xué)生記錄解鎖。6.創(chuàng)建學(xué)生表（xs）刪除觸發(fā)器，判斷成績(jī)表（cj）是否存在對(duì)應(yīng)的學(xué)號(hào)記錄，如果存在，發(fā)出錯(cuò)誤信號(hào)，阻止學(xué)生表（xs）刪除操作。參考答案習(xí)題-第09章一、選擇題 1.B 2.C 3.D 4.D 5.D 6.D 7.D 8.C二、說明題 1.存儲(chǔ)過程和存儲(chǔ)函數(shù)在服務(wù)器端運(yùn)行、執(zhí)行速度快，其執(zhí)行一次后，執(zhí)行規(guī)劃就駐留在高速緩沖存儲(chǔ)器，以后操作只需從高速緩存中調(diào)用已編譯好的二進(jìn)制代碼，提高了系統(tǒng)性能。另外，使用存儲(chǔ)過程和存儲(chǔ)函數(shù)可以完成所有數(shù)據(jù)庫(kù)操作，并可通過編程方式控制上述操作對(duì)數(shù)據(jù)庫(kù)信息訪問的權(quán)限，確保數(shù)據(jù)庫(kù)安全。 2.存儲(chǔ)函數(shù)與存儲(chǔ)過程的區(qū)別：（1）存儲(chǔ)函數(shù)不能擁有輸出參數(shù)，因?yàn)榇鎯?chǔ)函數(shù)本身就是輸出參數(shù)；（2）不能用CALL語(yǔ)句來調(diào)用存儲(chǔ)函數(shù)；（3）存儲(chǔ)函數(shù)必須包含一條RETURN語(yǔ)句，而這條特殊的SQL語(yǔ)句不允許包含于存儲(chǔ)過程中。存儲(chǔ)函數(shù)多用于輔助數(shù)據(jù)庫(kù)操作（如克服生成列的局限性），但MySQL的存儲(chǔ)函數(shù)并不支持使用顯示或隱式事務(wù)，所以涉及基本表的更新操作一般不要采用存儲(chǔ)函數(shù)實(shí)現(xiàn)，而是用存儲(chǔ)過程結(jié)合事務(wù)的方法來保證更新數(shù)據(jù)的一致和完備性。 3.MySQL程序中處理SQL語(yǔ)句可能會(huì)出錯(cuò)，導(dǎo)致系統(tǒng)停止對(duì)程序的處理，使用錯(cuò)誤處理功能可以為不同的錯(cuò)誤定義不同的處理程序，讓系統(tǒng)根據(jù)條件自動(dòng)處理。在實(shí)際應(yīng)用中有時(shí)需要根據(jù)情況主動(dòng)向MySQL系統(tǒng)提供信號(hào)，以便服務(wù)器向處理程序、客戶端提供當(dāng)前數(shù)據(jù)庫(kù)操作情況信息，這個(gè)時(shí)候就需要拋出錯(cuò)誤信息。 4.存儲(chǔ)過程創(chuàng)建好后，可以在應(yīng)用程序、觸發(fā)器或者其他存儲(chǔ)過程中通過“CALL存儲(chǔ)過程名(參數(shù),...)”調(diào)用；而存儲(chǔ)函數(shù)創(chuàng)建好后，就如同系統(tǒng)提供的內(nèi)置函數(shù)一樣可供隨時(shí)調(diào)用使用，調(diào)用語(yǔ)句為“存儲(chǔ)函數(shù)名([參數(shù),...])”，調(diào)用存儲(chǔ)函數(shù)的方式與調(diào)用存儲(chǔ)過程相比，更加靈活多樣。存儲(chǔ)過程只能采用CALL語(yǔ)句直接調(diào)用，而存儲(chǔ)函數(shù)則可以出現(xiàn)在各種語(yǔ)句中。 5.仍然需要事務(wù)的，因?yàn)榧渔i解決并發(fā)問題，而事務(wù)解決多語(yǔ)句操作記錄的完整性。 6.事務(wù)處理是將一系列不可分割的增刪改查操作放在同一個(gè)事務(wù)中執(zhí)行以確保數(shù)據(jù)完整一致性，發(fā)生錯(cuò)誤失敗時(shí)事務(wù)會(huì)回滾，不會(huì)對(duì)數(shù)據(jù)庫(kù)造成任何影響；而錯(cuò)誤處理則是在出錯(cuò)時(shí)根據(jù)錯(cuò)誤類型和返回碼預(yù)先定義處理程序，發(fā)生錯(cuò)誤時(shí)由MySQL自動(dòng)執(zhí)行處理程序，而錯(cuò)誤所造成對(duì)數(shù)據(jù)的影響仍然是作用在數(shù)據(jù)庫(kù)上的，只不過通過執(zhí)行錯(cuò)誤處理程序可以加以彌補(bǔ)和善后，并提示用戶發(fā)生錯(cuò)誤的原因以便排查。 7.幻讀（PhantomRead）是當(dāng)一個(gè)事務(wù)對(duì)某行執(zhí)行插入或刪除操作，而該行屬于某個(gè)事務(wù)正在讀取的行的范圍時(shí)發(fā)生的問題。事務(wù)第一次讀的行范圍顯示出其中一行已不復(fù)存在于第二次讀或后續(xù)讀中，因?yàn)樵撔幸驯黄渌聞?wù)刪除。同樣，由于其他事務(wù)的插入操作，事務(wù)的第二次讀或后續(xù)讀顯示有一行已不存在于原始讀中。 MVCC（MultiversionConcurrencyControl），即多版本并發(fā)控制技術(shù)。它通過數(shù)據(jù)行的多個(gè)版本管理來實(shí)現(xiàn)數(shù)據(jù)庫(kù)的并發(fā)控制，簡(jiǎn)單來說就是保存數(shù)據(jù)的歷史版本。可以通過比較版本號(hào)決定數(shù)據(jù)是否顯示。讀取數(shù)據(jù)的時(shí)候不需要加鎖可以保證事務(wù)的隔離效果。 8.觸發(fā)器與表的關(guān)系密切，用于保護(hù)表中的數(shù)據(jù)。當(dāng)有操作影響到觸發(fā)器保護(hù)的數(shù)據(jù)時(shí)，觸發(fā)器自動(dòng)執(zhí)行，例如，通過觸發(fā)器實(shí)現(xiàn)多個(gè)表間數(shù)據(jù)的一致性。當(dāng)對(duì)表執(zhí)行INSERT、DELETE或UPDATE操作時(shí)，將激活觸發(fā)器。 9.事件的主要作用是：關(guān)閉賬戶；打開或關(guān)閉數(shù)據(jù)庫(kù)指示器；使數(shù)據(jù)庫(kù)中的數(shù)據(jù)在某個(gè)間隔后刷新；執(zhí)行對(duì)進(jìn)入數(shù)據(jù)的復(fù)雜的檢查工作。定義的事件會(huì)被作為數(shù)據(jù)庫(kù)對(duì)象保存在系統(tǒng)中，在設(shè)定的時(shí)刻被啟動(dòng)運(yùn)行。三、編程題1.存儲(chǔ)過程proc_area計(jì)算三角形面積，執(zhí)行語(yǔ)句：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.存儲(chǔ)函數(shù)func_area計(jì)算三角形面積，執(zhí)行語(yǔ)句：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.編寫插入成績(jī)表（cj）記錄存儲(chǔ)過程CJ_INSERT，執(zhí)行語(yǔ)句：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課程號(hào)=cid; IFxfISNULLTHEN SETCode=-11; LEAVElabel; ENDIF; SELECT學(xué)號(hào)INTOxhFROMxs WHERE學(xué)號(hào)=sid; IFxhISNULLTHEN SETCode=-12; LEAVElabel; ENDIF; STARTTRANSACTION; INSERTINTOcj(學(xué)號(hào),課程號(hào),成績(jī)) VALUES(sid,cid,score); UPDATExsSET總學(xué)分=總學(xué)分+xf WHERE學(xué)號(hào)=xh; IFtransErr=1THEN ROLLBACK; SETCode=-1; ELSE COMMITWORK; SETCode=1; ENDIF;END;測(cè)試執(zhí)行語(yǔ)句：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.編寫修改成績(jī)表（cj）記錄存儲(chǔ)過程CJ_UPDATE，執(zhí)行語(yǔ)句：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課程號(hào)=cid; IFxfISNULLTHEN CALLCJ_INSERT(sid,cid,score,@code); SELECT@codeINTOcode; SETCode=code; LEAVElabel; ENDIF; SELECT學(xué)號(hào)INTOxhFROMxs WHERE學(xué)號(hào)=sid; IFxhISNULLTHEN CALLCJ_INSERT(sid,cid,score,@code); SELECT@codeINTOcode; SETCode=code; LEAVElabel; ENDIF; SELECT成績(jī)INTOcjFROMcj WHERE學(xué)號(hào)=sidAND課程號(hào)=cid; STARTTRANSACTION; IF(cj>=60ANDscore>=60)OR(cj<60ANDscore<60)THEN UPDATEcjSET成績(jī)=score WHERE學(xué)號(hào)=sidAND課程號(hào)=cid; ENDIF; IFcj>=60ANDscore<60THEN UPDATEcjSET成績(jī)=score WHERE學(xué)號(hào)=sidAND課程號(hào)=cid; UPDATExsSET總學(xué)分=總學(xué)分-xf WHERE學(xué)號(hào)=xh; ENDIF; IFcj<60ANDscore>=60THEN UPDATEcjSET成績(jī)=score WHERE學(xué)號(hào)=sidAND課程號(hào)=cid; UPDATExsSET總學(xué)分=總學(xué)分+xf WHERE學(xué)號(hào)=xh; ENDIF; IFtransErr=1THEN ROLLBACK; SETCode=-1; ELSE COMMITWORK; SETCode=1; ENDIF;END;測(cè)試執(zhí)行語(yǔ)句：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é)分初始化存儲(chǔ)過程XS_ZXF_RESET，執(zhí)行語(yǔ)句：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課程號(hào),成績(jī)FROMcjWHERE學(xué)號(hào)=sid; DECLARECONTINUEHANDLERFORNOTFOUND SETmyfound=false; DECLARECONTINUEHANDLERFORSQLEXCEPTIONSETtransErr=1; SETCode=0; SELECTDISTINCT(學(xué)號(hào))INTOxhFROMcj WHERE學(xué)號(hào)=sid; IFxhISNULLTHEN SETCode=-12; LEAVElabel; ENDIF; STARTTRANSACTION; SELECT*