網(wǎng)頁(yè)制作基礎(chǔ)教程 （Dreamweaver CC）（第3版）課件 項(xiàng)目11 數(shù)據(jù)庫(kù)編程

數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用授課人：陳翠松項(xiàng)目11數(shù)據(jù)庫(kù)編程負(fù)責(zé)前端開(kāi)發(fā)工作的老李告訴小王，如果在客戶端實(shí)現(xiàn)數(shù)據(jù)處理，那么每個(gè)客戶端程序都進(jìn)行處理，需要重復(fù)開(kāi)發(fā)，工作量比較大，建議小王將某些功能移到數(shù)據(jù)庫(kù)服務(wù)器上實(shí)現(xiàn)，客戶端直接調(diào)用服務(wù)器相關(guān)資源實(shí)現(xiàn)特定的數(shù)據(jù)處理，這樣只需要開(kāi)發(fā)一次，能有效提高工作效率。通過(guò)調(diào)查和學(xué)習(xí)，小王決定在粵文創(chuàng)項(xiàng)目中，通過(guò)函數(shù)來(lái)優(yōu)化某些功能實(shí)現(xiàn)，通過(guò)存儲(chǔ)過(guò)程和觸發(fā)器來(lái)完善數(shù)據(jù)處理和數(shù)據(jù)一致性，通過(guò)事務(wù)提升數(shù)據(jù)的安全性和一致性。工作情境目錄01應(yīng)用函數(shù)02使用存儲(chǔ)過(guò)程04應(yīng)用事務(wù)06任務(wù)訓(xùn)練03應(yīng)用觸發(fā)器05鞏固與小結(jié)01應(yīng)用函數(shù)一、應(yīng)用函數(shù)任務(wù)分析大家非常佩服程序員，在很短的時(shí)間內(nèi)就能編寫(xiě)出大量的代碼，但是很多代碼其實(shí)是重復(fù)的。例如，在一個(gè)項(xiàng)目中，有10000個(gè)比較幾個(gè)數(shù)的大小的功能需求，那么比較幾個(gè)數(shù)的大小的程序代碼需要出現(xiàn)10000次，除了第1次，后面還要重復(fù)9999次。可以先把比較幾個(gè)數(shù)的大小的程序代碼定義為函數(shù)，再直接調(diào)用這個(gè)函數(shù)10000次就可以實(shí)現(xiàn)用戶的功能需求，這樣不僅能大大減少代碼量，還方便維護(hù)程序。小王對(duì)粵文創(chuàng)進(jìn)行后得到的任務(wù)清單如下。任務(wù)編號(hào)任務(wù)內(nèi)容任務(wù)11-1設(shè)計(jì)計(jì)算體重指數(shù)BMI值的函數(shù)任務(wù)11-2設(shè)計(jì)根據(jù)不同時(shí)間提示不同問(wèn)候語(yǔ)的函數(shù)任務(wù)11-3設(shè)計(jì)抽獎(jiǎng)函數(shù)拓展任務(wù)11-1粵文創(chuàng)推出健康咨詢機(jī)器人拓展任務(wù)11-2粵文創(chuàng)推出生日送生肖禮拓展任務(wù)11-3粵文創(chuàng)為用戶昵稱設(shè)計(jì)加密算法一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備1、SQL函數(shù)的簡(jiǎn)介SQL函數(shù)是指能完成特定功能的一組SQL語(yǔ)句。如果沒(méi)有函數(shù)，那么所有代碼集中在一起，從上到下按語(yǔ)句執(zhí)行。函數(shù)相當(dāng)于把特定功能的語(yǔ)句組單獨(dú)封裝在一起，變成一個(gè)相對(duì)獨(dú)立的程序，即把原來(lái)的程序分為兩部分，分出來(lái)的子程序叫作函數(shù)，原來(lái)剩下的語(yǔ)句組稱為主程序，主程序需要運(yùn)行這個(gè)函數(shù)稱為函數(shù)調(diào)用，函數(shù)完成特定功能后把結(jié)果告之主程序稱為返回值。假設(shè)小王到飯店吃飯時(shí)點(diǎn)了白切雞這道菜，那么小王是主程序，酒店是函數(shù)，小王點(diǎn)菜就是函數(shù)調(diào)用，飯店服務(wù)員端上的白切雞就是返回值。MySQL本身提供了許多函數(shù)，一般稱為系統(tǒng)函數(shù)或內(nèi)部函數(shù)，如SUM()函數(shù)、MIN()函數(shù)等。常見(jiàn)的系統(tǒng)函數(shù)及其使用方法請(qǐng)參考附錄B。當(dāng)調(diào)用系統(tǒng)函數(shù)無(wú)法解決用戶需求時(shí)，需要根據(jù)用戶需求定義新的函數(shù)，即用戶可以自已定義函數(shù)。自定義函數(shù)需要先創(chuàng)建再調(diào)用。一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備2、創(chuàng)建與調(diào)用沒(méi)有參數(shù)的函數(shù)1）創(chuàng)建函數(shù)創(chuàng)建函數(shù)使用CREATEFUNCTION語(yǔ)句，語(yǔ)法格式如下：CREATEFUNCTION函數(shù)名()RETURNS返回值類型DETERMINISTIC或NOSQL或READSSQLDATA函數(shù)體;需要說(shuō)明以下幾點(diǎn)。在創(chuàng)建函數(shù)前，需要設(shè)置好當(dāng)前數(shù)據(jù)庫(kù)，函數(shù)名一般由字母、數(shù)字和下畫(huà)線組成，建議前面加前綴fun_。函數(shù)體一般以BEGIN開(kāi)始，以END結(jié)束，兩者之間是函數(shù)功能代碼。關(guān)鍵字RETURNS后面已加“S”，在函數(shù)體中用關(guān)鍵字RETURN返回指定值。DETERMINISTIC表示確定的，NOSQL表示沒(méi)有SQl語(yǔ)句不修改數(shù)據(jù)，READSSQLDATA只讀取數(shù)據(jù)不修改數(shù)據(jù)，一般選擇NOSQL，如果不想選擇，那么可以執(zhí)行語(yǔ)句setgloballog_bin_trust_function_creators=TRUE;。一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備2、創(chuàng)建與調(diào)用沒(méi)有參數(shù)的函數(shù)在默認(rèn)情況下，MySQL的語(yǔ)句以分號(hào)結(jié)束，即系統(tǒng)遇到分號(hào)時(shí)執(zhí)行該語(yǔ)句。如果需要使用定義新的語(yǔ)句結(jié)束符，那么可以使用DELIMITER命令實(shí)現(xiàn)。DELIMITER命令的語(yǔ)法格式如下：DELIMITER語(yǔ)句結(jié)束符需要注意的是，DELIMITER和語(yǔ)句結(jié)束符之間至少要有一個(gè)空格。示例11-1創(chuàng)建函數(shù)fun_product()，計(jì)算123*987的積。一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備2、創(chuàng)建與調(diào)用沒(méi)有參數(shù)的函數(shù)程序代碼如下：DELIMITER//CREATEFUNCTIONfun_product()RETURNSINTNOSQLBEGINDECLARExINT;RETURN123*987;END//一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備2、創(chuàng)建與調(diào)用沒(méi)有參數(shù)的函數(shù)2）調(diào)用函數(shù)完成函數(shù)創(chuàng)建，相當(dāng)于廚師學(xué)會(huì)了做菜，沒(méi)有人請(qǐng)他做菜時(shí)，他空有一身本事，有人點(diǎn)餐時(shí)，廚師才真正工作，函數(shù)也一樣，創(chuàng)建之后需要調(diào)用，調(diào)用函數(shù)才能真正完成特定的功能。函數(shù)可以直接在表達(dá)式中使用，具體如下：SELECT函數(shù)名();示例11-2調(diào)用函數(shù)fun_product。程序代碼如下：SELECTfun_product()//DELIMITER;思考：使用fun_product函數(shù)只能計(jì)算123*987的積，能否把該函數(shù)擴(kuò)展為計(jì)算兩個(gè)整數(shù)的乘積？一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備3、創(chuàng)建與調(diào)用帶參數(shù)的函數(shù)1）常量常量一般包括字符串常量、數(shù)值常量、日期和時(shí)間常量、布爾值、空值等，如表11-1所示。常量的類型說(shuō)明應(yīng)用示例字符串常量必須用單引號(hào)或雙引號(hào)引起來(lái)'a'和"a"數(shù)值常量包括整型常量和包含小數(shù)點(diǎn)的浮點(diǎn)型常量123、987和1.2日期和時(shí)間常量必須符合日期和時(shí)間的標(biāo)準(zhǔn)規(guī)范，必須用單引號(hào)或雙引號(hào)引起來(lái)"2023-2-1413:14:00""2023-5-1"布爾常量包括TRUE和FALSE兩個(gè)值TRUE和FALSE空值空值NULL表示“沒(méi)有值”，可以使用各種數(shù)據(jù)類型NULL一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備3、創(chuàng)建與調(diào)用帶參數(shù)的函數(shù)2）變量變量用于臨時(shí)存儲(chǔ)數(shù)據(jù)，其值在程序運(yùn)行過(guò)程中可能會(huì)發(fā)生變化。變量分為系統(tǒng)變量和用戶變量。MySQL中有一些特定的變量，當(dāng)MySQL數(shù)據(jù)庫(kù)服務(wù)器啟動(dòng)時(shí)，其會(huì)讀這些設(shè)置以決定如何進(jìn)行下一步，這些設(shè)置就是系統(tǒng)變量。有些系統(tǒng)變量以“@@”為前綴，如@@Version等，有些系統(tǒng)變量不以“@@”為前綴，如Current_Date等。示例11-3通過(guò)系統(tǒng)變量@@Version、Current_Date、Current_Time及Current_User查看系統(tǒng)版本、當(dāng)前日期、當(dāng)前時(shí)間和當(dāng)前用戶。程序代碼如下：SELECT@@Version,Current_Date,Current_Time,Current_User;用戶變量即用戶自已定義的變量。在使用變量前應(yīng)用DECLARE聲明，語(yǔ)法格式如下：DECLARE變量名數(shù)據(jù)類型[DEFAULT默認(rèn)值];變量可以使用SET賦值，語(yǔ)法格式如下：SET變量名=值;示例11-4創(chuàng)建函數(shù)fun_productNew，計(jì)算123和987的乘積，并調(diào)用該函數(shù)。一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備3、創(chuàng)建與調(diào)用帶參數(shù)的函數(shù)程序代碼如下：DELIMITER//CREATEFUNCTIONfun_productNew()RETURNSBIGINTNOSQLBEGINDECLAREnum1,num2INT;DECLAREsBIGINT;SETnum1=123,num2=987;SETs=num1*num2;RETURNs;END//SELECTfun_productNew()//DELIMITER;在一般情況下，盡管一個(gè)簡(jiǎn)單變量的值可以改變，但任何時(shí)刻只能保存一個(gè)值。程序所需的變量個(gè)數(shù)與數(shù)據(jù)量有關(guān)，計(jì)算兩個(gè)整數(shù)的乘積，需要兩個(gè)數(shù)，因此需要兩個(gè)變量，兩個(gè)數(shù)的乘積也是一個(gè)數(shù)值，一般保存在一個(gè)新變量中，即需要3個(gè)變量。一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備3、創(chuàng)建與調(diào)用帶參數(shù)的函數(shù)3）參數(shù)盡管函數(shù)fun_productNew通過(guò)變量保存數(shù)據(jù)，但仍然無(wú)法實(shí)現(xiàn)在函數(shù)調(diào)用時(shí)才輸入值的功能，因?yàn)樵诙x函數(shù)fun_productNew時(shí)，為兩個(gè)乘數(shù)賦值，相乘的兩個(gè)數(shù)與函數(shù)調(diào)用無(wú)關(guān)。在定義函數(shù)時(shí)，可以設(shè)置形式參數(shù)，在調(diào)用函數(shù)時(shí)通過(guò)實(shí)際參數(shù)將值傳給形式參數(shù)，完成數(shù)據(jù)處理。創(chuàng)建帶參數(shù)函數(shù)的語(yǔ)法格式如下：CREATEFUNCTION函數(shù)名(形式參數(shù))RETURNS返回值類型DETERMINISTIC或NOSQL或READSSQLDATA

