MySQL云數(shù)據(jù)庫(kù)應(yīng)用與實(shí)踐 課件 ch07-數(shù)據(jù)庫(kù)服務(wù)端編程

第七章

數(shù)據(jù)庫(kù)服務(wù)端編程Database西北工業(yè)大學(xué)NorthwesternPolytechnicalUniversity數(shù)據(jù)庫(kù)目錄7.1

實(shí)戰(zhàn)目標(biāo)與準(zhǔn)備7.2變量7.3

函數(shù)7.4存儲(chǔ)過程7.5SQL控制流程語(yǔ)句7.6游標(biāo)7.7觸發(fā)器7.8預(yù)處理SQL語(yǔ)句7.9銀行場(chǎng)景化綜合實(shí)戰(zhàn)7.1實(shí)戰(zhàn)目標(biāo)與準(zhǔn)備實(shí)戰(zhàn)目標(biāo)數(shù)據(jù)庫(kù)服務(wù)端編程指編寫運(yùn)行在數(shù)據(jù)庫(kù)服務(wù)端的程序，具體主要包括服務(wù)端的存儲(chǔ)過程、自定義函數(shù)、觸發(fā)器等。為了便于描述，本書將存儲(chǔ)過程、用戶自定義函數(shù)、觸發(fā)器等不同形式的數(shù)據(jù)庫(kù)服務(wù)端程序統(tǒng)稱為SQL程序。掌握MySQL數(shù)據(jù)庫(kù)服務(wù)端編程的基本知識(shí)，包括編寫服務(wù)端程序需要的變量、控制流程語(yǔ)句、游標(biāo)、存儲(chǔ)過程、自定義函數(shù)、觸發(fā)器、事件等。7.2變量全局變量（global，系統(tǒng)變量，不能自定義，可改值）會(huì)話變量（session，系統(tǒng)變量，不能自定義，可改值）用戶變量（@var，用戶可以自定義）局部變量（參數(shù),declare）

7.2變量MySQL的用戶變量

@var，以“@”開頭，可以作用于當(dāng)前整個(gè)連接,但是若當(dāng)前連接斷開后,所定義的用戶變量都會(huì)消失?？梢栽诖鎯?chǔ)過程之間傳遞全局范圍的變量。

賦值：set@count=1;

selectcount(id)into@count

fromitemswhereprice<99;讀?。簊elect@count；7.2變量MySQL的局部變量

注意：

1）支持SQL的數(shù)據(jù)類型

2）給出DEFAULT，該變量進(jìn)入BEGIN塊時(shí)初始化為該值.例：DECLARE語(yǔ)句部分聲明變量,SET進(jìn)行賦值。declarenamesvarchar(10)default'';declareiintdefault0;setnames=concat('test','');字符串拼接函數(shù)：concat7.2變量MySQL變量對(duì)比

7.3函數(shù)——系統(tǒng)內(nèi)置函數(shù)SQL常用的內(nèi)置函數(shù)可以分為：數(shù)學(xué)函數(shù)（如絕對(duì)值函數(shù)等）聚合函數(shù)（如求和、求平均函數(shù)等）字符串函數(shù)（如求字符串長(zhǎng)度、求子串函數(shù)等）日期和時(shí)間函數(shù)（如返回當(dāng)前日期函數(shù)等）格式化函數(shù)（如字符串轉(zhuǎn)IP地址函數(shù)等）控制流函數(shù)（如邏輯判斷函數(shù)等）加密函數(shù)（如使用密鑰對(duì)字符串加密函數(shù)等）系統(tǒng)信息函數(shù)（如返回當(dāng)前數(shù)據(jù)庫(kù)名、服務(wù)器版本函數(shù)等）7.3函數(shù)——用戶自定義函數(shù)delimiter$$createfunction函數(shù)名(參數(shù)類型)returns返回類型Begin

方法體

return數(shù)據(jù)類型；end$$delimiter；select函數(shù)名

