第七章

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

實戰(zhàn)目標與準備7.2變量7.3

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

7.2變量MySQL的用戶變量

@var，以“@”開頭，可以作用于當前整個連接,但是若當前連接斷開后,所定義的用戶變量都會消失?？梢栽诖鎯^程之間傳遞全局范圍的變量。

賦值：set@count=1;

selectcount(id)into@count

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

注意：

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

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

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

方法體

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

7.3函數(shù)——用戶自定義函數(shù)要求：指定年和月時，編寫一個自定義函數(shù)：求該年該月的平均氣溫。某氣象站有一張表temperature，每天在2點，8點，14點，20點自動采集溫度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點，8點，14點，20點自動采集溫度7.4存儲過程delimiter$$createprocedure存儲過程名(參數(shù))Begin

方法體end$$delimiter；call存儲過程名

7.4存儲過程MYSQL存儲過程/存儲函數(shù)完整語法7.4存儲過程存儲過程示例7.4存儲過程MySQL的參數(shù)傳遞存儲過程：IN，OUT，INOUT類型函數(shù)：所有參數(shù)為IN類型7.5SQL控制流程語句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控制流程語句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控制流程語句MySQL流程控制：循環(huán)WHILE[begin_label:]WHILEsearch_conditionDOstatement_listENDWHILE[end_label]CREATEPROCEDURE

dowhile()BEGINDECLAREv1INTDEFAULT5;

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

ENDWHILE;END;7.5SQL控制流程語句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游標游標

若查詢SQL只返回一條記錄可以放入一個變量，當返回

多條記錄時，需使用游標逐行處理結(jié)果集。聲明游標(DECLARE)打開游標(OPEN)使用游標讀取數(shù)據(jù)(FETCH)關(guān)閉游標(CLOSE)7.6游標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ā)動作。PG:僅支持視圖上的該觸發(fā)器。INSERT觸發(fā)器UPDATE觸發(fā)器TRUNCATE觸發(fā)器DELETE觸發(fā)器在定義了觸發(fā)器的表上發(fā)生修改操作時，會自動為觸發(fā)器的運行而派生兩個記錄：1)Old—存放舊記錄(for:delete,update)2)New—存放新記錄(for:insert,update)REFERENCING子句：將對應(yīng)記錄聲明為一個表格使用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='不能刪除有選課記錄的學生';endif;end$$delimiter;sqlstate：='00'(success)='01'(warning)='02'(notfound)>'02'(exception)='40'MySQL中的異常處理：signalsqlstate7.7觸發(fā)器signalsqlstate'31000'：錯誤代碼說明（00表示正常，不能用于signal語句）

7.7觸發(fā)器/doc/refman/8.0/en/trigger-syntax.html同一個表上可以存在兩個同類型同事件的觸發(fā)器（如兩個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)建一個觸發(fā)器，當每插入一條記錄時累加金額CREATETRIGGERins_sumBEFOREINSERTONaccountFOREACHROWSET@sum=@sum+NEW.amount;DROPTRIGGERins_sum;7.8預(yù)處理SQLprepare預(yù)處理：指將SQL語句中的關(guān)鍵字和數(shù)據(jù)分離，對固定SQL語句中只進行一次解析或查詢計劃生成，這樣在數(shù)據(jù)不同但SQL語句相同的情況下，執(zhí)行性能會顯著提升。PREPAREstmt_nameFROMpreparable_stmtEXECUTEstmt_name[USING@var_name[,@var_name]...]{DEALLOCATE|DROP}PREPAREstmt_name7.8預(yù)處理SQLmysql>