函數(shù)體;調(diào)用帶參數(shù)函數(shù)的語(yǔ)法格式如下：SELECT函數(shù)名(實(shí)際參數(shù));示例11-5創(chuàng)建函數(shù)fun_productExtend，計(jì)算兩個(gè)整數(shù)的乘積，并調(diào)用該函數(shù)計(jì)算123和987的乘積。一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備3、創(chuàng)建與調(diào)用帶參數(shù)的函數(shù)程序代碼如下：DELIMITER//CREATEFUNCTIONfun_productExtend(num1INT,num2INT)RETURNSBIGINTNOSQLBEGINDECLAREsBIGINT;SETs=num1*num2;RETURNs;END//SELECTfun_productExtend(123,987)//DELIMITER;思考：使用fun_productExtend函數(shù)能計(jì)算兩個(gè)整數(shù)的乘積，能否實(shí)現(xiàn)只計(jì)算兩個(gè)正整數(shù)的乘積？一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備3、創(chuàng)建與調(diào)用帶參數(shù)的函數(shù)4）運(yùn)算符與表達(dá)式MySQL中的運(yùn)算符主要包括算術(shù)運(yùn)算符、比較運(yùn)算符、邏輯運(yùn)算符和位運(yùn)算符，其中前3類最常用。MySQL中的表達(dá)式由數(shù)字、運(yùn)算符、數(shù)字分組符號(hào)、自由變量和約束變量等，以能求得數(shù)值的有意義排列方法所得的組合，如(1+2)*3。表達(dá)式中可以使用多種運(yùn)算符，但不同運(yùn)算符有對(duì)應(yīng)的優(yōu)先級(jí)。關(guān)于比較運(yùn)算符和邏輯運(yùn)算符的相關(guān)內(nèi)容請(qǐng)參考項(xiàng)目5，其它常見(jiàn)運(yùn)算符及其優(yōu)先級(jí)請(qǐng)參考附錄C。一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備4、查看與刪除函數(shù)1）查看函數(shù)創(chuàng)建好的函數(shù)第一次運(yùn)行時(shí)正常執(zhí)行，如果再執(zhí)行一次，那么系統(tǒng)提示函數(shù)已存在，會(huì)出錯(cuò)，如再創(chuàng)建fun_productExtend函數(shù)?？梢允褂肧HOWCREATEFUNCTION語(yǔ)句查看已存在的函數(shù)，了解函數(shù)的定義，語(yǔ)法格式如下：SHOWCREATEFUNCTION函數(shù)名;示例11-6查看fun_productExtend函數(shù)的定義。程序代碼如下：SHOWCREATEFUNCTIONfun_productExtend;2）刪除函數(shù)不需要的函數(shù)可以刪除。刪除函數(shù)使用DROPFUNCTION，語(yǔ)法格式如下：DROPFUNCTION[IFEXISTS]函數(shù)名;說(shuō)明：在刪除函數(shù)時(shí)，如果函數(shù)不存在就會(huì)報(bào)錯(cuò)，所以刪除函數(shù)時(shí)可以加上關(guān)鍵字IFEXISTS。示例11-7刪除fun_productExtend函數(shù)。程序代碼如下：DROPFUNCTIONIFEXISTSfun_productExtend;一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備5、復(fù)雜函數(shù)體的設(shè)計(jì)復(fù)雜程序一般包括3種基本結(jié)構(gòu)，分別為順序結(jié)構(gòu)、選擇結(jié)構(gòu)和循環(huán)結(jié)構(gòu)。順序結(jié)構(gòu)就是從上至下逐行執(zhí)行，不需要專門的控制語(yǔ)句；選擇結(jié)構(gòu)根據(jù)判斷結(jié)果執(zhí)行不同的語(yǔ)句，一般用IF和CASE等語(yǔ)句實(shí)現(xiàn)；循環(huán)語(yǔ)句在滿足循環(huán)條件時(shí)反復(fù)執(zhí)行，不滿足循環(huán)條件時(shí)結(jié)束循環(huán)，一般用WHILE、REPEAT和LOOP等語(yǔ)句實(shí)現(xiàn)。1）簡(jiǎn)單IF語(yǔ)句使用IF語(yǔ)句需要解決幾個(gè)關(guān)鍵問(wèn)題：先設(shè)置一個(gè)判斷條件，再確定條件成立時(shí)需要做什么，條件不成立時(shí)需要做什么。IF語(yǔ)句的語(yǔ)法格式如下：IF條件表達(dá)式THEN

條件成立時(shí)執(zhí)行的語(yǔ)句ELSE

