第7章SQL擴展編程數(shù)據(jù)庫原理與應(yīng)用1本章內(nèi)容7.1SQL編程基礎(chǔ)7.2存儲過程7.3函數(shù)7.4觸發(fā)器27.1SQL編程基礎(chǔ)7.1.1變量7.1.2游標(biāo)7.1.3運算符7.1.4分支結(jié)構(gòu)7.1.5循環(huán)結(jié)構(gòu)37.1.1變量MySQL的變量分為兩種：會話變量無需定義可直接使用，在使用時在變量名前加“@”符號即可。普通變量必須在存儲過程、函數(shù)或觸發(fā)器中使用，需要先聲明(定義)后使用，聲明時也可以賦值。4會話變量會話變量的賦值方式有三種：方式1：使用SET語句給會話變量賦值。

SET@變量名=表達(dá)式;例7-1定義會話變量num，并賦值為5。SET@num=5;5會話變量方式2：使用SELECT語句給會話變量賦值。

SELECT@變量名:=表達(dá)式;例7-2查詢books表中quanlity字段的最大值并賦值給會話變量max_qty。SELECT@max_qty:=max(quantity)FROMbooks;67.1.1變量方式3：使用INTO子句，直接將查詢結(jié)果賦給會話變量：

SELECT表達(dá)式INTO@變量名;例7-3查詢圖書的總數(shù)量，并將結(jié)果賦值給sum_qty。SELECTsum(quantity)INTO@sum_qtyFROMbooks;7查看會話變量的值可通過SELECT語句查看會話變量的值。例如：SELECT@sum_qty;8普通變量定義普通變量的方法：DECLARE變量名

類型[default默認(rèn)值];給普通變量賦值可以使用SET語句，方法為：

SET變量名=值;97.1.2游標(biāo)游標(biāo)(cursor)是SELECT語句檢索出來的結(jié)果集。游標(biāo)可以在存儲過程、函數(shù)、觸發(fā)器中使用。定義好游標(biāo)后，用戶可根據(jù)需要滾動或瀏覽游標(biāo)中數(shù)據(jù)。使用游標(biāo)的四個步驟：聲明游標(biāo)打開游標(biāo)遍歷游標(biāo)關(guān)閉游標(biāo)101.聲明游標(biāo)聲明游標(biāo)就是指定游標(biāo)所包含的結(jié)果，這個結(jié)果是由執(zhí)行SELECT語句產(chǎn)生的。聲明游標(biāo)語法如下：DECLARE游標(biāo)名稱CURSORFOR查詢語句;112.打開游標(biāo)聲明游標(biāo)后，MySQL并不執(zhí)行聲明游標(biāo)的SELECT語句，只有通過執(zhí)行打開游標(biāo)的操作，才會執(zhí)行游標(biāo)對應(yīng)的SELECT語句，產(chǎn)生游標(biāo)的內(nèi)容。打開游標(biāo)的語法：OPEN游標(biāo)名稱;123.遍歷游標(biāo)遍歷游標(biāo)是使用循環(huán)方法遍歷游標(biāo)結(jié)果集中的每行數(shù)據(jù)，在遍歷的過程中可對游標(biāo)數(shù)據(jù)進行處理。遍歷游標(biāo)的語法如下：

