《MySQL數(shù)據(jù)庫(kù)應(yīng)用案例教程》651-5（馬潔）教案 第17課 存儲(chǔ)過(guò)程和函數(shù)（一）

PAGE12PAGE12PAGE13PAGE13

課題存儲(chǔ)過(guò)程和函數(shù)（一）課時(shí)2課時(shí)（90min）教學(xué)目標(biāo)知識(shí)技能目標(biāo)：（1）了解存儲(chǔ)過(guò)程和函數(shù)的相關(guān)概念（2）掌握創(chuàng)建并調(diào)用存儲(chǔ)過(guò)程和函數(shù)的方法（3）掌握變量、條件和處理程序、游標(biāo)及流程控制的使用素質(zhì)目標(biāo)：（1）培養(yǎng)做事要了解全貌的習(xí)慣（2）了解數(shù)據(jù)庫(kù)前沿技術(shù)，緊跟時(shí)代發(fā)展教學(xué)重難點(diǎn)教學(xué)重點(diǎn)：存儲(chǔ)過(guò)程和函數(shù)的相關(guān)概念教學(xué)難點(diǎn)：創(chuàng)建并調(diào)用存儲(chǔ)過(guò)程和函數(shù)的方法教學(xué)方法案例分析法、問(wèn)答法、討論法、講授法、實(shí)踐法教學(xué)用具電腦、投影儀、多媒體課件、教材教學(xué)設(shè)計(jì)第1節(jié)課：→→→傳授新知（38min）第2節(jié)課：→傳授新知（20min）→課堂實(shí)訓(xùn)（15min）→課堂小結(jié)（3min）→作業(yè)布置（2min）教學(xué)過(guò)程主要教學(xué)內(nèi)容及步驟設(shè)計(jì)意圖第一節(jié)課課前任務(wù)【教師】布置課前任務(wù)，和學(xué)生負(fù)責(zé)人取得聯(lián)系，讓其提醒同學(xué)通過(guò)文旌課堂APP或其他學(xué)習(xí)軟件，預(yù)習(xí)本節(jié)課要學(xué)習(xí)的知識(shí)【學(xué)生】完成課前任務(wù)通過(guò)課前任務(wù)，使學(xué)生預(yù)習(xí)本節(jié)課要學(xué)習(xí)的知識(shí)，增加學(xué)生的學(xué)習(xí)興趣考勤（2min）【教師】使用文旌課堂APP進(jìn)行簽到【學(xué)生】班干部報(bào)請(qǐng)假人員及原因培養(yǎng)學(xué)生的組織紀(jì)律性，掌握學(xué)生的出勤情況問(wèn)題導(dǎo)入（5min）【教師】提出以下問(wèn)題：存儲(chǔ)過(guò)程和函數(shù)是什么，對(duì)比單條SQL語(yǔ)句有什么優(yōu)勢(shì)？·通過(guò)問(wèn)題導(dǎo)入，引導(dǎo)學(xué)生主動(dòng)思考，激發(fā)學(xué)生的學(xué)習(xí)興趣傳授新知（38min）【教師】通過(guò)學(xué)生的回答引入要講的知識(shí)，講解存儲(chǔ)過(guò)程和函數(shù)的相關(guān)概念，以及創(chuàng)建并調(diào)用存儲(chǔ)過(guò)程和函數(shù)的相關(guān)知識(shí)13.1存儲(chǔ)過(guò)程和函數(shù)概述通過(guò)前面章節(jié)的學(xué)習(xí)，相信用戶已經(jīng)能夠編寫(xiě)操作單表或者多表的單條SQL語(yǔ)句，但是針對(duì)表的一個(gè)完整操作往往不是單條SQL語(yǔ)句就能實(shí)現(xiàn)的，而是需要一組SQL語(yǔ)句來(lái)實(shí)現(xiàn)。?【教師】隨機(jī)邀請(qǐng)學(xué)生回答以下問(wèn)題：要完成一個(gè)購(gòu)買商品的訂單的處理，一般需要考慮哪些問(wèn)題？?【學(xué)生】聆聽(tīng)、思考、回答?【教師】總結(jié)學(xué)生的回答例如，要完成一個(gè)購(gòu)買商品的訂單的處理，一般需要考慮以下幾步：（1）在生成訂單之前，首先需要查看商品庫(kù)存中是否有相應(yīng)商品；（2）如果商品庫(kù)存中不存在相應(yīng)商品，需要向供應(yīng)商訂貨；（3）如果商品庫(kù)存中存在相應(yīng)商品，需要預(yù)定商品，并修改庫(kù)存數(shù)量。對(duì)于上述完整操作，顯然不是單條SQL語(yǔ)句就能實(shí)現(xiàn)的。在實(shí)際應(yīng)用中，一個(gè)完整的操作會(huì)包含多條SQL語(yǔ)句，并且在執(zhí)行過(guò)程中還需要根據(jù)前面語(yǔ)句的執(zhí)行結(jié)果有選擇的執(zhí)行后面的語(yǔ)句。為此，可將一個(gè)完整操作中所包含的多條SQL語(yǔ)句創(chuàng)建為存儲(chǔ)過(guò)程或函數(shù)，以方便應(yīng)用。存儲(chǔ)過(guò)程和函數(shù)可以簡(jiǎn)單地理解為一組經(jīng)過(guò)編譯并保存在數(shù)據(jù)庫(kù)中的SQL語(yǔ)句的集合，可以隨時(shí)被調(diào)用。?【教師】隨機(jī)邀請(qǐng)學(xué)生回答以下問(wèn)題：存儲(chǔ)過(guò)程和函數(shù)具有哪些優(yōu)點(diǎn)和缺點(diǎn)？?【學(xué)生】聆聽(tīng)、思考、回答?【教師】總結(jié)學(xué)生的回答存儲(chǔ)過(guò)程和函數(shù)具有以下優(yōu)點(diǎn)：允許標(biāo)準(zhǔn)組件式編程：存儲(chǔ)過(guò)程和函數(shù)在創(chuàng)建后可以在程序中被多次調(diào)用，有效提高了SQL語(yǔ)句的重用性、共享性和可移值性。較快的執(zhí)行速度：如果某一操作包含大量的事務(wù)處理代碼，并且被多次執(zhí)行，那么存儲(chǔ)過(guò)程要比批處理的執(zhí)行速度快很多。因?yàn)榇鎯?chǔ)過(guò)程是預(yù)編譯的，在首次運(yùn)行一個(gè)存儲(chǔ)過(guò)程時(shí)，查詢優(yōu)化器會(huì)對(duì)其進(jìn)行分析優(yōu)化，并將最終執(zhí)行計(jì)劃存儲(chǔ)在系統(tǒng)中，而批處理的事務(wù)處理語(yǔ)句在每次運(yùn)行時(shí)都要進(jìn)行編譯和優(yōu)化。減少網(wǎng)絡(luò)流量：對(duì)于大量的SQL語(yǔ)句，將其組織成存儲(chǔ)過(guò)程，會(huì)比一條一條地調(diào)用SQL語(yǔ)句要大大節(jié)省網(wǎng)絡(luò)流量，降低網(wǎng)絡(luò)負(fù)載。安全：數(shù)據(jù)庫(kù)管理員通過(guò)設(shè)置執(zhí)行某一存儲(chǔ)過(guò)程的權(quán)限，從而限制相應(yīng)數(shù)據(jù)的訪問(wèn)權(quán)限，避免非授權(quán)用戶對(duì)數(shù)據(jù)的訪問(wèn)，保證數(shù)據(jù)的安全。除上述優(yōu)點(diǎn)外，存儲(chǔ)過(guò)程和函數(shù)也存在一定的缺陷。首先，存儲(chǔ)過(guò)程和函數(shù)的編寫(xiě)比單個(gè)SQL語(yǔ)句的編寫(xiě)要復(fù)雜很多，需要用戶具有更高的技能和更豐富的經(jīng)驗(yàn)；其次，在編寫(xiě)存儲(chǔ)過(guò)程和函數(shù)時(shí)，需要?jiǎng)?chuàng)建這些數(shù)據(jù)庫(kù)對(duì)象的權(quán)限。13.2創(chuàng)建并調(diào)用存儲(chǔ)過(guò)程和函數(shù)?【教師】組織學(xué)生掃碼觀看“創(chuàng)建并調(diào)用存儲(chǔ)過(guò)程和函數(shù)”視頻（詳見(jiàn)教材），讓學(xué)生對(duì)相關(guān)知識(shí)有一個(gè)大致了解存儲(chǔ)程序可以分為存儲(chǔ)過(guò)程和函數(shù)。存儲(chǔ)過(guò)程和函數(shù)的操作主要包括創(chuàng)建存儲(chǔ)過(guò)程和函數(shù)、調(diào)用存儲(chǔ)過(guò)程和函數(shù)、查看存儲(chǔ)過(guò)程和函數(shù)，以及修改和刪除存儲(chǔ)過(guò)程和函數(shù)。本節(jié)主要介紹如何創(chuàng)建和調(diào)用存儲(chǔ)過(guò)程和函數(shù)。13.2.1創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程使用SQL語(yǔ)句CREATEPROCEDURE來(lái)實(shí)現(xiàn)，其語(yǔ)法形式如下：CREATEPROCEDUREproc_name([proc_parameter[,…])[characteristic…]routine_body（1）CREATEPROCEDURE為創(chuàng)建存儲(chǔ)過(guò)程的關(guān)鍵字。（2）proc_name表示要?jiǎng)?chuàng)建的存儲(chǔ)過(guò)程名。（3）proc_parameter表示存儲(chǔ)過(guò)程的參數(shù)，參數(shù)形式如下：[IN|OUT|INOUT]parameter_nameTYPE其中，IN表示輸入?yún)?shù)，可把外界的數(shù)據(jù)傳遞到存儲(chǔ)過(guò)程當(dāng)中；OUT表示輸出參數(shù)，可把存儲(chǔ)過(guò)程的運(yùn)算結(jié)果傳遞到外界；INOUT表示輸入輸出參數(shù)，既可以把外界的數(shù)據(jù)傳遞到存儲(chǔ)過(guò)程當(dāng)中，又可以把存儲(chǔ)過(guò)程的運(yùn)算結(jié)果傳遞到外界；parameter_name表示參數(shù)名；TYPE表示參數(shù)的數(shù)據(jù)類型。【提示】需要注意的是，存儲(chǔ)過(guò)程的參數(shù)名不要與數(shù)據(jù)表中的字段名重復(fù)，否則系統(tǒng)會(huì)報(bào)錯(cuò)。（4）characteristic表示存儲(chǔ)過(guò)程的特性，可取值及其意義如下：LANGUAGESQL：表示存儲(chǔ)過(guò)程的routine_body部分使用SQL語(yǔ)言編寫(xiě)，當(dāng)前系統(tǒng)支持的語(yǔ)言為SQL。[NOT]DETERMINISTIC：DETERMINISTIC表示存儲(chǔ)過(guò)程的執(zhí)行結(jié)果是確定的，就是每次輸入相同的參數(shù)并執(zhí)行存儲(chǔ)過(guò)程后，得到的結(jié)果是相同的；默認(rèn)為NOTDETERMINISTIC，表示執(zhí)行結(jié)果不確定，即相同的輸入可能得到不同的結(jié)果。{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}：指明子程序使用SQL語(yǔ)句的限制。CONTAINSSQL為默認(rèn)值，表示子程序包含SQL語(yǔ)句，但不包含讀或?qū)憯?shù)據(jù)的語(yǔ)句；NOSQL表示子程序不包含SQL語(yǔ)句；READSSQLDATA表示子程序包含讀取數(shù)據(jù)的語(yǔ)句，但不包含寫(xiě)數(shù)據(jù)的語(yǔ)句；MODIFIESSQLDATA表示子程序包含寫(xiě)入數(shù)據(jù)的語(yǔ)句。SQLSECURITY{DEFINER|INVOKER}：指定可執(zhí)行存儲(chǔ)過(guò)程的用戶，DEFINER表示只有創(chuàng)建者才能執(zhí)行，INVOKER表示擁有權(quán)限的調(diào)用者可以執(zhí)行。COMMENT'string'：表示存儲(chǔ)過(guò)程或者函數(shù)的注釋信息。（5）routine_body表示需要執(zhí)行的SQL語(yǔ)句的集合，可以使用BEGIN表示開(kāi)始，使用END表示結(jié)束?！緦?shí)例13-1】創(chuàng)建一個(gè)名為proc的簡(jiǎn)單存儲(chǔ)過(guò)程，用于獲取goods表中的記錄數(shù)。在創(chuàng)建存儲(chǔ)過(guò)程前首先登錄MySQL，并選擇數(shù)據(jù)庫(kù)db_shop。然后執(zhí)行以下語(yǔ)句：mysql>DELIMITER$$mysql>CREATEPROCEDUREproc(OUTnumINT)->BEGIN->SELECTCOUNT(*)INTOnumFROMgoods;->END$$QueryOK,0rowsaffected(0.03sec)mysql>DELIMITER;【提示】“DELIMITER$$”的作用是將語(yǔ)句的結(jié)束符“;”修改為“$$”，這樣存儲(chǔ)過(guò)程中的SQL語(yǔ)句結(jié)束符“;”就不會(huì)被MySQL解釋成語(yǔ)句的結(jié)束而提示錯(cuò)誤。在存儲(chǔ)過(guò)程創(chuàng)建完成后，應(yīng)使用“DELIMITER;”語(yǔ)句將結(jié)束符修改為默認(rèn)結(jié)束符。13.2.2創(chuàng)建存儲(chǔ)函數(shù)創(chuàng)建存儲(chǔ)函數(shù)使用SQL語(yǔ)句CREATEFUNCTION來(lái)實(shí)現(xiàn)，其語(yǔ)法形式如下：CREATEFUNCTIONfunc_name([parameter_name[,…])RETURNSTYPE[characteristic…]routine_bodyCREATEFUNCTION為創(chuàng)建存儲(chǔ)函數(shù)的關(guān)鍵字；func_name表示存儲(chǔ)函數(shù)名；parameter_name表示存儲(chǔ)函數(shù)參數(shù)名；TYPE表示函數(shù)返回值的數(shù)據(jù)類型；characteristic指定存儲(chǔ)函數(shù)的特性，取值與創(chuàng)建存儲(chǔ)過(guò)程時(shí)相同；routine_body表示函數(shù)體。【實(shí)例13-2】創(chuàng)建一個(gè)名為func的簡(jiǎn)單存儲(chǔ)函數(shù)，用于獲取goods表中的記錄數(shù)。在創(chuàng)建存儲(chǔ)函數(shù)前首先登錄MySQL，并選擇數(shù)據(jù)庫(kù)db_shop。SQL語(yǔ)句的執(zhí)行結(jié)果如下：mysql>DELIMITER$$mysql>CREATEFUNCTIONfunc()->RETURNSINT(11)->RETURN(SELECTCOUNT(*)FROMgoods)->$$QueryOK,0rowsaffected(0.03sec)mysql>DELIMITER;【提示】RETURNSTYPE子句對(duì)于存儲(chǔ)函數(shù)而言是必須存在的，如果RETURN子句返回值的數(shù)據(jù)類型與RETURNSTYPE子句指定的數(shù)據(jù)類型不同，MySQL會(huì)將返回值強(qiáng)制轉(zhuǎn)換為RETURNSTYPE子句指定的類型。13.2.3調(diào)用存儲(chǔ)過(guò)程和函數(shù)存儲(chǔ)過(guò)程必須使用關(guān)鍵字CALL調(diào)用，而存儲(chǔ)函數(shù)與MySQL內(nèi)置函數(shù)的調(diào)用相同，使用關(guān)鍵字SELECT。1．調(diào)用存儲(chǔ)過(guò)程通常使用關(guān)鍵字CALL調(diào)用存儲(chǔ)過(guò)程，其語(yǔ)法形式如下：CALLprocedure_name([parameter[,…]]);其中的parameter表示變量名，存儲(chǔ)過(guò)程的返回值將賦予該變量?！緦?shí)例13-3】調(diào)用實(shí)例13-1創(chuàng)建的存儲(chǔ)過(guò)程proc()，查看其返回值。首先登錄MySQL，并選擇數(shù)據(jù)庫(kù)db_shop。SQL語(yǔ)句及其執(zhí)行結(jié)果如下：mysql>CALLproc(@num);QueryOK,1rowaffected(0.06sec)mysql>SELECT@num;++|@num|++|10|++1rowinset(0.01sec)上述語(yǔ)句中，使用CALLproc(@num);語(yǔ)句調(diào)用存儲(chǔ)過(guò)程后，系統(tǒng)會(huì)將返回值賦予變量@num；使用SELECT語(yǔ)句查看變量@num值，結(jié)果為10。調(diào)用存儲(chǔ)過(guò)程的執(zhí)行結(jié)果與直接執(zhí)行查詢語(yǔ)句SELECTCOUNT(*)FROMgoods;的執(zhí)行結(jié)果相同，但是存儲(chǔ)過(guò)程的好處在于處理邏輯都封裝在數(shù)據(jù)庫(kù)端，調(diào)用者不需要了解中間的處理邏輯，當(dāng)處理邏輯發(fā)生變化時(shí)，只需要修改存儲(chǔ)過(guò)程即可，而對(duì)調(diào)用者的程序完全沒(méi)有影響。2．調(diào)用存儲(chǔ)函數(shù)通常使用關(guān)鍵字SELECT調(diào)用存儲(chǔ)函數(shù)，其語(yǔ)法形式如下：SELECTfunction_name([parameter[,……]]);【實(shí)例13-4】調(diào)用實(shí)例13-2創(chuàng)建的存儲(chǔ)函數(shù)func()，查看其返回值。首先登錄MySQL，并選擇數(shù)據(jù)庫(kù)db_shop。然后創(chuàng)建存儲(chǔ)過(guò)程并定義變量，SQL語(yǔ)句及其執(zhí)行結(jié)果如下：mysql>SELECTfunc();++|func()|++|10|++1rowinset(0.04sec)其返回值同樣為10。13.2.4使用圖形化工具創(chuàng)建存儲(chǔ)過(guò)程和函數(shù)使用NavicatforMySQL也可以創(chuàng)建存儲(chǔ)過(guò)程和函數(shù)，具體操作如下。步驟