條件不成立時(shí)執(zhí)行的語(yǔ)句ENDIF;示例11-8創(chuàng)建函數(shù)fun_productPositive，計(jì)算兩個(gè)正整數(shù)的乘積，如果不是兩個(gè)正整數(shù)就返回-1，并調(diào)用該函數(shù)計(jì)算-123和987的乘積。一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備5、復(fù)雜函數(shù)體的設(shè)計(jì)程序代碼如下：DELIMITER//DROPFUNCTIONIFEXISTSfun_productPositive;CREATEFUNCTIONfun_productPositive(num1INT,num2INT)RETURNSBIGINTNOSQLBEGINDECLAREsBIGINT;IFnum1>0ANDnum2>0THENSETs=num1*num2;ELSESETs=-1;ENDIF;RETURNs;END//SELECTfun_productPositive(-123,987)//DELIMITER;num1>0ANDnum2>0是判斷條件，若滿足判斷條件則計(jì)算兩個(gè)整數(shù)的乘積，若不滿足判斷條件則結(jié)果為-1。一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備5、復(fù)雜函數(shù)體的設(shè)計(jì)2）嵌套的IF語(yǔ)句一個(gè)簡(jiǎn)單的IF語(yǔ)句最多只能表示兩種情況，即條件成立和條件不成立。如果有多種可能的情況，那么可以通過(guò)嵌套的IF語(yǔ)句來(lái)實(shí)現(xiàn)。IF語(yǔ)句可以多層嵌套。嵌套的IF語(yǔ)句的語(yǔ)法格式如下：IF條件表達(dá)式1THEN

條件表達(dá)式1成立時(shí)執(zhí)行的語(yǔ)句ELSEIF條件表達(dá)式2THEN

條件表達(dá)式1不成立且條件表達(dá)式2成立時(shí)執(zhí)行的語(yǔ)句ELSE

條件表達(dá)式2不成立時(shí)執(zhí)行的語(yǔ)句…ENDIF;示例11-9定義函數(shù)fun_productPositiveNew，計(jì)算兩個(gè)正整數(shù)的積，若兩個(gè)都是正整數(shù)則計(jì)算，若只有一個(gè)正整數(shù)則返回-1，若兩個(gè)都不是正整數(shù)則返回-2，并調(diào)用該函數(shù)計(jì)算-123和-987的乘積。一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備5、復(fù)雜函數(shù)體的設(shè)計(jì)DELIMITER//DROPFUNCTIONIFEXISTSfun_productPositiveNew;CREATEFUNCTIONfun_productPositiveNew(num1INT,num2INT)RETURNSBIGINTNOSQLBEGINDECLAREsBIGINT;IFnum1>0ANDnum2>0THENSETs=num1*num2;ELSEIFnum1<0ANDnum2<0THENSETs=-2;ELSESETs=-1;ENDIF;RETURNs;END//SELECTfun_productPositiveNew(-123,-987)//DELIMITER;一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備5、復(fù)雜函數(shù)體的設(shè)計(jì)如果滿足num1>0ANDnum2>0，那么說(shuō)明兩個(gè)數(shù)都是正整數(shù)，否則有3種情況：兩個(gè)整數(shù)都不是正整數(shù)；第1個(gè)整數(shù)不是正整數(shù)但第2個(gè)整數(shù)是正整數(shù)；第1個(gè)整數(shù)是正整數(shù)但第2個(gè)整數(shù)不是正整數(shù)。在這3種情況下判斷條件num1<0ANDnum2<0，若滿足條件則說(shuō)明兩個(gè)整數(shù)都不是正整數(shù)，這個(gè)條件再否則，就剩下2種情況：第1個(gè)整數(shù)不是正整數(shù)但第2個(gè)整數(shù)是正整數(shù)；第1個(gè)整數(shù)是正整數(shù)但第2個(gè)整數(shù)不是正整數(shù)。這兩種情況都只有一個(gè)正整數(shù)，不需要再進(jìn)一步處理。一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備5、復(fù)雜函數(shù)體的設(shè)計(jì)3）CASE語(yǔ)句當(dāng)選擇的分支比較多時(shí)，還可以使用CASE語(yǔ)句，語(yǔ)法格式如下：CASE WHEN條件表達(dá)式結(jié)果1THEN語(yǔ)句1 WHEN條件表達(dá)式結(jié)果2THEN語(yǔ)句2 … WHEN條件表達(dá)式結(jié)果nTHEN語(yǔ)句nELSE其他情況執(zhí)行的語(yǔ)句ENDCASE;示例11-10利用CASE語(yǔ)句改寫(xiě)fun_productPositiveNew函數(shù)。一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備5、復(fù)雜函數(shù)體的設(shè)計(jì)DELIMITER//DROPFUNCTIONIFEXISTSfun_productPositiveNew;CREATEFUNCTIONfun_productPositiveNew(num1INT,num2INT)RETURNSBIGINTNOSQLBEGIN DECLAREsBIGINT; CASE WHENnum1>0ANDnum2>0THENSETs=num1*num2; WHENnum1<0ANDnum2<0THENSETs=-2; ELSESETs=-1; ENDCASE; RETURNs;END//SELECTfun_productPositiveNew(-123,-987)//DELIMITER;一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備5、復(fù)雜函數(shù)體的設(shè)計(jì)4）WHILE語(yǔ)句使用WHILE語(yǔ)句需要解決幾個(gè)主要問(wèn)題：首先設(shè)置循環(huán)條件，然后確定循環(huán)體（要反復(fù)做什么），最后確定循環(huán)控制變量的步長(zhǎng)值。在循環(huán)之前一般需要設(shè)置控制變量和其他變量的初值。WHILE語(yǔ)句的語(yǔ)法格式如下：循環(huán)控制變量賦初值;WHILE條件表達(dá)式DO

循環(huán)體語(yǔ)句ENDWHILE;示例11-11定義fun_customSum函數(shù)，實(shí)現(xiàn)1～100所有正整數(shù)之和。一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備5、復(fù)雜函數(shù)體的設(shè)計(jì)程序代碼如下：DELIMITER//DROPFUNCTIONIFEXISTSfun_customSum;CREATEFUNCTIONfun_customSum()RETURNSBIGINTNOSQLBEGIN DECLAREsBIGINTDEFAULT0; DECLAREiINTDEFAULT1; WHILEi<=100DO SETs=s+i; SETi=i+1; ENDWHILE; RETURNs;END//SELECTfun_customSum()//DELIMITER;一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備5、復(fù)雜函數(shù)體的設(shè)計(jì)這是一個(gè)典型的循環(huán)結(jié)構(gòu)，需要設(shè)置一個(gè)循環(huán)變量i，其初值為i=1，條件為i<=100，步長(zhǎng)值為1，即i=i+1。求1～100共100個(gè)數(shù)的和，反復(fù)做的工作是兩個(gè)數(shù)求和，即每次利用前面的結(jié)果加上當(dāng)前整數(shù)。可設(shè)置一個(gè)求和變量s，求和可用s=s+i表示，第1個(gè)數(shù)求和時(shí)，要設(shè)置s的初始值s=0。初始值只需要設(shè)置1次，因此i=1和s=0應(yīng)放在循環(huán)之前，而s=s+i和i=i+1要反復(fù)執(zhí)行，應(yīng)放在循環(huán)體之內(nèi)。一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備5、復(fù)雜函數(shù)體的設(shè)計(jì)5）REPEAT語(yǔ)句REPEAT語(yǔ)句與WHILE語(yǔ)句的功能相似，只是WHILE語(yǔ)句先檢查循環(huán)條件，滿足條件才執(zhí)行循環(huán)體，而REPEAT語(yǔ)句先執(zhí)行循環(huán)體后進(jìn)行條件判斷，不滿足循環(huán)條件時(shí)繼續(xù)循環(huán)，滿足循環(huán)條件時(shí)結(jié)束循環(huán)。使用REPEAT語(yǔ)句實(shí)現(xiàn)循環(huán)時(shí)，循環(huán)體至少要執(zhí)行1次。REPEAT語(yǔ)句的語(yǔ)法格式如下：循環(huán)控制變量賦初值;REPEAT