FETCH游標(biāo)名稱INTO變量列表;取出游標(biāo)指針?biāo)傅漠?dāng)前行數(shù)據(jù)，并將每個數(shù)據(jù)保存到相應(yīng)變量中，然后將游標(biāo)指針下移一行的數(shù)據(jù)一般和循環(huán)語句結(jié)合使用。13說明如果使用FETCH時當(dāng)前行無數(shù)據(jù)，會引發(fā)MySQL內(nèi)部的NOTFOUND異常。可以通過下述語句進行異常處理：DECLARECONTINUEHANDLERFORNOTFOUNDSET變量=TRUE;當(dāng)遇到NOTFOUND錯誤時,將變量設(shè)為TRUE。144.關(guān)閉游標(biāo)游標(biāo)使用完之后一定要關(guān)閉，以釋放游標(biāo)所占資源。關(guān)閉游標(biāo)的語句：CLOSE游標(biāo)名稱;157.1.3運算符介紹常用的算術(shù)運算符關(guān)系運算符邏輯運算符161.算術(shù)運算符主要用于數(shù)學(xué)運算，主要有：+、-、*、/、%（取余數(shù)）和MOD（取模）。說明：加法運算中，如果參與運算的數(shù)值或表達(dá)式有一個為NULL，則運算結(jié)果為NULL。乘法運算中，參與運算的兩方數(shù)值都為整型時，結(jié)果為整型；若有一個數(shù)值為浮點型，則結(jié)果為浮點型。除法運算中，一個數(shù)除以整數(shù)后，不管是否能除盡，結(jié)果都為一個浮點數(shù)；當(dāng)除數(shù)是0時，結(jié)果為NULL。172.關(guān)系運算符關(guān)系運算符有：>、>=、<、<=、=、<>、!=運算符的結(jié)果為真/假,在MySQL中用1/0表示。注意，=、<>、！=運算符不能用于NULL的比較，如果有一個值是NULL，則比較結(jié)果為NULL。183.邏輯運算符邏輯運算符：and、or、not運算結(jié)果與Java、C語言中的邏輯與、邏輯或、邏輯非的運算規(guī)則相同，運算符的結(jié)果為真/假,在MySQL中用1/0表示。197.1.4分支結(jié)構(gòu)包括單分支結(jié)構(gòu)、雙分支結(jié)構(gòu)和多分支結(jié)構(gòu)。單分支結(jié)構(gòu)

IF條件表達(dá)式THEN

語句塊ENDIF;當(dāng)條件表達(dá)式為“真”時，執(zhí)行語句塊。20雙分支結(jié)構(gòu)IF條件表達(dá)式THEN

語句塊1[ELSE

語句塊2]ENDIF;當(dāng)條件表達(dá)式為“真”時，執(zhí)行語句塊1，否則執(zhí)行語句塊2。21多分支結(jié)構(gòu)多分支結(jié)構(gòu)有兩種實現(xiàn)方法用IF語句實現(xiàn)用CASE表達(dá)式實現(xiàn)。22多分支結(jié)構(gòu)（續(xù)1）用IF語句實現(xiàn)：IF條件表達(dá)式1THEN

語句塊1[ELSEIF條件表達(dá)式2THEN

語句塊2][…n][ELSE

語句塊n+1]ENDIF;23多分支結(jié)構(gòu)（續(xù)2）用CASE表達(dá)式實現(xiàn)：CASE表達(dá)式WHEN常量值1THEN語句塊1;WHEN常量值2THEN語句塊2;……WHEN常量值nTHEN語句塊n;[ELSE語句塊n+1;]ENDCASE;247.1.5循環(huán)結(jié)構(gòu)MySQL的循環(huán)結(jié)構(gòu)有三種：WHILEREPEATLOOP25WHILE循環(huán)WHILE循環(huán)條件DO循環(huán)體語句塊ENDWHILE當(dāng)“循環(huán)條件”為真時，執(zhí)行循環(huán)體語句塊，當(dāng)循環(huán)條件為假時，結(jié)束循環(huán)。26REPEAT循環(huán)REPEAT循環(huán)體語句塊UNTIL結(jié)束循環(huán)的條件

ENDREPEAT;當(dāng)“結(jié)束循環(huán)的條件”為真時，結(jié)束循環(huán)，否則繼續(xù)執(zhí)行循環(huán)體。27LOOP循環(huán)循環(huán)名:LOOPIF判斷條件THENLEAVE循環(huán)名;ENDIF;其它循環(huán)體語句ENDLOOP;28程序從LOOP至ENDLOOP循環(huán)，在“LOOP”單詞前定義循環(huán)名，在循環(huán)體中，當(dāng)符合判斷條件時，通過“LEAVE循環(huán)名”語句結(jié)束循環(huán)。本章內(nèi)容7.1SQL編程基礎(chǔ)7.2存儲過程7.3函數(shù)7.4觸發(fā)器297.2存儲過程存儲過程(StoredProcedure)是數(shù)據(jù)庫中一組為了完成特定功能的SQL語句塊，它將常用或復(fù)雜的工作預(yù)先用SQL語句編寫腳本并指定名稱存儲在數(shù)據(jù)庫服務(wù)器中，存儲過程中可以包含控制語句，可以完成復(fù)雜的數(shù)據(jù)操作功能，用戶通過調(diào)用存儲過程來執(zhí)行它。存儲過程是數(shù)據(jù)庫中的一個重要對象，利用存儲過程可以提高數(shù)據(jù)的操作效率。30定義存儲過程DELIMITER$$CREATEPROCEDURE存儲過程名([[in|out|inout]參數(shù)1數(shù)據(jù)類型][,[in|out|inout]參數(shù)2數(shù)據(jù)類型,...])BEGIN [DECLARE變量名

類型[DEFAULT值];]