1使用NavicatforMySQL連接MySQL后，雙擊需要操作的數(shù)據(jù)庫(kù)“test_db”，然后單擊“函數(shù)”按鈕。步驟

2單擊“新建函數(shù)”按鈕，選擇需要?jiǎng)?chuàng)建的類型，此處選擇創(chuàng)建存儲(chǔ)過(guò)程，如圖13-1所示。步驟

3在編輯區(qū)填寫(xiě)存儲(chǔ)過(guò)程需要的參數(shù)，單擊編輯區(qū)左下方的“+”按鈕可以添加參數(shù)，單擊“－”按鈕可以刪除參數(shù)，如果存儲(chǔ)過(guò)程沒(méi)有參數(shù)，直接單擊“完成”按鈕即可……（詳見(jiàn)教材）【學(xué)生】聆聽(tīng)、思考、記錄通過(guò)教師的講解和演示，使學(xué)生了解存儲(chǔ)過(guò)程和函數(shù)的相關(guān)概念，以及創(chuàng)建并調(diào)用存儲(chǔ)過(guò)程和函數(shù)的相關(guān)知識(shí)第二節(jié)課問(wèn)題導(dǎo)入（5min）【教師】提出以下問(wèn)題：什么是表達(dá)式？【學(xué)生】思考、舉手回答通過(guò)問(wèn)題導(dǎo)入，引導(dǎo)學(xué)生主動(dòng)思考，激發(fā)學(xué)生的學(xué)習(xí)興趣傳授新知（20min）【教師】通過(guò)學(xué)生的回答引入新知，講解存儲(chǔ)過(guò)程和函數(shù)的表達(dá)式的相關(guān)知識(shí)13.3關(guān)于存儲(chǔ)過(guò)程和函數(shù)的表達(dá)式?【教師】組織學(xué)生掃碼觀看“變量、條件和游標(biāo)的使用”視頻（詳見(jiàn)教材），讓學(xué)生對(duì)相關(guān)知識(shí)有一個(gè)大致了解13.2