循環(huán)體語(yǔ)句UNTIL條件表達(dá)式ENDREPEAT;示例11-12使用REPEAT語(yǔ)句改寫(xiě)fun_customSum函數(shù)。一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備5、復(fù)雜函數(shù)體的設(shè)計(jì)DELIMITER//DROPFUNCTIONIFEXISTSfun_customSum;CREATEFUNCTIONfun_customSum()RETURNSBIGINTNOSQLBEGIN DECLAREsBIGINTDEFAULT0; DECLAREiINTDEFAULT1; REPEAT SETs=s+i; SETi=i+1; UNTILi>100 ENDREPEAT; RETURNs;END//SELECTfun_customSum()//DELIMITER;一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備5、復(fù)雜函數(shù)體的設(shè)計(jì)6）LOOP語(yǔ)句LOOP語(yǔ)句的語(yǔ)法格式如下：循環(huán)控制變量賦初值;開(kāi)始標(biāo)號(hào):LOOP

循環(huán)體語(yǔ)句ENDLOOP;說(shuō)明：LOOP語(yǔ)句不能自動(dòng)結(jié)束循環(huán)，在循環(huán)體中需要設(shè)置退出循環(huán)的條件，當(dāng)滿足循環(huán)結(jié)束條件時(shí)，使用LEAVE語(yǔ)句跳出循環(huán)控制。LEAVE語(yǔ)句的語(yǔ)法格式如下：LEAVE標(biāo)號(hào);示例11-13使用LOOP語(yǔ)句改寫(xiě)fun_customSum函數(shù)。一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備5、復(fù)雜函數(shù)體的設(shè)計(jì)DELIMITER//DROPFUNCTIONIFEXISTSfun_customSum;CREATEFUNCTIONfun_customSum()RETURNSBIGINTNOSQLBEGIN DECLAREsBIGINTDEFAULT0; DECLAREiINTDEFAULT1; Lsum:LOOP SETs=s+i; SETi=i+1; IFi>100THEN LEAVELsum; ENDIF; ENDLOOP; RETURNs;END//SELECTfun_customSum()//DELIMITER;一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備6、使用Navicat工具管理函數(shù)1）查看函數(shù)（1）啟動(dòng)Navicat，先選擇指定連接，再選擇指定數(shù)據(jù)庫(kù)。（2）單擊“函數(shù)”圖標(biāo)，顯示數(shù)據(jù)庫(kù)中當(dāng)前所有函數(shù)列表，如圖11-9所示。一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備6、使用Navicat工具管理函數(shù)2）新建函數(shù)示例11-14新建函數(shù)pro_addition，計(jì)算兩個(gè)整數(shù)之和。（1）選中圖11-9中的一個(gè)函數(shù)或在空白處右擊，在快捷菜單中選擇“新建函數(shù)”命令。（2）打開(kāi)“函數(shù)向?qū)А贝翱?，輸入函?shù)名，選中“函數(shù)”單選按鈕，如圖11-10所示，單擊“完成”按鈕。（3）單擊“下一步”按鈕，設(shè)置函數(shù)參數(shù)?！啊北硎驹黾訁?shù)，“”表示刪除參數(shù)，“”和“”分別表示向上和向下移動(dòng)選中的參數(shù)。設(shè)置2個(gè)整型參數(shù)num1和num2，但要一個(gè)一個(gè)地增加，如圖11-11所示。一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備6、使用Navicat工具管理函數(shù)2）新建函數(shù)（4）單擊“下一步”按鈕，設(shè)置函數(shù)返回值（可設(shè)置返回值的類型、長(zhǎng)度、小數(shù)位、字符集等，如圖11-12所示。（5）在完成類型選擇、參數(shù)設(shè)置、返回值類型設(shè)置后，單擊“完成”按鈕，返回Navicat主界面，已自動(dòng)生成函數(shù)，再根據(jù)情況補(bǔ)充參數(shù)定義，完善函數(shù)體，如圖11-13所示。單擊代碼框左上方的“保存”按鈕保存函數(shù)。一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備6、使用Navicat工具管理函數(shù)3）運(yùn)行函數(shù)（1）保存函數(shù)后，單擊圖11-13中代碼框上方的“運(yùn)行”按鈕，打開(kāi)“輸入?yún)?shù)”對(duì)話框，輸入?yún)?shù)后，單擊“確定”按鈕，如圖11-14所示。（2）運(yùn)行結(jié)果如圖11-15所示。單擊“信息”選項(xiàng)卡，查看運(yùn)行信息和運(yùn)行時(shí)間，如圖11-16所示。單擊“定義”選項(xiàng)卡，查看程序代碼。一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備6、使用Navicat工具管理函數(shù)4）關(guān)閉函數(shù)窗口將光標(biāo)移到文檔選項(xiàng)卡上，顯示“關(guān)閉”按鈕，如圖11-17所示，單擊該按鈕關(guān)閉函數(shù)文檔窗口。需要注意的是，“關(guān)閉”按鈕平時(shí)是隱藏的。一、應(yīng)用函數(shù)知識(shí)儲(chǔ)備6、使用Navicat工具管理函數(shù)5）其他操作在圖11-13中，單擊“對(duì)象”選項(xiàng)卡，顯示函數(shù)列表，選中指定函數(shù)并右擊，快捷菜單中包括如下命令?！霸O(shè)計(jì)函數(shù)”命令：選擇該命令可以打開(kāi)“函數(shù)定義”窗口，在該窗口中可以修改和保存函數(shù)。另外，雙擊函數(shù)名也可以打開(kāi)“函數(shù)定義”窗口?！皠h除函數(shù)”命令：選擇該命令可以刪除指定函數(shù)?！爸孛泵睿哼x擇該命令可以修改函數(shù)名稱。一、應(yīng)用函數(shù)任務(wù)實(shí)施任務(wù)11-1設(shè)計(jì)計(jì)算體重指數(shù)BMI值的函數(shù)。設(shè)計(jì)函數(shù)fun_BMI，根據(jù)體重（單位：千克）和身高（單位：米）計(jì)算體重指數(shù)BMI值，計(jì)算方法為體重（千克）除以身高（米）的平方。任務(wù)11-2設(shè)計(jì)根據(jù)不同時(shí)間提示不同問(wèn)候語(yǔ)的函數(shù)?；浳膭?chuàng)App需要根據(jù)用戶登錄時(shí)間提示不同的問(wèn)候語(yǔ)，登錄時(shí)，時(shí)間讀取系統(tǒng)當(dāng)前時(shí)間的小時(shí)值，問(wèn)候語(yǔ)生成規(guī)則如下：07:00—12:00顯示“上午好”，13:00—19:00顯示“下午好”，00:00—06:00和20:00—24:00顯示“晚上好”。設(shè)計(jì)函數(shù)fun_greetings實(shí)現(xiàn)以上功能。任務(wù)11-3設(shè)計(jì)抽獎(jiǎng)函數(shù)?；浳膭?chuàng)想設(shè)計(jì)一個(gè)抽獎(jiǎng)函數(shù)fun_prize，抽獎(jiǎng)規(guī)則如下：每次隨機(jī)產(chǎn)生一個(gè)數(shù)字，連續(xù)產(chǎn)生次數(shù)由用戶決定。一、應(yīng)用函數(shù)任務(wù)實(shí)施拓展任務(wù)11-1粵文創(chuàng)推出健康咨詢機(jī)器人。機(jī)器人可以根據(jù)體重（單位：千克）和身高（單位：米）判斷用戶的健康情況，并給出建議：如果體重指數(shù)BMI值小于18.5，那么提示“體重過(guò)低，可能存在其他健康問(wèn)題”；如果體重指數(shù)BMI值介于18.5和23.9之間，那么提示“正常體重，請(qǐng)繼續(xù)保持良好的生活方式”；如果體重指數(shù)BMI值介于24.0和27.9之間，那么提示“超重，請(qǐng)通過(guò)合理飲食、有效運(yùn)動(dòng)達(dá)到理想體重”；如果體重指數(shù)BMI值大于或等于28，那么提示“肥胖，請(qǐng)盡快來(lái)用合理飲食、運(yùn)動(dòng)能量平衡的治療方法來(lái)減肥吧”。設(shè)計(jì)函數(shù)fun_consulting實(shí)現(xiàn)以上功能。拓展任務(wù)11-2粵文創(chuàng)推出生日送生肖禮。設(shè)計(jì)函數(shù)fun_zodiac實(shí)現(xiàn)根據(jù)出生年份查詢生肖的功能。拓展任務(wù)11-3粵文創(chuàng)為用戶昵稱設(shè)計(jì)加密算法。為了提高系統(tǒng)安全性，粵文創(chuàng)為用戶昵稱（包含英文字母和數(shù)字）設(shè)計(jì)了加密算法，英文字母改變大小寫(xiě)，非0數(shù)字轉(zhuǎn)換為對(duì)應(yīng)的補(bǔ)數(shù)，其他字符不變。設(shè)計(jì)函數(shù)fun_encryption實(shí)現(xiàn)以上功能。02使用存儲(chǔ)過(guò)程二、使用存儲(chǔ)過(guò)程任務(wù)分析函數(shù)會(huì)向調(diào)用者返回一個(gè)結(jié)果值，參數(shù)類型也只有一種，函數(shù)的限制比較多，很多時(shí)候不能滿足用戶需求。而存儲(chǔ)過(guò)程的參數(shù)類型有3種，包括輸入?yún)?shù)、輸出參數(shù)和輸入/輸出參數(shù)，使用更加靈活。小王對(duì)粵文創(chuàng)進(jìn)行分析后得到的任務(wù)清單如下。任務(wù)編號(hào)任務(wù)內(nèi)容任務(wù)11-4創(chuàng)建存儲(chǔ)過(guò)程pro_cleanname，對(duì)粵文創(chuàng)的user表中的userName進(jìn)行清洗，把姓名只有一個(gè)字的用戶刪除，并返回刪除記錄數(shù)任務(wù)11-5創(chuàng)建存儲(chǔ)過(guò)程pro_cleanage，對(duì)粵文創(chuàng)的user表中的birthday進(jìn)行清洗，合理的年齡范圍為[0,150]，刪除不合理的記錄，并返回刪除記錄數(shù)任務(wù)11-6創(chuàng)建存儲(chǔ)過(guò)程pro_queryuser，輸入用戶姓名，在粵文創(chuàng)的user表中查詢?cè)撚脩舻膄kTitle、gender、nation、birthday、nativePlace和phone拓展任務(wù)11-4創(chuàng)建存儲(chǔ)過(guò)程pro_supplementnation，對(duì)粵文創(chuàng)的user表中的nation進(jìn)行清洗，為沒(méi)有“族”字的記錄補(bǔ)充“族”字，并返回修改記錄數(shù)拓展任務(wù)11-5創(chuàng)建存儲(chǔ)過(guò)程pro_cleannation，對(duì)粵文創(chuàng)的user表中的nation進(jìn)行清洗，將有錯(cuò)誤的記錄顯示出來(lái)，并返回輸入錯(cuò)誤的用戶數(shù)拓展任務(wù)11-6創(chuàng)建存儲(chǔ)過(guò)程pro_cleanfkTitle，對(duì)粵文創(chuàng)的user表中的fkTitle進(jìn)行清洗，刪除不滿足職稱和年齡關(guān)系的記錄二、使用存儲(chǔ)過(guò)程知識(shí)儲(chǔ)備1、存儲(chǔ)過(guò)程簡(jiǎn)介存儲(chǔ)過(guò)程是一組為了完成特定功能的SQL語(yǔ)句集，是數(shù)據(jù)庫(kù)中的一個(gè)重要對(duì)象。在數(shù)據(jù)量特別大的情況下利用存儲(chǔ)過(guò)程可以顯著提升效率。1）存儲(chǔ)過(guò)程的優(yōu)點(diǎn)（1）重復(fù)使用：存儲(chǔ)過(guò)程可以重復(fù)使用，這不僅減少了開(kāi)發(fā)人員的工作量，還提高了效率。（2）減少網(wǎng)絡(luò)流量：存儲(chǔ)過(guò)程位于服務(wù)器上，客戶端通過(guò)存儲(chǔ)過(guò)程和參數(shù)調(diào)用返回結(jié)果，不需要將大量原始數(shù)據(jù)傳給客戶端，因此降低了網(wǎng)絡(luò)傳輸?shù)臄?shù)據(jù)量。（3）安全性：存儲(chǔ)過(guò)程的參數(shù)化可以防止SQL注入，可以將Grant、Deny和Revoke權(quán)限應(yīng)用于存儲(chǔ)過(guò)程之中。二、使用存儲(chǔ)過(guò)程知識(shí)儲(chǔ)備1、存儲(chǔ)過(guò)程簡(jiǎn)介2）存儲(chǔ)過(guò)程的缺點(diǎn)（1）存儲(chǔ)過(guò)程存儲(chǔ)在數(shù)據(jù)庫(kù)服務(wù)端上，需要在數(shù)據(jù)庫(kù)服務(wù)器環(huán)境中調(diào)試，項(xiàng)目上線后，存儲(chǔ)過(guò)程的調(diào)試和維護(hù)相對(duì)來(lái)說(shuō)比較麻煩。（2）存儲(chǔ)過(guò)程在數(shù)據(jù)庫(kù)服務(wù)器環(huán)境中運(yùn)行，當(dāng)進(jìn)行版本差別很大的服務(wù)器更新，甚至更換數(shù)據(jù)庫(kù)服務(wù)器類型時(shí)，數(shù)據(jù)庫(kù)的遷移比較麻煩，甚至可能需要重寫(xiě)存儲(chǔ)過(guò)程。二、使用存儲(chǔ)過(guò)程知識(shí)儲(chǔ)備2、創(chuàng)建與調(diào)用無(wú)參數(shù)的存儲(chǔ)過(guò)程1）創(chuàng)建無(wú)參數(shù)的存儲(chǔ)過(guò)程使用PROCEDURE創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法格式如下：CREATEPROCEDURE存儲(chǔ)過(guò)程名()存儲(chǔ)過(guò)程體需要說(shuō)明以下幾點(diǎn)。創(chuàng)建函數(shù)之前需要設(shè)置好當(dāng)前數(shù)據(jù)庫(kù)，存儲(chǔ)過(guò)程名一般由字母、數(shù)字和下畫(huà)線組成。存儲(chǔ)過(guò)程名建議以pro_作為前綴，從而與其他數(shù)據(jù)庫(kù)對(duì)象進(jìn)行區(qū)分。存儲(chǔ)過(guò)程體是存儲(chǔ)過(guò)程的主體部分，可以充分使用本項(xiàng)目任務(wù)1介紹的變量、常量、控制語(yǔ)句和函數(shù)等，存儲(chǔ)過(guò)程體一般放在BEGIN…END語(yǔ)句之中。二、使用存儲(chǔ)過(guò)程知識(shí)儲(chǔ)備2、創(chuàng)建與調(diào)用無(wú)參數(shù)的存儲(chǔ)過(guò)程2）調(diào)用無(wú)參數(shù)的存儲(chǔ)過(guò)程使用CALL調(diào)用存儲(chǔ)過(guò)程的語(yǔ)法格式如下：CALL存儲(chǔ)過(guò)程名();示例11-15創(chuàng)建存儲(chǔ)過(guò)程pro_QueryAll()，先查詢user表的前3條記錄，再運(yùn)行該存儲(chǔ)過(guò)程。二、使用存儲(chǔ)過(guò)程知識(shí)儲(chǔ)備2、創(chuàng)建與調(diào)用無(wú)參數(shù)的存儲(chǔ)過(guò)程程序代碼如下：DELIMITER//CREATEPROCEDUREpro_QueryAll()BEGIN SELECT*FROMuserLIMIT3;END//CALLpro_QueryAll()//DELIMITER;這個(gè)存儲(chǔ)過(guò)程其實(shí)是將查詢放置在存儲(chǔ)過(guò)程體中，有查詢知識(shí)和技能的支撐，只要簡(jiǎn)單地添加存儲(chǔ)過(guò)程固定語(yǔ)法即可完成任務(wù)。二、使用存儲(chǔ)過(guò)程知識(shí)儲(chǔ)備3、創(chuàng)建與調(diào)用帶參數(shù)的存儲(chǔ)過(guò)程1）創(chuàng)建帶參數(shù)的存儲(chǔ)過(guò)程創(chuàng)建帶參數(shù)的存儲(chǔ)過(guò)程的語(yǔ)法格式如下：CREATEPROCEDURE存儲(chǔ)過(guò)程名(形參列表)存儲(chǔ)過(guò)程體需要說(shuō)明以下幾點(diǎn)。形參有3種類型，分別為輸入?yún)?shù)、輸出參數(shù)和輸入/輸出參數(shù)，其對(duì)應(yīng)的關(guān)鍵字分別為IN、OUT和INOUT。存儲(chǔ)過(guò)程可帶一個(gè)或多個(gè)參數(shù)，每個(gè)參數(shù)必須指明參數(shù)的參數(shù)類型、參數(shù)名稱和數(shù)據(jù)類型。若沒(méi)有標(biāo)明參數(shù)類型，則采用默認(rèn)值，即輸入?yún)?shù)。二、使用存儲(chǔ)過(guò)程知識(shí)儲(chǔ)備3、創(chuàng)建與調(diào)用帶參數(shù)的存儲(chǔ)過(guò)程2）調(diào)用帶參數(shù)的存儲(chǔ)過(guò)程調(diào)用帶參數(shù)的存儲(chǔ)過(guò)程的語(yǔ)法格式如下：CALL存儲(chǔ)過(guò)程名(實(shí)參列表);需要說(shuō)明以下幾點(diǎn)。實(shí)參的數(shù)量與順序必須形參的一致。輸入?yún)?shù)需要先賦值，輸出參數(shù)不需要事先賦值。如果沒(méi)有聲明直接使用用戶自定義變量，那么可以在變量前面加“@”。示例11-16創(chuàng)建存儲(chǔ)過(guò)程pro_QueryN，查詢前n個(gè)用戶的信息，n由用戶調(diào)用時(shí)輸入。二、使用存儲(chǔ)過(guò)程知識(shí)儲(chǔ)備3、創(chuàng)建與調(diào)用帶參數(shù)的存儲(chǔ)過(guò)程程序代碼如下：DELIMITER//CREATEPROCEDUREpro_QueryAllN(nINT)BEGIN SELECT*FROMuserLIMITn;END//CALLpro_QueryAllN(2)//DELIMITER;由用戶控制顯示記錄數(shù)目，并且更具靈活性。二、使用存儲(chǔ)過(guò)程知識(shí)儲(chǔ)備3、創(chuàng)建與調(diào)用帶參數(shù)的存儲(chǔ)過(guò)程示例11-17創(chuàng)建存儲(chǔ)過(guò)程pro_QueryByName，輸入用戶姓名，查詢其編號(hào)、性別、電話，并調(diào)用該存儲(chǔ)過(guò)程。二、使用存儲(chǔ)過(guò)程知識(shí)儲(chǔ)備3、創(chuàng)建與調(diào)用帶參數(shù)的存儲(chǔ)過(guò)程程序代碼如下：DELIMITER//CREATEPROCEDUREpro_QueryByName(INiNameVARCHAR(8),OUToIDSMALLINT,OUToGenderVARCHAR(2),OUToPhoneVARCHAR(13))BEGIN SELECTuserId,gender,phoneINTOoid,oGender,oPhoneFROMuserWHEREusername=iName;END//CALLpro_QueryByName(“張宏峰”,@ID,@gender,@phone);SELECT@ID,@gender,@phone;CALLpro_QueryByName(“陳小鋒”,@ID,@gender,@phone);SELECT@ID,@gender,@phone//DELIMITER;二、使用存儲(chǔ)過(guò)程知識(shí)儲(chǔ)備4、管理存儲(chǔ)過(guò)程1）查詢存儲(chǔ)過(guò)程查詢當(dāng)前數(shù)據(jù)庫(kù)包括系統(tǒng)存儲(chǔ)過(guò)程在內(nèi)的所有存儲(chǔ)過(guò)程信息，即存儲(chǔ)過(guò)程所屬數(shù)據(jù)庫(kù)，以及存儲(chǔ)過(guò)程的名稱、類型和各種狀態(tài)值等，語(yǔ)法格式如下：SHOWPROCEDURESTATUS;查詢存儲(chǔ)過(guò)程，語(yǔ)法格式如下：SHOWCREATEPROCEDURE存儲(chǔ)過(guò)程名;示例11-18查詢存儲(chǔ)過(guò)程pro_QueryAllN。程序代碼如下：SHOWCREATEPROCEDUREpro_QueryAllN;2）刪除存儲(chǔ)過(guò)程ROPPROCEDURE刪除存儲(chǔ)過(guò)程，語(yǔ)法格式如下：可以使用DDROPPROCEDURE[IFEXISTS]存儲(chǔ)過(guò)程名;刪除不存在的存儲(chǔ)過(guò)程會(huì)報(bào)錯(cuò)，在刪除前可以使用[IFEXISTS]判斷存儲(chǔ)過(guò)程是否存在。示例11-19刪除存儲(chǔ)過(guò)程pro_QueryAll()。程序代碼如下：DROPPROCEDUREIFEXISTSpro_QueryAll;二、使用存儲(chǔ)過(guò)程知識(shí)儲(chǔ)備4、使用Navicat管理存儲(chǔ)過(guò)程在Navicat中，函數(shù)與存儲(chǔ)過(guò)程在同一位置顯示。要?jiǎng)?chuàng)建存儲(chǔ)過(guò)程，必須在“函數(shù)向?qū)А贝翱谥羞x中“過(guò)程”單選按鈕，其他與創(chuàng)建函數(shù)相同。存儲(chǔ)過(guò)程的運(yùn)行、修改、查看、重命名和刪除等操作與函數(shù)的相關(guān)操作一樣。二、使用存儲(chǔ)過(guò)程知識(shí)儲(chǔ)備5、使用游標(biāo)1）游標(biāo)的簡(jiǎn)介游標(biāo)是處理數(shù)據(jù)的一種方法。為了查看或處理結(jié)果集中的數(shù)據(jù)，游標(biāo)提供了在結(jié)果集中一次一行或多行前進(jìn)或向后瀏覽數(shù)據(jù)的能力。游標(biāo)在部分資料中也被稱為光標(biāo)。游標(biāo)的使用流程為聲明游標(biāo)、打開(kāi)游標(biāo)、讀取游標(biāo)和關(guān)閉游標(biāo)。2）聲明游標(biāo)聲明游標(biāo)的語(yǔ)法格式如下：DECLARE游標(biāo)名CURSORFOR查詢語(yǔ)句;說(shuō)明：將游標(biāo)與指定的查詢結(jié)果關(guān)聯(lián)起來(lái)，查詢語(yǔ)句不能使用INTO關(guān)鍵字。3）打開(kāi)游標(biāo)打開(kāi)游標(biāo)的語(yǔ)法格式如下：OPEN游標(biāo)名;二、使用存儲(chǔ)過(guò)程知識(shí)儲(chǔ)備5、使用游標(biāo)4）讀取游標(biāo)讀取游標(biāo)的語(yǔ)法格式如下：FETCH游標(biāo)名INTO變量列表說(shuō)明：將游標(biāo)指向的一行記錄或多個(gè)數(shù)據(jù)賦給對(duì)應(yīng)的變量，變量的個(gè)數(shù)必須與游標(biāo)的數(shù)據(jù)個(gè)數(shù)一致。變量列表中的各個(gè)變量必須事先定義好，并且變量的類型與必須與對(duì)應(yīng)字段的類型一致。5）關(guān)閉游標(biāo)關(guān)閉游標(biāo)的語(yǔ)法格式如下：CLOSE游標(biāo)名;示例11-20創(chuàng)建存儲(chǔ)過(guò)程pro_QueryName，查詢姓名最長(zhǎng)的用戶的姓名。二、使用存儲(chǔ)過(guò)程知識(shí)儲(chǔ)備5、使用游標(biāo)DELIMITER//DROPPROCEDUREIFEXISTSpro_QueryName;CREATEPROCEDUREpro_QueryName(OUToNameVARCHAR(10))BEGIN DECLAREtname,lnameVARCHAR(10); DECLAREdoneINTDEFAULT0; DECLAREcur_nameCURSORFORSELECTuserNameFROMuser; DECLAREcontinueHANDLERFORNOTFOUNDSETdone=1; OPENcur_name; FETCHcur_nameINTOtname; SETlname=tname; WHILE(NOTdone)DO IFCHAR_LENGTH(lname)<CHAR_LENGTH(tname)THEN SETlname=tname; ENDIF; FETCHcur_nameINTOtname;