存儲過程的語句塊;END$$--DELIMITER語句的作用是重新定義MySQL語句的分隔符。31定義存儲過程（續(xù)）存儲過程中的參數(shù)：in：輸入?yún)?shù)(默認(rèn)情況下為in參數(shù))，該參數(shù)的值在調(diào)用時指定。out：輸出參數(shù)，存儲過程運行過程中可以對該類參數(shù)賦值，起到將計算結(jié)果返回給調(diào)用程序的作用。inout：既是輸入?yún)?shù)，又是輸出參數(shù)，該參數(shù)的值可在調(diào)用時指定，又可以將計算結(jié)果返回給調(diào)用程序。32調(diào)用存儲過程調(diào)用存儲過程使用CALL語句：CALL存儲過程名(實參列表)[;]33定義存儲過程示例例7-4定義一個無參數(shù)存儲過程，該存儲過程查詢?nèi)繄D書信息。DELIMITER$$CREATEPROCEDUREshowAllBooks()BEGINSELECTISBN,bname,category,press,pub_dateFROMbooks;END$$34定義存儲過程示例例7-5定義有一個輸入?yún)?shù)的存儲過程。該存儲過程查詢指定出版社出版的圖書信息，出版社為輸入?yún)?shù)。DELIMITER$$CREATEPROCEDUREshowPressBooks(INpressnamevarchar(40))BEGINSELECTISBN,bname,category,press,pub_dateFROMbooksWHEREpress=pressname;END$$35pressname為輸入?yún)?shù)，作為SELECT語句的查詢條件。當(dāng)調(diào)用有輸入?yún)?shù)的存儲過程時，必須為輸入?yún)?shù)指定一個確定值。調(diào)用存儲過程示例調(diào)用showPressBooks存儲過程，查詢“機械工業(yè)出版社”出版的圖書信息，結(jié)果如圖所示。CALLshowPressBooks('機械工業(yè)出版社');36定義存儲過程示例例7-6定義有多個輸入?yún)?shù)的存儲過程。查詢指定學(xué)院和性別的學(xué)生信息，學(xué)院和性別為輸入?yún)?shù)。DELIMITER$$CREATEPROCEDUREshowStudents(INsexchar(2),INcollegenamevarchar(30))BEGINSELECT*FROMstudentsWHEREgender=sexandcollege=collegename;END$$37調(diào)用存儲過程示例執(zhí)行帶多個參數(shù)的存儲過程時，實參的順序必須與定義存儲過程時定義的參數(shù)順序一致、類型兼容。調(diào)用本例存儲過程：查詢計算機學(xué)院的男生信息。CALLshowStudents('男','計算機學(xué)院');38定義存儲過程示例例7-7定義查詢指定出版社的圖書種類數(shù)的存儲過程，圖書種類數(shù)用輸出參數(shù)返回。DELIMITER$$CREATEPROCEDUREgetBookCount(inpressnameVARCHAR(20),outbookcountint)BEGINSELECTsum(quantity)INTObookcountFROMbooksWHEREpress=pressname;END$$39bookcount為out類型參數(shù)，可以在存儲過程中被賦值。調(diào)用存儲過程示例調(diào)用本例存儲過程：查詢“機械工業(yè)出版社”出版的圖書總數(shù)量，并用SELECT語句查看存儲過程的返回結(jié)果。CALLgetBookCount('機械工業(yè)出版社',@bcount);SELECT@bcount;@bcount作為實參，對應(yīng)存儲過程中的bookcount形參，用于保存存儲過程的返回結(jié)果。40定義使用IF語句的存儲過程示例例7-8建立滿足要求的存儲過程，該存儲過程首先根據(jù)給定的圖書ISBN號判斷該圖書的庫存數(shù)量，若庫存數(shù)量為0，則提示“庫存不足”；否則根據(jù)給定的ISBN號和學(xué)號在圖書借閱表中插入相應(yīng)數(shù)據(jù)，并給出提示“借閱成功”。41例7-8（續(xù)）DELIMITER$$CREATEPROCEDUREborrowBook(inisbncodevarchar(30),instuidchar(9))BEGINDECLAREbookcountint;--保存圖書數(shù)量DECLAREresultvarchar(50);--保存操作結(jié)果SELECTquantityINTObookcountFROMbooksWHEREisbn=isbncode;IFbookcount=0THENSETresult='庫存不足';ELSEINSERTINTOborrow(isbn,sid,borrow_time)VALUES(isbncode,stuid,now());UPDATEbooksSETquantity=quantity-1WHEREisbn=isbncode;SETresult='借閱成功';ENDIF;SELECTresult;END$$42假設(shè)ISBN號為“9787111650812”的圖書的庫存數(shù)量為1。第一次調(diào)用上述存儲過程，返回的結(jié)果如圖所示。CALLborrowBook('9787111650812','202101001');。用同樣的參數(shù)再次調(diào)用該存儲過程，返回的結(jié)果如圖7-7所示。CALLborrowBook('9787111650812','202101001');定義使用循環(huán)語句的存儲過程例7-9設(shè)已有數(shù)據(jù)表bookcountyear，該表有兩個字段pubyear和quantity，分別用于存儲圖書出版年份和對應(yīng)的圖書數(shù)量。現(xiàn)要統(tǒng)計2016-2022年間每年出版的圖書數(shù)量，并將結(jié)果存儲到數(shù)據(jù)表bookcountyear中。43例7-9（續(xù)）DELIMITER$$CREATEPROCEDUREgetBookCountYear()BEGINDECLAREpubyearint;SETpubyear=2016;WHILEpubyear<=2022doSELECTIFNULL(sum(quantity),0)INTO@bookcountFROMbooksWHEREyear(pub_date)=pubyear;INSERTINTObookcountyear(pubyear,quantity)VALUES(pubyear,@bookcount);SETpubyear=pubyear+1;ENDWHILE;END$$44調(diào)用該存儲過程：DELETEFROMbookcountyear;CALLgetBookCountYear();查詢bookcountyear表中數(shù)據(jù)，結(jié)果如圖/。所示。SELECT*FROMbookcountyear;定義使用游標(biāo)的存儲過程例7-10在books表中增加一列，列名：state，類型為文本字符串，默認(rèn)值為“正常”。使用游標(biāo)完成對圖書的如下處理，將2017年1月1日之前出版的圖書庫存量設(shè)為0，并將state列設(shè)為“已下架”，將2017年1月1日至2019年12月31日之間出版的圖書state列設(shè)為“即將下架”,2019年12月31日之后出版的圖書，其state列值用默認(rèn)值。45例7-10（續(xù)）DELIMITER$$CREATEPROCEDUREprocessbooks()BEGINDECLAREbookidCHAR(13);DECLAREpubdatevarchar(50);/*創(chuàng)建游標(biāo)結(jié)束標(biāo)志變量*/DECLAREcursor_ENDintDEFAULTFALSE;/*聲明游標(biāo)*/DECLAREbkscursorForSELECTisbn,pub_dateFROMbooks;/*設(shè)置游標(biāo)結(jié)束時cursor_END的值為true，用來判斷游標(biāo)是否結(jié)束*/DECLARECONTINUEHANDLERFORNOTFOUNDSETcursor_END=TRUE;/*打開游標(biāo)*/OPENbks;46例7-10（續(xù)）/*通過循環(huán)遍歷游標(biāo)*/a:LOOPFETCHbksINTObookid,pubdate;/*當(dāng)觸發(fā)NOTFOUND異常，退出循環(huán)*/IFcursor_ENDTHENLEAVEa;ENDIF;IFpubdate<'2017-01-01'THEN UPDATEbooksSETquantity=0,state='已下架'WHEREisbn=bookid;ELSEIFpubdateBETWEEN'2017-01-01'and'2019-12-31'THEN UPDATEbooksSETstate='即將下架'WHEREISBN=bookid;EndIF;ENDLOOP;/*關(guān)閉游標(biāo)*/CLOSEbks;END$$477.2.3維護存儲過程維護存儲過程的操作包括：刪除存儲過程查看存儲過程信息查看定義存儲過程的代碼487.2.3維護存儲過程