節(jié)介紹了存儲(chǔ)過(guò)程和函數(shù)的創(chuàng)建方法，本節(jié)詳細(xì)介紹存儲(chǔ)過(guò)程和函數(shù)中所包含的表達(dá)式語(yǔ)句。此處的表達(dá)式同其他高級(jí)語(yǔ)言中一樣，主要由變量、運(yùn)算符和流程控制語(yǔ)句構(gòu)成。13.3.1變量變量是表達(dá)式中最基本的元素，可用于存儲(chǔ)臨時(shí)數(shù)據(jù)。本節(jié)簡(jiǎn)單介紹變量的分類，以及在存儲(chǔ)過(guò)程和函數(shù)中應(yīng)用變量的方法。1．變量的分類?【教師】隨機(jī)邀請(qǐng)學(xué)生回答以下問(wèn)題：變量有哪些類型？?【學(xué)生】聆聽(tīng)、思考、回答?【教師】總結(jié)學(xué)生的回答MySQL中的變量可分為以下三類。用戶變量（User-DefinedVariables）：帶有前綴@，只能被定義它的用戶使用，作用于當(dāng)前整個(gè)連接，當(dāng)前連接斷開(kāi)后，所定義的用戶變量會(huì)被全部釋放。用戶變量不用提前定義就可以直接使用。局部變量（LocalVariables）：沒(méi)有前綴，一般用于SQL語(yǔ)句塊中，比如存儲(chǔ)過(guò)程的BEGIN…END中。其作用域僅限于該語(yǔ)句塊，在語(yǔ)句塊執(zhí)行完畢后，局部變量就會(huì)被釋放。局部變量使用前需要先通過(guò)DECLARE聲明。如沒(méi)有聲明，則初始值為NULL。系統(tǒng)變量（ServerSystemVariables）：帶有前綴@@，MySQL有許多已經(jīng)設(shè)置默認(rèn)值的系統(tǒng)變量。系統(tǒng)變量包含全局變量和會(huì)話變量。全局變量會(huì)影響整個(gè)服務(wù)器，而會(huì)話變量只影響個(gè)人客戶端連接。2．在存儲(chǔ)過(guò)程和函數(shù)中應(yīng)用變量局部變量可以在子程序中定義并應(yīng)用，其作用范圍是BEGIN…END語(yǔ)句塊。（1）定義變量。在存儲(chǔ)過(guò)程中使用DECLARE語(yǔ)句定義局部變量，其語(yǔ)法形式如下：DECLAREvar_name[,…]type[DEFAULTvalue];上述語(yǔ)句中，var_name為局部變量名稱，type為變量的數(shù)據(jù)類型，DEFAULTvalue是為變量指定的默認(rèn)值。如果沒(méi)有DEFAULTvalue，初始值為NULL。例如，定義一個(gè)INT類型的變量，名稱為var1：DECLAREvar1INT;【提示】變量的定義必須在復(fù)合語(yǔ)句開(kāi)頭，并且在任何其他語(yǔ)句前面。也就是說(shuō)，DECLARE語(yǔ)句在存儲(chǔ)過(guò)程和函數(shù)中使用時(shí)，必須出現(xiàn)在BEGIN…END語(yǔ)句塊的最前面，并且變量名不區(qū)分大小寫(xiě)?？梢砸淮温暶鞫鄠€(gè)相同類型的變量。（2）為變量賦值。定義變量之后，可以使用SET關(guān)鍵字為變量賦值，語(yǔ)法形式如下：SETvar_name=expr[,var_name=expr]…;為前面定義的變量var1賦值，具體如下：SETvar1=3;變量值可以為常量或者表達(dá)式。另外，也可以使用SELECT…INTO…查詢語(yǔ)句將查詢結(jié)果賦給變量，這要求查詢結(jié)果必須只有一行，具體語(yǔ)法形式如下：SELECTcol_name[,……]INTOvar_name[,……]FROMtable_name;col_name為字段名，var_name為變量名?！緦?shí)例13-5】執(zhí)行SQL語(yǔ)句，定義變量并為其賦值。首先登錄MySQL，并選擇數(shù)據(jù)庫(kù)db_shop。SQL語(yǔ)句及其執(zhí)行結(jié)果如下：mysql>DELIMITER$$mysql>CREATEPROCEDUREproc1()->BEGIN->DECLAREvar1,var2,var3,g_idINT;->SETvar1=1,var2=2;->SETvar3=var1+var2;->SELECTidintog_idFROMgoodsWHEREid=1;->END$$QueryOK,0rowsaffected(0.10sec)mysql>DELIMITER;13.3.2定義條件和處理程序條件和處理程序是MySQL提供的一種異常處理機(jī)制，定義條件是事先定義程序執(zhí)行過(guò)程中可能會(huì)遇到的問(wèn)題；定義處理程序是定義在遇到問(wèn)題時(shí)執(zhí)行的相應(yīng)處理方法，并且保證存儲(chǔ)過(guò)程和函數(shù)在遇到問(wèn)題時(shí)不終止。1．定義條件在MySQL中定義條件使用DECLARE…CONDITION語(yǔ)句，其語(yǔ)法形式如下：DECLAREcondition_nameCONDITIONFOR[condition_type];上述語(yǔ)句中，condition_name表示條件名。condition_type表示條件的類型，其可取值及其意義如下：mysql_error_code：表示數(shù)值類型錯(cuò)誤代碼。sqlstate_value：表示長(zhǎng)度為5的字符串類型錯(cuò)誤代碼。下面以名為“not_found_database”的條件“ERROR1049(42000)”的定義為例，來(lái)看看這兩種形式的區(qū)別。使用mysql_error_code形式：DECLAREnot_found_databaseCONDITIONFOR1049;使用sqlstate_value形式：DECLAREnot_found_databaseCONDITIONFORSQLSTATE'42000';【提示】數(shù)值類型的錯(cuò)誤代碼不要使用0，因?yàn)?表示成功而不是錯(cuò)誤；字符串類型的錯(cuò)誤代碼不要使用'00'，因?yàn)?00'表示成功而不是錯(cuò)誤。2．定義處理程序在定義條件之后，可以使用DECLARE…HANDLER語(yǔ)句定義處理程序，語(yǔ)法形式如下：DECLAREhandler_typeHANDLERFORcondition_value[...]statement下面簡(jiǎn)單介紹上述語(yǔ)句中各組成部分及其意義。（1）handler_type為異常處理方式，可取值及其意義如下：CONTINUE：表示遇到錯(cuò)誤不處理，程序繼續(xù)執(zhí)行。EXIT：表示遇到錯(cuò)誤立即退出程序。UNDO：表示遇到錯(cuò)誤后撤回之前的操作，目前MySQL暫不支持該操作。（2）condition_value表示錯(cuò)誤值，可取值及其意義如下：mysql_error_code：表示數(shù)值類型的錯(cuò)誤代碼。sqlstate_value：表示包含5個(gè)字符的字符串錯(cuò)誤值。condition_name：表示使用DECLARE…CONDITION語(yǔ)句定義的條件名。SQLWARNING：匹配所有以01開(kāi)頭的SQLSTATE錯(cuò)誤代碼。NOTFOUND：匹配所有以02開(kāi)頭的SQLSTATE錯(cuò)誤代碼。SQLEXCEPTION：匹配所有未被SQLWARNING和NOTFOUND捕獲的SQLSTATE錯(cuò)誤代碼。（3）statement為程序語(yǔ)句段，表示在遇到定義的異常條件時(shí)，需要執(zhí)行的存儲(chǔ)過(guò)程或函數(shù)。定義處理程序有以下6種方法：方法一：捕獲sqlstate_valueDECLARECONTINUEHANDLERFORSQLSTATE'42s02'SET@info='cannotfind';意義為，如果遇到sqlstate_value值為'42s02'的異常，執(zhí)行CONTINUE操作，并輸出“cannotfind”信息。方法二：捕獲mysql_error_codeDECLARECONTINUEHANDLERFOR1146SET@info='cannotfind';意義為，如果遇到mysql_error_code值為1146的異常，執(zhí)行CONTINUE操作，并輸出“cannotfind”信息。方法三：先定義條件，然后再調(diào)用條件DECLAREcan_not_findCONDITIONFOR1146;DECLARECONTINUEHANDLERFORcan_not_findSET@info='cannotfind';意義為，先定義can_not_find條件，遇到1146錯(cuò)誤就執(zhí)行CONTINUE操作。方法四：使用SQLWARNINGDECLAREEXITHANDLERFORSQLWARNINGSET@info='error';意義為，捕獲所有以01開(kāi)頭的sqlstate_value值，然后執(zhí)行EXIT操作，并輸出“error”信息。方法五：使用NOTFOUNDDECLAREEXITHANDLERFORNOTFOUNDSET@info='cannotfind';意義為，捕獲所有以02開(kāi)頭的sqlstate_value值，然后執(zhí)行EXIT操作，并輸出“cannotfind”信息。方法六：使用SQLEXCEPTIONDECLAREEXITHANDLERFORSQLEXCEPTIONSET@info='error';意義為，捕獲所有沒(méi)有被SQLWARNING和NOTFOUND捕獲的sqlstate_value值，然后執(zhí)行EXIT操作，并輸出“error”信息?！緦?shí)例13-6】執(zhí)行SQL語(yǔ)句，在存儲(chǔ)過(guò)程中定義條件和處理程序，體驗(yàn)異常處理機(jī)制在存儲(chǔ)過(guò)程中的作用。步驟1登錄MySQL，并選擇任一數(shù)據(jù)庫(kù)，之后執(zhí)行以下SQL語(yǔ)句，創(chuàng)建t_handler表。CREATETABLEt_handler(s1INT,PRIMARYKEY(s1));步驟2在存儲(chǔ)過(guò)程和函數(shù)中定義條件和處理程序，SQL語(yǔ)句及其執(zhí)行結(jié)果如下。mysql>DELIMITER$$mysql>CREATEPROCEDUREhandlerdemo()->BEGIN->DECLARECONTINUEHANDLERFORSQLSTATE'23000'SET@x2=1;->SET@x=1;->INSERTINTOt_handlerVALUES(1);->SET@x=2;->INSERTINTOt_handlerVALUES(1);->SET@x=3;->END$$QueryOK,0rowsaffected(0.03sec)mysql>DELIMITER;步驟3調(diào)用存儲(chǔ)過(guò)程，查看變量x的值……（詳見(jiàn)教材）13.3.3游標(biāo)的使用在存儲(chǔ)過(guò)程和函數(shù)中，當(dāng)查詢語(yǔ)句返回多條記錄時(shí)，可以使用游標(biāo)對(duì)結(jié)果集進(jìn)行逐條讀取。本節(jié)介紹定義、打開(kāi)、使用和關(guān)閉游標(biāo)的方法。1．定義游標(biāo)在MySQL中，使用DECLARE關(guān)鍵字來(lái)定義游標(biāo)，其語(yǔ)法形式如下：DECLAREcursor_nameCURSORFORselect_statement;上述語(yǔ)句中，cursor_name表示游標(biāo)名，select_statement表示SELECT語(yǔ)句，返回一個(gè)用于創(chuàng)建游標(biāo)的結(jié)果集。2．打開(kāi)游標(biāo)打開(kāi)游標(biāo)的關(guān)鍵字為OPEN，其語(yǔ)法形式如下：OPENcursor_name;【提示】在打開(kāi)一個(gè)游標(biāo)時(shí)，游標(biāo)并不指向第一條記錄，而是指向第一條記錄的前邊。3．使用游標(biāo)使用游標(biāo)的關(guān)鍵字是FETCH，其語(yǔ)法形式如下：FETCHcursor_nameINTOvar_name[,var_name]…上述語(yǔ)句的作用是將定義游標(biāo)cursor_name時(shí)查詢出的數(shù)據(jù)賦予變量var_name。4．關(guān)閉游標(biāo)關(guān)閉游標(biāo)的關(guān)鍵字為CLOSE，其語(yǔ)法形式如下：CLOSEcursor_name;【實(shí)例13-7】創(chuàng)建存儲(chǔ)過(guò)程，并在存儲(chǔ)過(guò)程中使用游標(biāo)。在創(chuàng)建存儲(chǔ)過(guò)程前首先登錄MySQL，并選擇數(shù)據(jù)庫(kù)db_shop。SQL語(yǔ)句及其執(zhí)行結(jié)果如下：mysql>DELIMITER$$mysql>CREATEPROCEDUREproc9(INflagVARCHAR(10),OUTgnameVARCHAR(30),OUTgpriceDECIMAL(7,2))->BEGIN->DECLAREg_idINT;->DECLAREg_nameVARCHAR(30);->DECLAREg_priceDECIMAL(7,2);->DECLAREg_cursorCURSORFORSELECTid,name,priceFROMdb_shop.goods;->DECLAREEXITHANDLERFORNOTFOUNDCLOSEg_cursor;->OPENg_cursor;->REPEAT->FETCHg_cursorINTOg_id,g_name,g_price;->IFg_id=flagTHEN->SELECTg_name,g_priceINTOgname,gprice;->ENDIF;->UNTILg_id=flag->ENDREPEAT;->CLOSEg_cursor;->END$$QueryOK,0rowsaffected(0.12sec)mysql>DELIMITER;調(diào)用存儲(chǔ)過(guò)程，查看結(jié)果：mysql>CALLproc9(5,@gname,@gprice);QueryOK,1rowaffected(0.06sec)mysql>SELECT@gname,@gprice;+++|@gname|@gprice|+++|果汁|2.50|+++1rowinset(0.01sec)13.3.4流程控制的使用?【教師】組織學(xué)生掃碼觀看“流程控制的使用”視頻（詳見(jiàn)教材），讓學(xué)生對(duì)相關(guān)知識(shí)有一個(gè)大致了解流程控制語(yǔ)句是指可以控制程序運(yùn)行順序的指令，程序運(yùn)行順序主要包括順序執(zhí)行、條件執(zhí)行和循環(huán)執(zhí)行。MySQL支持的流程控制語(yǔ)句包括IF語(yǔ)句、CASE語(yǔ)句、LOOP語(yǔ)句、REPEAT語(yǔ)句、WHILE語(yǔ)句、LEAVE語(yǔ)句、ITERATE語(yǔ)句和RETURN語(yǔ)句，下面逐一介紹。1．IF語(yǔ)句IF實(shí)現(xiàn)條件判斷，語(yǔ)句中可以包含多個(gè)判斷條件，系統(tǒng)會(huì)根據(jù)條件的結(jié)果是否為TRUE執(zhí)行相應(yīng)的操作，語(yǔ)法形式如下：IFsearch_conditionTHENstatement_list[ELSEIFsearch_conditionTHENstatement_list]...[ELSEstatement_list]ENDIF上述語(yǔ)句中，search_condition為判斷條件，statement_list為相應(yīng)操作，如果所有判斷條件均不為TRUE，則執(zhí)行ELSE子句中的操作。2．CASE語(yǔ)句CASE語(yǔ)句可以實(shí)現(xiàn)比IF語(yǔ)句更復(fù)雜的條件構(gòu)造，該語(yǔ)句有兩種使用形式。第1種語(yǔ)法形式如下：CASEcase_exprWHENwhen_valueTHENstatement_list[WHENwhen_valueTHENstatement_list]...[ELSEstatement_list]ENDCASE上述語(yǔ)句中，case_expr表示判斷條件的表達(dá)式，將此表達(dá)式與每個(gè)WHEN子句中的when_value值進(jìn)行比較，直到與其中一個(gè)相等，此時(shí)，執(zhí)行相應(yīng)THEN子句中的statement_list。如果表達(dá)式與所有when_value值都不相等，則執(zhí)行ELSE子句中的statement_list。CASE語(yǔ)句的第2種語(yǔ)法形式如下：CASEWHENsearch_conditionTHENstatement_list[WHENsearch_conditionTHENstatement_list]...[ELSEstatement_list]ENDCASE上述語(yǔ)句中，系統(tǒng)會(huì)對(duì)每個(gè)WHEN子句中的search_condition表達(dá)式進(jìn)行判斷，直到某個(gè)search_condition表達(dá)式為TRUE，此時(shí)將執(zhí)行其對(duì)應(yīng)的THEN子句中的statement_list。如果所有search_condition表達(dá)式的值都不為TRUE，則執(zhí)行ELSE子句中的statement_list。3．LOOP語(yǔ)句和LEAVE語(yǔ)句LOOP語(yǔ)句可以實(shí)現(xiàn)簡(jiǎn)單的循環(huán)，使得系統(tǒng)能夠重復(fù)執(zhí)行循環(huán)結(jié)構(gòu)內(nèi)的語(yǔ)句列表。該語(yǔ)句列表由一條或多條語(yǔ)句組成，每條語(yǔ)句使用分號(hào)（;）隔開(kāi)。語(yǔ)法形式如下：[loop_label:]LOOPstatement_listENDLOOP[end_label]上述語(yǔ)句中，loop_label表示LOOP語(yǔ)句的標(biāo)注名稱（可以省略），statement_list表示需要循環(huán)執(zhí)行的SQL語(yǔ)句。如果不在statement_list中增加退出循環(huán)的語(yǔ)句，LOOP語(yǔ)句可以實(shí)現(xiàn)簡(jiǎn)單的死循環(huán)。使用LEAVE語(yǔ)句可以退出循環(huán)。語(yǔ)法形式如下：LEAVElabel;其中，label參數(shù)表示循環(huán)的標(biāo)注名。4．REPEAT語(yǔ)句REPEAT語(yǔ)句可以實(shí)現(xiàn)一個(gè)帶條件判斷的循環(huán)結(jié)構(gòu)。語(yǔ)法形式如下：[repeat_label:]REPEATstatement_listUNTILsearch_conditionENDREPEAT[repeat_label]repeat_label表示REPEAT語(yǔ)句的標(biāo)注名稱（可以省略），每次SQL語(yǔ)句statement_list執(zhí)行完畢后，會(huì)對(duì)條件search_condition進(jìn)行判斷，如果結(jié)果為TRUE，循環(huán)終止，否則繼續(xù)執(zhí)行循環(huán)中的語(yǔ)句。5．WHILE語(yǔ)句WHILE語(yǔ)句同樣可以實(shí)現(xiàn)一個(gè)帶條件判斷的循環(huán)結(jié)構(gòu)，但與REPEAT語(yǔ)句不同的是，WHILE語(yǔ)句會(huì)先對(duì)條件進(jìn)行判斷，如果為TRUE，才會(huì)執(zhí)行需要循環(huán)的操作，否則終止循環(huán)，語(yǔ)法形式如下：[while_label:]WHILEsearch_conditionDOstatement_listENDWHILE[while_label]上述語(yǔ)句中，while_label為WHILE語(yǔ)句的標(biāo)注名稱，search_condition為判斷條件，statement_list為需要循環(huán)的操作。6．ITERATE