二、使用存儲(chǔ)過(guò)程知識(shí)儲(chǔ)備5、使用游標(biāo) ENDWHILE; CLOSEcur_name; SEToName=lname;END//CALLpro_QueryName(@name);SELECT@name//DELIMITER;二、使用存儲(chǔ)過(guò)程任務(wù)實(shí)施任務(wù)11-4創(chuàng)建存儲(chǔ)過(guò)程pro_cleanname。對(duì)粵文創(chuàng)的user表中的userName進(jìn)行清洗，把姓名只有一個(gè)字的用戶刪除，并返回刪除記錄數(shù)。任務(wù)11-5創(chuàng)建存儲(chǔ)過(guò)程pro_cleanage。對(duì)粵文創(chuàng)的user表中的birthday進(jìn)行清洗，合理的年齡范圍為[0,150]，刪除不合理的記錄，并返回刪除記錄數(shù)。任務(wù)11-6創(chuàng)建存儲(chǔ)過(guò)程pro_queryuser。輸入用戶姓名，在粵文創(chuàng)的user表中查詢?cè)撚脩舻膄kTitle、gender、nation、birthday、nativePlace和phone。二、使用存儲(chǔ)過(guò)程任務(wù)實(shí)施拓展任務(wù)11-6創(chuàng)建存儲(chǔ)過(guò)程pro_cleanfkTitle。對(duì)粵文創(chuàng)的user表中的fkTitle進(jìn)行清洗，刪除不滿足職稱和年齡關(guān)系的記錄。拓展任務(wù)11-4創(chuàng)建存儲(chǔ)過(guò)程pro_supplementnation。對(duì)粵文創(chuàng)的user表中的nation進(jìn)行清洗，為沒(méi)有“族”字的記錄補(bǔ)充“族”字，并返回修改記錄數(shù)。拓展任務(wù)11-5創(chuàng)建存儲(chǔ)過(guò)程pro_cleannation。對(duì)粵文創(chuàng)的user表中的nation進(jìn)行清洗，將有錯(cuò)誤的記錄顯示出來(lái)，并返回輸入錯(cuò)誤的用戶數(shù)。03應(yīng)用觸發(fā)器三、應(yīng)用觸發(fā)器任務(wù)分析存儲(chǔ)過(guò)程的功能非常強(qiáng)大。利用存儲(chǔ)過(guò)程能實(shí)現(xiàn)很多功能，但存儲(chǔ)過(guò)程需要手動(dòng)調(diào)用，不能自動(dòng)執(zhí)行。MySQL提供了觸發(fā)器對(duì)象。觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程，主要用于強(qiáng)制引用完整性，以便在多個(gè)表中添加、更新或刪除行時(shí)，保留在這些表之間所定義的關(guān)系。小王對(duì)粵文創(chuàng)進(jìn)行分析后得到的任務(wù)清單如下。任務(wù)編號(hào)任務(wù)內(nèi)容任務(wù)11-7通過(guò)INSERT對(duì)粵文創(chuàng)的工作計(jì)劃表plan進(jìn)行自動(dòng)檢查，要求計(jì)劃發(fā)布時(shí)間、計(jì)劃審核時(shí)間不能晚于操作當(dāng)前時(shí)間任務(wù)11-8通過(guò)INSEERT觸發(fā)器tri_checkparticipant，對(duì)粵文創(chuàng)的工作計(jì)劃參與人員表participant進(jìn)行自動(dòng)檢查，在任何計(jì)劃中每個(gè)人只能分配一項(xiàng)，即在一個(gè)計(jì)劃中工號(hào)是唯一的任務(wù)11-9通過(guò)INSERT觸發(fā)器tri_chechplanall，對(duì)粵文創(chuàng)的工作計(jì)劃表plan進(jìn)行自動(dòng)檢查，要求計(jì)劃開(kāi)始時(shí)間早于計(jì)劃結(jié)束時(shí)間，計(jì)劃制訂者和計(jì)劃審核者不是同一個(gè)人，計(jì)劃發(fā)布時(shí)間要晚于計(jì)劃審核時(shí)間拓展任務(wù)11-7將觸發(fā)器tri_checkplanall完善為tri_checkplanallextend，并指出具體錯(cuò)誤三、應(yīng)用觸發(fā)器知識(shí)儲(chǔ)備1、觸發(fā)器簡(jiǎn)介觸發(fā)器由一組SQL語(yǔ)句組成，由事件觸發(fā)，能自動(dòng)執(zhí)行，無(wú)須用戶調(diào)用。觸發(fā)器與表的緊密非常關(guān)系，可以作為表的一部分創(chuàng)建，常用于保護(hù)表中的數(shù)據(jù)或?qū)崿F(xiàn)數(shù)據(jù)的完整性。1）觸發(fā)器的作用（1）觸發(fā)器可在數(shù)據(jù)處理前，用來(lái)強(qiáng)制檢驗(yàn)或轉(zhuǎn)換數(shù)據(jù)。（2）觸發(fā)器發(fā)生錯(cuò)誤時(shí)，異動(dòng)的結(jié)果會(huì)被撤銷。（3）可依照特定的情況替換異動(dòng)的指令。三、應(yīng)用觸發(fā)器知識(shí)儲(chǔ)備2、創(chuàng)建觸發(fā)器使用CREATETRIGGER可以創(chuàng)建觸發(fā)器，語(yǔ)法格式如下：CREATETRIGGER觸發(fā)器名觸發(fā)時(shí)間觸發(fā)事件ON表名FOREACHROW觸發(fā)器執(zhí)行語(yǔ)句需要說(shuō)明以下幾點(diǎn)。觸發(fā)時(shí)間表示觸發(fā)的時(shí)機(jī)，有兩個(gè)選項(xiàng)：一是AFTER，表示在觸發(fā)觸發(fā)器的執(zhí)行語(yǔ)句之后執(zhí)行；二是BEFORE，表示觸發(fā)觸發(fā)器的執(zhí)行語(yǔ)句之前驗(yàn)證新數(shù)據(jù)是否滿足使用規(guī)則。觸發(fā)事件用來(lái)指明在表中執(zhí)行哪類操作時(shí)激活觸發(fā)器，有3個(gè)選項(xiàng)：一是INSERT事件，表示向表中插入新行時(shí)觸活觸發(fā)器；二是DELETE事件，表示從表中刪除一條記錄時(shí)觸活觸發(fā)器；三是UPDATE事件，表示表中更新數(shù)據(jù)時(shí)觸發(fā)觸發(fā)器。FOREACHROW表示觸發(fā)器執(zhí)行的間隔，對(duì)于受觸發(fā)器事件影響的每行都要觸活觸發(fā)器的執(zhí)行語(yǔ)句。觸發(fā)器執(zhí)行語(yǔ)句是觸發(fā)器的主體，即當(dāng)觸發(fā)器觸活時(shí)，真正要執(zhí)行哪些操作。三、應(yīng)用觸發(fā)器知識(shí)儲(chǔ)備2、創(chuàng)建觸發(fā)器觸發(fā)器不能將任何結(jié)果返回到客戶端，不要在觸發(fā)器定義中包含SELECT語(yǔ)句和將數(shù)據(jù)返回客戶端的存儲(chǔ)過(guò)程。在觸發(fā)器執(zhí)行語(yǔ)句中，OLD關(guān)聯(lián)被刪除或被更新前的記錄，NEW關(guān)聯(lián)被插入或被更新后的記錄，在INSERT事件中可以使用NEW，在DELETE事件中可以使用OLD，在UPDATE事件中可以使用NEW、OLD。觸發(fā)器自動(dòng)執(zhí)行，所以要執(zhí)行觸發(fā)器需要讓相關(guān)數(shù)據(jù)表產(chǎn)生指定的事件。觸發(fā)器名一般由字母、數(shù)字和下畫(huà)線組成，建議前面加前綴tri_。示例11-21創(chuàng)建觸發(fā)器tri_CheckAge，user表中只能增加18歲以上的用戶。三、應(yīng)用觸發(fā)器知識(shí)儲(chǔ)備2、創(chuàng)建觸發(fā)器程序代碼如下：DELIMITER//CREATETRIGGERtri_CheckAge2BEFOREINSERTONuserFOREACHROWBEGIN IFYEAR(CURDATE())-YEAR(NEW.birthday)<18THEN SIGNALSQLSTATE'45000'SETmessage_text="用戶年齡小于18歲，無(wú)法插入。"; ENDIF;END//INSERTINTOuser(username,fkTitle,gender,nation,birthday,nativePlace,phone)VALUES("張建國(guó)","助理研究員","男","漢族","1980-1-29","湖南長(zhǎng)沙","132123XX321")//INSERTINTOuser(username,fkTitle,gender,nation,birthday,nativePlace,phone)VALUES("李大為","實(shí)習(xí)研究員","男","漢族","2020-11-19","廣東惠州","136363XX383")//DELIMITER;三、應(yīng)用觸發(fā)器知識(shí)儲(chǔ)備2、創(chuàng)建觸發(fā)器控制數(shù)據(jù)增加需要使用INSERT觸發(fā)器，在插入前應(yīng)檢查數(shù)據(jù)有效性，需要選擇BEFORE。在觸發(fā)器中，要檢查NEW的birthday值，判斷用戶是否已滿18歲，若未滿18歲，則設(shè)置錯(cuò)誤阻止數(shù)據(jù)插入。當(dāng)觸發(fā)觸發(fā)器時(shí)，第1條插入語(yǔ)句，年齡滿足要求成功插入，第2條年齡不滿足條件，插入失敗，并提示用戶失敗的原因。三、應(yīng)用觸發(fā)器知識(shí)儲(chǔ)備3、管理觸發(fā)器1）查看觸發(fā)器查看所有觸發(fā)器的語(yǔ)法格式如下：SHOWTRIGGERS;查看指定觸發(fā)器的語(yǔ)法格式如下：SELECT*FROMInformation_Schema.TriggerWHERETrigger_Name=觸發(fā)器名稱;2）刪除觸發(fā)器刪除觸發(fā)器的語(yǔ)法格式如下：DROPTRIGGER數(shù)據(jù)庫(kù)名.觸發(fā)器名;說(shuō)明：如果省略數(shù)據(jù)庫(kù)名，就表示在當(dāng)前數(shù)據(jù)庫(kù)中刪除指定的觸發(fā)器。示例11-22刪除觸發(fā)器tri_CheckAge。程序代碼如下：DROPTRIGGERIFEXISTStri_CheckAge;三、應(yīng)用觸發(fā)器知識(shí)儲(chǔ)備4、使用Navicat管理觸發(fā)器1）查看觸發(fā)器選中指定表并右擊，選擇“設(shè)計(jì)表”命令，進(jìn)入表的設(shè)計(jì)模式，單擊“觸發(fā)器”選項(xiàng)卡，可以看到當(dāng)前表的觸發(fā)器，如圖11-23所示，工作區(qū)上方顯示的是觸發(fā)器列表，下方顯示的是選中觸發(fā)器的內(nèi)容。三、應(yīng)用觸發(fā)器知識(shí)儲(chǔ)備4、使用Navicat管理觸發(fā)器2）添加觸發(fā)器單擊觸發(fā)器列表上方的“添加觸發(fā)器”按鈕，在觸發(fā)器列表中新增一行，輸入觸發(fā)器名稱，選擇觸發(fā)時(shí)間，根據(jù)需要勾選后面的“插入”復(fù)選框、“更新”復(fù)選框和“刪除”復(fù)選框，但只能勾選1個(gè)，在“定義”選項(xiàng)卡中輸入觸發(fā)器執(zhí)行代碼，如圖11-24所示，單擊“保存”按鈕。三、應(yīng)用觸發(fā)器知識(shí)儲(chǔ)備4、使用Navicat管理觸發(fā)器3）刪除觸發(fā)器先在觸發(fā)器列表中選擇指定的觸發(fā)器，再單擊觸發(fā)器列表上方的“刪除觸發(fā)器”按鈕即可刪除選中的觸發(fā)器。三、應(yīng)用觸發(fā)器任務(wù)實(shí)施任務(wù)11-7通過(guò)INSERT觸發(fā)器tri_checkplandate。對(duì)粵文創(chuàng)的工作計(jì)劃表plan進(jìn)行自動(dòng)檢查，要求計(jì)劃發(fā)布時(shí)間、計(jì)劃審核時(shí)間不能晚于操作當(dāng)前時(shí)間。任務(wù)11-8通過(guò)INSERT觸發(fā)器tri_checkparticipant。對(duì)粵文創(chuàng)的工作計(jì)劃參與人員表participant進(jìn)行自動(dòng)檢查，在任何計(jì)劃中每個(gè)人只能分配一項(xiàng)，即在任何一個(gè)計(jì)劃中工號(hào)是唯一的。任務(wù)11-9通過(guò)INSERT觸發(fā)器tri_checkplanall。對(duì)粵文創(chuàng)的工作計(jì)劃表plan進(jìn)行自動(dòng)檢查，要求計(jì)劃開(kāi)始時(shí)間早于計(jì)劃結(jié)束時(shí)間，計(jì)劃制訂者和計(jì)劃審核者不是同一個(gè)人，計(jì)劃發(fā)布時(shí)間要晚于計(jì)劃審核時(shí)間。二、使用存儲(chǔ)過(guò)程任務(wù)實(shí)施拓展任務(wù)11-7將觸發(fā)器tri_checkplanall完善為tri_checkplanallextend。將觸發(fā)器tri_checkplanall完善為tri_checkplanallextend，并指出具體錯(cuò)誤。04應(yīng)用事務(wù)四、應(yīng)用事務(wù)任務(wù)分析當(dāng)張三爸爸給張三轉(zhuǎn)當(dāng)月生活費(fèi)時(shí)，張三爸爸的賬戶已成功扣款2000元，正準(zhǔn)備給張三的賬戶增加2000元時(shí)突然停電，應(yīng)該怎么辦呢？不用著急，數(shù)據(jù)庫(kù)具有良好的事務(wù)處理機(jī)制。事務(wù)可以把一些相關(guān)操作作為一個(gè)整體進(jìn)行處理，要不全部成功完成，要不完全不做，若某一步出錯(cuò)則撤銷所有操作，回到原點(diǎn)。小王對(duì)粵文創(chuàng)進(jìn)行后得到的任務(wù)清單如下。任務(wù)編號(hào)任務(wù)內(nèi)容任務(wù)11-10利用事務(wù)為粵文創(chuàng)的plan表插入兩條記錄，其中一條記錄中的數(shù)據(jù)正確，另一條記錄中的數(shù)據(jù)不正確拓展任務(wù)11-8利用事務(wù)為粵文創(chuàng)的user表和plan表插入記錄四、應(yīng)用事務(wù)知識(shí)儲(chǔ)備1、事務(wù)簡(jiǎn)介事務(wù)是恢復(fù)和并發(fā)控制的基本單位。一個(gè)事務(wù)可以是一條SQL語(yǔ)句，也可以是一組SQL語(yǔ)句或整個(gè)程序。事務(wù)的屬性包括以下幾點(diǎn)。（1）原子性：事務(wù)是一個(gè)不可分割單位，其中包括的操作要么都做，要么都不做。（2）一致性：事務(wù)必須是使數(shù)據(jù)庫(kù)從一個(gè)一致性狀態(tài)變?yōu)榱硪粋€(gè)一致性狀態(tài)。（3）隔離性：一個(gè)事務(wù)內(nèi)部的操作及使用的數(shù)據(jù)對(duì)并發(fā)的其他事務(wù)是隔離的，并發(fā)執(zhí)行的各個(gè)事務(wù)之間不能互相干擾。（4）持久性：一個(gè)事務(wù)一旦提交，它對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)的改變就應(yīng)該是永久性的。四、應(yīng)用事務(wù)知識(shí)儲(chǔ)備2、事務(wù)操作1）開(kāi)始事務(wù)開(kāi)始事務(wù)的語(yǔ)法格式如下：STARTTRANSACTION2）提交事務(wù)提交事務(wù)的語(yǔ)法格式如下：COMMIT;