刪除存儲過程：DROPPROCEDURE存儲過程名;

查看系統(tǒng)中已有存儲過程的信息：SHOWCREATEPROCEDURE存儲過程名;查看某數(shù)據(jù)庫中特定存儲過程的信息：SHOWPROCEDURESTATUSWHEREdb='數(shù)據(jù)名'ANDNAME='過程名';49本章內(nèi)容7.1SQL編程基礎(chǔ)7.2存儲過程7.3函數(shù)7.4觸發(fā)器507.3.1函數(shù)的概念與作用函數(shù)也是數(shù)據(jù)庫中的一組完成特定功能的SQL語句集合。函數(shù)的定義和調(diào)用與存儲過程類似，但也有一些重要區(qū)別。自定義函數(shù)不能有輸出參數(shù)。自定義函數(shù)的函數(shù)體中必須包含一條RETURN語句。自定義函數(shù)是在SELECT語句中調(diào)用執(zhí)行的。517.3.2定義與調(diào)用函數(shù)定義函數(shù)使用CREATEFUNCTION語句CREATEFUNCTION函數(shù)名([參數(shù)1數(shù)據(jù)類型][,參數(shù)2數(shù)據(jù)類型]...])RETURNS<類型><函數(shù)體>52定義函數(shù)示例例7-11定義函數(shù)：計算兩個數(shù)的乘積。DELIMITER$$CREATEFUNCTIONsumprice(priceDECIMAL(6,2),quantityint)RETURNSDECIMAL(6,2)BEGINDECLAREallpriceDECIMAL(6,2);SETallprice=price*quantity;RETURNallprice;END$$53price和quantity為輸入?yún)?shù)，乘積的結(jié)果賦給allprice，函數(shù)返回值為allprice。調(diào)用函數(shù)示例調(diào)用例7-11函數(shù)，查看圖書的ISBN號、書名、價格、數(shù)量以及該圖書的總價（總價=價格*數(shù)量）：SELECTisbn,bname,price,quantity,sumprice(price,quantity)ASsumpriceFROMbooks;547.3.3維護函數(shù)刪除函數(shù)查看函數(shù)信息查看定義函數(shù)的代碼等。55刪除函數(shù)、查看函數(shù)信息刪除函數(shù)的語句的語法為：DROPFUNCTION函數(shù)名;查看系統(tǒng)中已有函數(shù)的信息：SHOWFUNCTIONSTATUS;56查看特定函數(shù)信息、函數(shù)定義代碼查看某數(shù)據(jù)庫中特定函數(shù)的信息SHOWFUNCTIONSTATUSWHEREDB='數(shù)據(jù)庫名'ANDNAME='函數(shù)名';查看指定函數(shù)的定義代碼SHOWCREATEFUNCTION函數(shù)名;57本章內(nèi)容7.1SQL編程基礎(chǔ)7.2存儲過程7.3函數(shù)7.4觸發(fā)器587.4.1觸發(fā)器的概念與作用觸發(fā)器和存儲過程一樣，也是存儲數(shù)據(jù)庫中的一段完成特定功能的SQL語句集合。主要區(qū)別是觸發(fā)器是由對數(shù)據(jù)進行的更改操作觸發(fā)自動執(zhí)行。這些操作包括INSERT、UPDATE、DELETE等。當(dāng)對數(shù)據(jù)表中的數(shù)據(jù)執(zhí)行插入、更新和刪除操作，需要自動執(zhí)行一些數(shù)據(jù)庫邏輯時，可以使用觸發(fā)器來實現(xiàn)。觸發(fā)器通常用于保證業(yè)務(wù)規(guī)則和復(fù)雜數(shù)據(jù)完整性。597.4.2定義觸發(fā)器定義觸發(fā)器時，需要指定觸發(fā)器名字、觸發(fā)器所作用的表、引發(fā)觸發(fā)器執(zhí)行的操作以及觸發(fā)器要完成的功能。CREATETRIGGER觸發(fā)器名{BEFORE|AFTER}{INSERT|UPDATE|DELETE}ON表名FOREACHROW觸發(fā)器體;60說明表名

：觸發(fā)器作用的對象。BEFORE|AFTER：觸發(fā)的時機。BEFORE：定義前觸發(fā)型觸發(fā)器。表示在引發(fā)觸發(fā)器執(zhí)行的操作執(zhí)行之前先執(zhí)行觸發(fā)器，再執(zhí)行引發(fā)觸發(fā)器執(zhí)行的操作。AFTER：定義后觸發(fā)型觸發(fā)器。表示在引發(fā)觸發(fā)器執(zhí)行的操作執(zhí)行之后，再執(zhí)行觸發(fā)器。INSERT|UPDATE|DELETE：指定引發(fā)觸發(fā)器執(zhí)行的操作。FOREACHROW：指定觸發(fā)器類型是行級觸發(fā)器。61定義觸發(fā)器示例例7-