7.3函數(shù)——用戶自定義函數(shù)要求：指定年和月時(shí)，編寫一個(gè)自定義函數(shù)：求該年該月的平均氣溫。某氣象站有一張表temperature，每天在2點(diǎn)，8點(diǎn)，14點(diǎn)，20點(diǎn)自動(dòng)采集溫度7.3函數(shù)——用戶自定義函數(shù)DELIMITER$CREATEFUNCTIONtemp_avg(myyearint,mymonthint)RETURNSrealBEGINDECLAREtemprealDEFAULT0;SELECT(SUM(T02)+SUM(T08)+SUM(T14)+SUM(T20))/(Count(T02)+Count(T08)+Count(T14)+Count(T20))INTOtempFROMTemperatureWHEREYear=myyearANDMonth=mymonth;returntemp;END$某氣象站有一張表temperature，每天在2點(diǎn)，8點(diǎn)，14點(diǎn)，20點(diǎn)自動(dòng)采集溫度7.4存儲(chǔ)過程delimiter$$createprocedure存儲(chǔ)過程名(參數(shù))Begin

方法體end$$delimiter；call存儲(chǔ)過程名

7.4存儲(chǔ)過程MYSQL存儲(chǔ)過程/存儲(chǔ)函數(shù)完整語(yǔ)法7.4存儲(chǔ)過程存儲(chǔ)過程示例7.4存儲(chǔ)過程MySQL的參數(shù)傳遞存儲(chǔ)過程：IN，OUT，INOUT類型函數(shù)：所有參數(shù)為IN類型7.5SQL控制流程語(yǔ)句MySQL流程控制：條件分支IFsearch_conditionTHENstatement_list[ELSEIFsearch_conditionTHENstatement_list]...[ELSEstatement_list]ENDIFCASEcase_value

WHENwhen_valueTHENstatement_list[WHENwhen_valueTHENstatement_list]...[ELSEstatement_list]ENDCASECASE

WHENsearch_conditionTHENstatement_list[WHENsearch_conditionTHENstatement_list]...[ELSEstatement_list]ENDCASEIFCASEWHEN7.5SQL控制流程語(yǔ)句MySQL流程控制：循環(huán)REPEAT[begin_label:]REPEATstatement_listUNTILsearch_conditionENDREPEAT[end_label]mysql>delimiter//mysql>CREATEPROCEDUREdorepeat(p1INT)BEGINSET@x=0;

REPEATSET@x=@x+1;

UNTIL@x>p1ENDREPEAT;END//QueryOK,0rowsaffected(0.00sec)mysql>CALLdorepeat(1000)//QueryOK,0rowsaffected(0.00sec)7.5SQL控制流程語(yǔ)句MySQL流程控制：循環(huán)WHILE[begin_label:]WHILEsearch_conditionDOstatement_listENDWHILE[end_label]CREATEPROCEDURE

dowhile()BEGINDECLAREv1INTDEFAULT5;

WHILEv1>0DO...SETv1=v1-1;

ENDWHILE;END;7.5SQL控制流程語(yǔ)句MySQL流程控制：循環(huán)LOOP[begin_label:]LOOPstatement_listENDLOOP[end_label]CREATEPROCEDUREdoiterate(p1INT)BEGIN

label1:LOOPSETp1=p1+1;IFp1<10THEN

ITERATElabel1;ENDIF;

LEAVElabel1;

ENDLOOP

label1;SET@x=p1;END;7.6游標(biāo)游標(biāo)

若查詢SQL只返回一條記錄可以放入一個(gè)變量，當(dāng)返回

多條記錄時(shí)，需使用游標(biāo)逐行處理結(jié)果集。聲明游標(biāo)(DECLARE)打開游標(biāo)(OPEN)使用游標(biāo)讀取數(shù)據(jù)(FETCH)關(guān)閉游標(biāo)(CLOSE)7.6游標(biāo)delimiter$$createprocedureget_cs_s()begin

declareout_namesvarchar(100)default'';declaretmpvarchar(100)default'';declarep_snovarchar(5)default'';declareP_snamevarchar(10)default'';declaredonebooleandefault0;

declarecursor_namecursorforselectsno,snamefromswheresdept='CS';

declarecontinuehandlerforsqlstate'02000'

setdone=1;

--ER_SP_FETCH_NO_DATA

opencursor_name;

fetchcursor_nameintop_sno,p_sname;

repeat

