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