3）回滾事務(wù)回滾事務(wù)的語(yǔ)法格式如下：ROLLBACK;示例11-23通過(guò)事務(wù)為user表插入兩條記錄，其中一條數(shù)據(jù)正確，另一條數(shù)據(jù)不正確。四、應(yīng)用事務(wù)知識(shí)儲(chǔ)備2、事務(wù)操作方法一：不利用事務(wù)插入記錄。程序代碼如下：INSERTINTOuser(username,fkTitle,gender,nation,birthday,nativePlace,phone)VALUES("張建國(guó)","助理研究員","男","漢族","1980-1-29","湖南長(zhǎng)沙","132123XX321");INSERTINTOuser(username,fkTitle,gender,nation,birthday,nativePlace,phone)VALUES("李大為","實(shí)習(xí)研究員","no","漢族","2020-11-19","廣東惠州","136363XX383");SELECT*FROMuser;deletefromuserwhereusername="張建國(guó)";第1條記錄插入成功，第2條記錄插入失敗，為了方便后繼操作，應(yīng)將成功插入的記錄刪除。四、應(yīng)用事務(wù)知識(shí)儲(chǔ)備2、事務(wù)操作方法二：利用事務(wù)插入記錄DELIMITER//CREATEPROCEDUREpro_InsertData()BEGIN DECLAREresult_codeINTEGERDEFAULT0; DECLARECONTINUE