settmp=concat(p_sno,'_',p_sname);setout_names=concat(out_names,tmp,'');

fetchcursor_nameintop_sno,p_sname;

untildone

endrepeat;

closecursor_name;

selectout_names;end$$delimiter;7.7觸發(fā)器1.觸發(fā)器定義-MySQL7.7觸發(fā)器2.觸發(fā)器的分類（DML）BEFORE觸發(fā)器AFTER觸發(fā)器INSTEADOF觸發(fā)器執(zhí)行INSTEADOF觸發(fā)器代替通常的增刪改等觸發(fā)動(dòng)作。PG:僅支持視圖上的該觸發(fā)器。INSERT觸發(fā)器UPDATE觸發(fā)器TRUNCATE觸發(fā)器DELETE觸發(fā)器在定義了觸發(fā)器的表上發(fā)生修改操作時(shí)，會(huì)自動(dòng)為觸發(fā)器的運(yùn)行而派生兩個(gè)記錄：1)Old—存放舊記錄(for:delete,update)2)New—存放新記錄(for:insert,update)REFERENCING子句：將對(duì)應(yīng)記錄聲明為一個(gè)表格使用7.7觸發(fā)器2.觸發(fā)器的分類（DML）WhenEventRow-levelStatement-level(mysql不支持)BEFORE/AFTER

INSERT/UPDATE/DELETETableTableandViewBEFORE/AFTERTRUNCATE-TableINSTEADOF（mysql不支持）INSERT/UPDATE/DELETEViewTRUNCATE:清空表中的數(shù)據(jù)，但是不刪除表結(jié)構(gòu)Trigger可以暫?；蛘邌⒂?.7觸發(fā)器3.觸發(fā)器修改/刪除修改：不支持

刪除DROPTRIGGERdatabase.trigger_name(mysql)DROPTRIGGERtrigger_nameONtablename

(其他多數(shù)DBMS)查看觸發(fā)器：SHOWTRIGGERS7.7觸發(fā)器引用：/doc/refman/8.0/en/signal.htmlusestudent;droptriggerifexistsdel_s;delimiter$$createtriggerdel_sbeforedeleteonsforeachrowbeginifOLD.snoin(selectdistinct(sno)fromsc)then

signalsqlstate'03100'setmessage_text='不能刪除有選課記錄的學(xué)生';endif;end$$delimiter;sqlstate：='00'(success)='01'(warning)='02'(notfound)>'02'(exception)='40'MySQL中的異常處理：signalsqlstate7.7觸發(fā)器signalsqlstate'31000'：錯(cuò)誤代碼說明（00表示正常，不能用于signal語(yǔ)句）

7.7觸發(fā)器/doc/refman/8.0/en/trigger-syntax.html同一個(gè)表上可以存在兩個(gè)同類型同事件的觸發(fā)器（如兩個(gè)beforeupdate），順序是按照創(chuàng)建順序被觸發(fā)；如果希望顯示指定觸發(fā)器的順序，可以通過:precedes或者follows關(guān)鍵字指定7.7觸發(fā)器4.MySQL中的Event

(不是一種通常意義的觸發(fā)器）/doc/refman/8.0/en/create-event.html7.7觸發(fā)器5.觸發(fā)器示例1-MySQL例1：創(chuàng)建一個(gè)觸發(fā)器，當(dāng)每插入一條記錄時(shí)累加金額CREATETRIGGERins_sumBEFOREINSERTONaccountFOREACHROWSET@sum=@sum+NEW.amount;DROPTRIGGERins_sum;7.8預(yù)處理SQLprepare預(yù)處理：指將SQL語(yǔ)句中的關(guān)鍵字和數(shù)據(jù)分離，對(duì)固定SQL語(yǔ)句中只進(jìn)行一次解析或查詢計(jì)劃生成，這樣在數(shù)據(jù)不同但SQL語(yǔ)句相同的情況下，執(zhí)行性能會(huì)顯著提升。PREPAREstmt_nameFROMpreparable_stmtEXECUTEstmt_name[USING@var_name[,@var_name]...]{DEALLOCATE|DROP}PREPAREstmt_name7.8預(yù)處理SQLmysql>