第8章 數(shù)據(jù)庫編程

博學(xué)谷——讓IT教學(xué)更簡單，讓IT學(xué)習(xí)更有效PAGE12PAGE10黑馬程序員《MySQL數(shù)據(jù)庫入門（第2版）》教學(xué)設(shè)計(jì)課程名稱：授課年級：授課學(xué)期：教師姓名：年月課題名稱第8章數(shù)據(jù)庫編程計(jì)劃課時(shí)6課時(shí)教學(xué)引入為了提高SQL語句的重用性，MySQL可以將頻繁使用的業(yè)務(wù)邏輯封裝成程序進(jìn)行存儲，這類程序主要包括存儲過程、函數(shù)、觸發(fā)器等。MySQL在SQL標(biāo)準(zhǔn)的基礎(chǔ)上，擴(kuò)展了一些程序設(shè)計(jì)語言的元素，如變量、流程控制語句等。這些程序設(shè)計(jì)語言的元素可以讓程序更加健全，以提高數(shù)據(jù)庫系統(tǒng)的性能。本章將針對數(shù)據(jù)庫編程的相關(guān)內(nèi)容進(jìn)行講解。教學(xué)目標(biāo)使學(xué)生掌握存儲過程的基本操作，能夠創(chuàng)建、查看、調(diào)用、修改和刪除存儲過程。使學(xué)生掌握存儲函數(shù)的基本操作，能夠創(chuàng)建、查看、調(diào)用和刪除存儲函數(shù)。使學(xué)生掌握變量的使用，能夠查看和修改系統(tǒng)變量，對用戶變量和局部變量進(jìn)行定義和賦值。使學(xué)生掌握流程控制語句的使用，能夠在程序中靈活使用判斷語句、循環(huán)語句和跳轉(zhuǎn)語句控制程序執(zhí)行流程。使學(xué)生掌握錯(cuò)誤觸發(fā)條件和錯(cuò)誤處理，能夠正確定義錯(cuò)誤觸發(fā)條件和錯(cuò)誤處理程序。使學(xué)生掌握游標(biāo)的使用，能夠使用游標(biāo)檢索數(shù)據(jù)。使學(xué)生了解觸發(fā)器，能夠說出觸發(fā)器的概念。使學(xué)生掌握觸發(fā)器的基本使用，能夠創(chuàng)建、查看和刪除觸發(fā)器。教學(xué)重點(diǎn)創(chuàng)建存儲過程調(diào)用存儲過程創(chuàng)建存儲函數(shù)調(diào)用存儲函數(shù)判斷語句循環(huán)語句跳轉(zhuǎn)語句自定義錯(cuò)誤處理程序使用游標(biāo)檢索數(shù)據(jù)觸發(fā)器的基本操作教學(xué)難點(diǎn)自定義錯(cuò)誤名稱自定義錯(cuò)誤處理程序使用游標(biāo)檢索數(shù)據(jù)教學(xué)方式課堂教學(xué)以PPT講授為主，并結(jié)合多媒體進(jìn)行教學(xué)教學(xué)過程第一課時(shí)（創(chuàng)建存儲過程、查看存儲過程、調(diào)用存儲過程、修改存儲過程、刪除存儲過程）一、復(fù)習(xí)鞏固教師通過上節(jié)課作業(yè)的完成情況，對學(xué)生吸收不好的知識點(diǎn)進(jìn)行再次鞏固講解。二、通過直接導(dǎo)入的方式導(dǎo)入新課開發(fā)過程中，經(jīng)常會遇到重復(fù)使用某一功能的情況，為此，MySQL引入了存儲過程。存儲過程是一組可以完成特定功能的SQL語句集合，它可以將常用或復(fù)雜的操作封裝成一個(gè)代碼塊存儲在數(shù)據(jù)庫服務(wù)器中，以便重復(fù)使用，大大減少數(shù)據(jù)庫開發(fā)人員的工作量。本節(jié)將針對存儲過程進(jìn)行詳細(xì)講解。三、新課講解知識點(diǎn)1-創(chuàng)建存儲過程教師通過PPT結(jié)合實(shí)際操作的方式講解創(chuàng)建存儲過程。展示創(chuàng)建存儲過程的基本語法格式。CREATEPROCEDURE存儲過程名([[IN|OUT|INOUT]參數(shù)名稱參數(shù)類型])[characteristic...]routine_body創(chuàng)建存儲過程的語句中，參數(shù)和選項(xiàng)的具體含義如下所示。IN：表示輸入?yún)?shù)，該參數(shù)需要在調(diào)用存儲過程時(shí)傳入。OUT：表示輸出參數(shù)，初始值為NULL，它是將存儲過程中的值保存到OUT指定的參數(shù)中，返回給調(diào)用者。INOUT：表示輸入輸出參數(shù)，既可以作為輸入?yún)?shù)也可以作為輸出參數(shù)。characteristic：表示存儲過程中的例程可以設(shè)置的特征。routine_body：表示存儲過程中的過程體。通過案例演示存儲過程的創(chuàng)建。知識點(diǎn)2-查看存儲過程教師通過PPT結(jié)合實(shí)際操作的方式講解查看存儲過程。使用SHOWPROCEDURESTATUS語句顯示存儲過程的狀態(tài)信息SHOWPROCEDURESTATUS語句顯示存儲過程狀態(tài)信息的基本語法格式如下。SHOWPROCEDURESTATUS[LIKE'pattern']使用SHOWCREATEPROCEDURE語句顯示存儲過程的創(chuàng)建信息使用SHOWCREATEPROCEDURE語句可以顯示存儲過程的創(chuàng)建語句等信息，其基本語法格式如下。SHOWCREATEPROCEDURE存儲過程名;從information_schema.Routines表中查看存儲過程的信息存儲過程的信息存儲在information_schema數(shù)據(jù)庫下的Routines表中，可以通過查詢該表的記錄獲取存儲過程的信息，查詢語句如下。SELECT*FROMinformation_schema.RoutinesWHEREROUTINE_NAME='pro_emp'ANDROUTINE_TYPE='PROCEDURE'\G知識點(diǎn)3-調(diào)用存儲過程教師通過PPT結(jié)合實(shí)際操作的方式講解調(diào)用存儲過程。調(diào)用存儲過程的基本語法格式如下。CALL[數(shù)據(jù)庫名稱.]存儲過程名稱([實(shí)參列表]);通過一個(gè)案例演示存儲過程的調(diào)用。知識點(diǎn)4-修改存儲過程教師通過PPT結(jié)合實(shí)際操作的方式講解修改存儲過程。修改存儲過程，其基本語法格式如下。ALTERPROCEDURE過程名稱[characteristic...];通過案例演示修改存儲過程的特征值。知識點(diǎn)5-刪除存儲過程教師通過PPT結(jié)合實(shí)際操作的方式講解刪除存儲過程。刪除存儲過程的基本語法格式如下所示。DROPPROCEDURE[IFEXISTS]存儲過程名稱;通過案例演示存儲過程的刪除。四、歸納總結(jié)教師回顧本節(jié)課所講的內(nèi)容，并通過測試題的方式引導(dǎo)學(xué)生解答問題并給予指導(dǎo)。五、布置作業(yè)教師通過高校教輔平臺（）布置本節(jié)課作業(yè)以及下節(jié)課的預(yù)習(xí)作業(yè)。第二課時(shí)（創(chuàng)建存儲函數(shù)、查看存儲函數(shù)、調(diào)用存儲函數(shù)、刪除存儲函數(shù)）一、復(fù)習(xí)鞏固教師通過上節(jié)課作業(yè)的完成情況，對學(xué)生吸收不好的知識點(diǎn)進(jìn)行再次鞏固講解。二、通過直接導(dǎo)入的方式導(dǎo)入新課MySQL支持函數(shù)的使用，MySQL中的函數(shù)可以分為兩種，一種是內(nèi)置函數(shù)，一種是自定義函數(shù)。在MySQL中，通常將用戶自定義的函數(shù)稱為存儲函數(shù)，存儲函數(shù)和MySQL內(nèi)置函數(shù)性質(zhì)相同，都用于實(shí)現(xiàn)某種功能。本節(jié)將對存儲函數(shù)進(jìn)行講解。新課講解知識點(diǎn)1-創(chuàng)建存儲函數(shù)教師通過PPT結(jié)合實(shí)際操作的方式講解創(chuàng)建存儲函數(shù)。創(chuàng)建存儲函數(shù)的基本語法格式如下所示。CREATEFUNCTIONfunc_name([func_parameter[...]])RETURNStype[characteristic...]Routine_body通過一個(gè)案例演示存儲函數(shù)的創(chuàng)建。知識點(diǎn)2-查看存儲函數(shù)教師通過PPT結(jié)合實(shí)際操作的方式講解查看存儲函數(shù)。使用SHOWFUNCTIONSTATUS語句顯示存儲函數(shù)的狀態(tài)信息，基本語法格式如下。SHOWFUNCTIONSTATUS[LIKE'pattern'];使用SHOWCREATEFUNCTION語句顯示存儲函數(shù)的創(chuàng)建信息，基本語法格式如下。SHOWCREATEFUNCTION存儲函數(shù)名;在information_schema.Routines表中查看存儲函數(shù)的信息，基本語法格式如下。SELECT*FROMinformation_schema.RoutinesWHEREROUTINE_NAME='存儲函數(shù)名'ANDROUTINE_TYPE='FUNCTION'\G知識點(diǎn)3-調(diào)用存儲函數(shù)教師通過PPT結(jié)合實(shí)際操作的方式講解調(diào)用存儲函數(shù)。調(diào)用存儲函數(shù)的基本語法格式如下所示。SELECT[數(shù)據(jù)庫名.]函數(shù)名1（實(shí)參列表）[函數(shù)名2(實(shí)參列表)…];通過一個(gè)案例演示存儲函數(shù)的調(diào)用。知識點(diǎn)4-刪除存儲函數(shù)教師通過PPT結(jié)合實(shí)際操作的方式講解刪除存儲函數(shù)。刪除存儲函數(shù)的語法格式如下所示。DROPFUNCTION[IFEXISTS]函數(shù)名稱;通過一個(gè)案例演示存儲函數(shù)的刪除。四、歸納總結(jié)教師回顧本節(jié)課所講的內(nèi)容，并通過測試題的方式引導(dǎo)學(xué)生解答問題并給予指導(dǎo)。五、布置作業(yè)教師通過高校教輔平臺（）布置本節(jié)課作業(yè)以及下節(jié)課的預(yù)習(xí)作業(yè)。第三課時(shí)（系統(tǒng)變量、用戶變量、局部變量）一、復(fù)習(xí)鞏固教師通過上節(jié)課作業(yè)的完成情況，對學(xué)生吸收不好的知識點(diǎn)進(jìn)行再次鞏固講解。二、通過直接導(dǎo)入的方式導(dǎo)入新課變量就是在程序執(zhí)行過程中，其值可以改變的量。在MySQL中，可以利用變量存儲程序執(zhí)行過程中涉及的數(shù)據(jù)，如輸入的值、計(jì)算結(jié)果等。根據(jù)變量的作用范圍可以將其劃分為系統(tǒng)變量、用戶變量和局部變量。本節(jié)將針對這3種變量進(jìn)行講解。三、新課講解知識點(diǎn)1-系統(tǒng)變量教師通過PPT結(jié)合實(shí)際操作的方式講解系統(tǒng)變量。系統(tǒng)變量又分為全局（GLOBAL）變量和會話（SESSION）變量，其中全局變量指的是MySQL系統(tǒng)內(nèi)部定義的變量，對所有MySQL客戶端都有效。查看系統(tǒng)變量在MySQL中可以通過SHOW語句顯示所有的系統(tǒng)變量，其語法格式如下所示。SHOW[GLOBAL|SESSION]VARIABLES[LIKE'匹配字符串'|WHERE表達(dá)式];修改系統(tǒng)變量在MySQL中，系統(tǒng)變量可以通過SET語句進(jìn)行修改，修改的語法格式如下所示。SET [GLOBAL|@@GLOBAL.|SESSION|@@SESSION.]系統(tǒng)變量名=新值;知識點(diǎn)2-用戶變量教師通過PPT結(jié)合實(shí)際操作的方式講解用戶變量。用戶變量指的是用戶自己定義的變量，它和連接有關(guān)，即用戶變量僅對當(dāng)前用戶使用的客戶端生效，不能被其他客戶端看到和使用。用戶變量由符號@和變量名組成，在使用用戶變量之前，需要對用戶變量進(jìn)行定義并賦值。MySQL中為用戶變量賦值有以下3種方式。使用SET語句完成賦值。在SELECT語句中使用賦值符號:=完成賦值。使用SELECT…INTO語句完成賦值。知識點(diǎn)3-局部變量教師通過PPT結(jié)合實(shí)際操作的方式講解局部變量。局部變量的作用范圍僅在語句塊BEGIN…END之間，在語句塊BEGIN…END之外，局部變量不能被獲取和修改。局部變量使用DECLARE語句定義，定義的基本語法格式如下。DECLARE變量名1[,變量名2…]數(shù)據(jù)類型[DEFAULT默認(rèn)值];通過案例演示局部變量的使用。四、歸納總結(jié)教師回顧本節(jié)課所講的內(nèi)容，并通過測試題的方式引導(dǎo)學(xué)生解答問題并給予指導(dǎo)。五、布置作業(yè)教師通過高校教輔平臺（）布置本節(jié)課作業(yè)以及下節(jié)課的預(yù)習(xí)作業(yè)。第四課時(shí)（判斷語句、循環(huán)語句、跳轉(zhuǎn)語句）一、復(fù)習(xí)鞏固教師通過上節(jié)課作業(yè)的完成情況，對學(xué)生吸收不好的知識點(diǎn)進(jìn)行再次鞏固講解。二、通過直接導(dǎo)入的方式導(dǎo)入新課程序在執(zhí)行時(shí)，都會按照程序結(jié)構(gòu)（由業(yè)務(wù)邏輯決定）對執(zhí)行流程進(jìn)行控制，程序的結(jié)構(gòu)主要分為順序結(jié)構(gòu)，選擇結(jié)構(gòu)和循環(huán)結(jié)構(gòu)，其中順序結(jié)構(gòu)會按照代碼編寫的先后順序，依次執(zhí)行；選擇結(jié)構(gòu)和循環(huán)結(jié)構(gòu)會根據(jù)程序的執(zhí)行情況調(diào)整和控制程序的執(zhí)行順序。程序執(zhí)行流程由流程控制語句進(jìn)行控制，MySQL中的流程控制語句有IF語句、CASE語句、LOOP語句、LEAVE語句、ITERATE語句、REPEAT語句和WHILE語句等。這些語句大體可以分為3類，分別為判斷語句、循環(huán)語句和跳轉(zhuǎn)語句，本節(jié)分別對這些語句進(jìn)行講解。三、新課講解知識點(diǎn)1-判斷語句教師通過PPT結(jié)合實(shí)際操作的方式講解判斷語句。IF語句IF語句的語法格式如下所示。IF條件表達(dá)式1THEN語句列表[ELSEIF條件表達(dá)式2THEN語句列表]...[ELSE語句列表]ENDIF通過一個(gè)案例演示IF語句的使用。CASE語句CASE語句的語法格式有2種，具體如下所示。#語法格式1CASE表達(dá)式WHEN值1THEN語句列表[WHEN值2THEN語句列表]...[ELSE語句列表]ENDCASE#語法格式2CASEWHEN條件表達(dá)式1THEN語句列表[WHEN條件表達(dá)式2THEN語句列表]...[ELSE語句列表]ENDCASE通過一個(gè)案例演示CASE語句的使用知識點(diǎn)2-循環(huán)語句教師通過PPT結(jié)合實(shí)際操作的方式講解循環(huán)語句。LOOP語句LOOP語句通常用于實(shí)現(xiàn)一個(gè)簡單的循環(huán)，其基本語法格式如下所示。[標(biāo)簽:]LOOP語句列表ENDLOOP[標(biāo)簽];通過一個(gè)案例演示LOOP語句的使用。REPEAT語句REPEAT語句用于循環(huán)執(zhí)行符合條件的語句列表，REPEAT語句的基本語法格式如下所示。[標(biāo)簽:]REPEAT語句列表UNTIL條件表達(dá)式ENDREPEAT[標(biāo)簽]通過示例演示REPEAT語句的使用。WHILE語句WHILE語句也用于循環(huán)執(zhí)行符合條件的語句列表，WHILE語句的基本語法格式如下所示。[標(biāo)簽:]WHILE條件表達(dá)式DO語句列表ENDWHILE[標(biāo)簽]通過示例演示W(wǎng)HILE語句的使用。知識點(diǎn)3-跳轉(zhuǎn)語句教師通過PPT結(jié)合實(shí)際操作的方式講解switch條件語句。跳轉(zhuǎn)語句用于實(shí)現(xiàn)執(zhí)行過程中的流程跳轉(zhuǎn)，跳轉(zhuǎn)語句的基本語法格式如下所示。{ITERATE|LEAVE}標(biāo)簽名;通過案例演示跳轉(zhuǎn)語句的使用。四、歸納總結(jié)教師回顧本節(jié)課所講的內(nèi)容，并通過測試題的方式引導(dǎo)學(xué)生解答問題并給予指導(dǎo)。五、布置作業(yè)教師通過高校教輔平臺（）布置本節(jié)課作業(yè)以及下節(jié)課的預(yù)習(xí)作業(yè)。第五課時(shí)（自定義錯(cuò)誤名稱、自定義錯(cuò)誤處理程序、游標(biāo)的操作流程、使用游標(biāo)檢索數(shù)據(jù)、觸發(fā)器概述、觸發(fā)器的基本操作）一、復(fù)習(xí)鞏固教師通過上節(jié)課作業(yè)的完成情況，對學(xué)生吸收不好的知識點(diǎn)進(jìn)行再次鞏固講解。二、通過情景導(dǎo)入的方式導(dǎo)入新課程序在運(yùn)行過程中可能會發(fā)生錯(cuò)誤，發(fā)生錯(cuò)誤時(shí)，默認(rèn)情況下，MySQL將自動終止程序的執(zhí)行。有些時(shí)候，如果不希望程序因?yàn)殄e(cuò)誤而停止執(zhí)行，可以通過MySQL中的錯(cuò)誤處理機(jī)制自定義錯(cuò)誤名稱和錯(cuò)誤處理程序，讓程序遇到警告或錯(cuò)誤時(shí)也能繼續(xù)執(zhí)行，從而增強(qiáng)程序處理問題的能力。接下來對錯(cuò)誤處理進(jìn)行講解。三、新課講解知識點(diǎn)1-自定義錯(cuò)誤名稱教師通過PPT結(jié)合實(shí)際操作的方式講解自定義錯(cuò)誤名稱。在MySQL中可以使用DECLARE語句為錯(cuò)誤聲明一個(gè)名稱，聲明的基本語法格式如下所示。DECLARE錯(cuò)誤名稱CONDITIONFOR錯(cuò)誤類型;通過案例演示自定義錯(cuò)誤名稱。知識點(diǎn)2-自定義錯(cuò)誤處理程序教師通過PPT結(jié)合實(shí)際操作的方式講解自定義錯(cuò)誤處理程序。自定義錯(cuò)誤處理程序的基本語法格式如下所示。DECLARE錯(cuò)誤處理方式HANDLERFOR錯(cuò)誤類型[,錯(cuò)誤類型...]程序語句段錯(cuò)誤類型有6種可選值，分別如下。sqlstate_value：匹配SQLSTATE錯(cuò)誤代碼。condition_name：匹配DECLARE定義的錯(cuò)誤條件名稱。SQLWARNING：匹配所有以01開頭的SQLSTATE錯(cuò)誤代碼。NOTFOUND：匹配所有以02開頭的SQLSTATE錯(cuò)誤代碼。SQLEXCEPTION：匹配所有沒有被SQLWARNING或NOTFOUND捕獲的SQLSTATE錯(cuò)誤代碼。mysql_error_code：匹配mysql_error_code類型的錯(cuò)誤代碼。通過案例演示自定義錯(cuò)誤處理程序的應(yīng)用。知識點(diǎn)3-游標(biāo)的操作流程教師通過PPT結(jié)合實(shí)際操作的方式講解游標(biāo)的操作流程。定義游標(biāo)定義游標(biāo)的基本語法格式如下所示。DECLARE游標(biāo)名稱CURSORFORSELECT語句打開游標(biāo)聲明游標(biāo)之后，要想從游標(biāo)中提取數(shù)據(jù)，需要先打開游標(biāo)。在MySQL中，打開游標(biāo)通過OPEN關(guān)鍵字實(shí)現(xiàn)，其語法格式如下。OPEN游標(biāo)名稱利用游標(biāo)檢索數(shù)據(jù)打開游標(biāo)之后，就可以通過游標(biāo)檢索SELECT語句返回的結(jié)果集中的數(shù)據(jù)，游標(biāo)檢索數(shù)據(jù)的基本語法格式如下所示。FETCH游標(biāo)名稱INTO變量名1[,變量名2]...關(guān)閉游標(biāo)游標(biāo)檢索完數(shù)據(jù)后，應(yīng)該利用MySQL提供的語法關(guān)閉游標(biāo)，釋放游標(biāo)占用的MySQL服務(wù)器的內(nèi)存資源，關(guān)閉游標(biāo)的基本語法格式如下所示。CLOSE游標(biāo)名稱知識點(diǎn)4-使用游標(biāo)檢索數(shù)據(jù)教師通過PPT結(jié)合實(shí)際操作的方式講解使用游標(biāo)檢索數(shù)據(jù)。通過具體的案例演示使用游標(biāo)檢索數(shù)據(jù)。知識點(diǎn)5-觸發(fā)器概述教師通過PPT結(jié)合實(shí)際操作的方式講解觸發(fā)器概述。觸發(fā)器可以看作是一種特殊的存儲過程，它與存儲過程的區(qū)別在于，存儲過程使用CALL語句調(diào)用時(shí)才會執(zhí)行，而觸發(fā)器會在預(yù)先定義好的事件（例如INSERT、DELETE等操作）發(fā)生時(shí)自動調(diào)用。觸發(fā)器具有以下優(yōu)點(diǎn)。觸發(fā)器自動執(zhí)行，在觸發(fā)器相關(guān)聯(lián)的數(shù)據(jù)表中的數(shù)據(jù)發(fā)生修改，觸發(fā)器中定義的語句會自動執(zhí)行。觸發(fā)器對數(shù)據(jù)進(jìn)行安全校驗(yàn)，保障數(shù)據(jù)安全。通過和觸發(fā)器相關(guān)聯(lián)的表，可以實(shí)現(xiàn)表數(shù)據(jù)的級聯(lián)更改，在一定程度上保證了數(shù)據(jù)的完整性。知識點(diǎn)6-觸發(fā)器的基本操作教師通過PPT結(jié)合實(shí)際操作的方式講解觸發(fā)器的基本操作。創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器的基本語法格式如下所示。CREATETRIGGER觸發(fā)器名稱觸發(fā)時(shí)機(jī)觸發(fā)事件ON數(shù)據(jù)表名FOREACHROW觸發(fā)程序查看觸發(fā)器利用SHOWTRIGGER語句查看觸發(fā)器信息的語法格式如下所示。SHOWTRIGGERS;通過SELECT語句查看該數(shù)據(jù)表獲取觸發(fā)器信息，通過TRIGGERS數(shù)據(jù)表查詢觸發(fā)器的語法格式如下所示。SELECT*FROMinformation_schema.triggers[WHEREtrigger_name='觸發(fā)器名稱'];觸發(fā)觸發(fā)器通過案例演示觸發(fā)器的觸發(fā)。刪除觸發(fā)器MySQ