MySQL數(shù)據(jù)庫管理與應(yīng)用任務(wù)式教程（微課版）單元10 存儲(chǔ)過程與觸發(fā)器

單元10存儲(chǔ)過程與觸發(fā)器《MySQL數(shù)據(jù)庫管理與應(yīng)用任務(wù)式教程（慕課版）》學(xué)習(xí)內(nèi)容任務(wù)1存儲(chǔ)過程

2任務(wù)2觸發(fā)器

存儲(chǔ)過程是一種數(shù)據(jù)庫對象，是存儲(chǔ)在服務(wù)器上的一組預(yù)定義的SQL語句集合。使用存儲(chǔ)過程的目的是將常用或復(fù)雜的工作預(yù)先用SQL語句寫好并用一個(gè)指定名稱存儲(chǔ)起來，這個(gè)過程經(jīng)編譯和優(yōu)化后存儲(chǔ)在數(shù)據(jù)庫服務(wù)器中，因此稱為存儲(chǔ)過程。當(dāng)以后需要數(shù)據(jù)庫提供與已定義好的存儲(chǔ)過程的功能相同的服務(wù)時(shí)，只需調(diào)用存儲(chǔ)過程即可。存儲(chǔ)過程可以用來完成轉(zhuǎn)換數(shù)據(jù)、遷移數(shù)據(jù)、制作報(bào)表等數(shù)據(jù)庫管理中的復(fù)雜操作，以減少數(shù)據(jù)庫管理員的工作量。由于存儲(chǔ)過程是在MySQL服務(wù)器中存儲(chǔ)和執(zhí)行的，因此可以避免重復(fù)編寫相同的代碼，減少客戶端和服務(wù)器端的數(shù)據(jù)傳輸，執(zhí)行速度快，提高系統(tǒng)性能。存儲(chǔ)過程中通過流程控制語句可以完成較復(fù)雜的判斷和運(yùn)算，實(shí)現(xiàn)更強(qiáng)的功能。此外，存儲(chǔ)過程也可以增強(qiáng)數(shù)據(jù)庫使用的安全性和數(shù)據(jù)完整性。

存儲(chǔ)過程具有與函數(shù)不同的特點(diǎn)，兩者區(qū)別如下。（1）語法定義的關(guān)鍵字不同，存儲(chǔ)過程使用PROCEDURE，而函數(shù)使用fUNCTION。（2）存儲(chǔ)過程在創(chuàng)建時(shí)沒有設(shè)置返回值，而函數(shù)定義時(shí)必須設(shè)置返回值。（3）存儲(chǔ)過程沒有返回值類型，且不能將結(jié)果直接賦給變量；而函數(shù)定義時(shí)要設(shè)置返回值類型，且在調(diào)用時(shí)必須將返回值賦給變量。（4）存儲(chǔ)過程必須通過CALL調(diào)用，不能用SELECT語句調(diào)用；而函數(shù)可以直接使用SELECT語句調(diào)用。任務(wù)1

存儲(chǔ)過程10.1.1創(chuàng)建存儲(chǔ)過程1．創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程與創(chuàng)建自定義函數(shù)大體相同，其語法格式如下。CREATEPROCEDURE存儲(chǔ)過程名([[IN|OUT|INOUT]參數(shù)名數(shù)據(jù)類型])

存儲(chǔ)過程體說明：（1）PROCEDURE：存儲(chǔ)過程標(biāo)識(shí)。（2）IN|OUT|INOUT：在為存儲(chǔ)過程設(shè)置參數(shù)時(shí)，在參數(shù)名前可指定參數(shù)的來源及用途。IN為默認(rèn)值，表示輸入?yún)?shù)，即參數(shù)是在調(diào)用存儲(chǔ)過程時(shí)傳入存儲(chǔ)過程里使用，傳入的數(shù)據(jù)可以是直接數(shù)據(jù)，也可以是保存的數(shù)據(jù)變量；OUT表示輸出參數(shù)，初始值為NULL，它的作用是將存儲(chǔ)過程中的值保存到OUT指定的參數(shù)中，返回給調(diào)用者；INOUT表示輸入輸出參數(shù)，INOUT參數(shù)跟OUT類似，都可以從存儲(chǔ)過程內(nèi)部傳值給調(diào)用者，不同的是調(diào)用者還可以通過INOUT參數(shù)傳遞值給存儲(chǔ)過程。（3）過程體：存儲(chǔ)過程的主體部分，包含在存儲(chǔ)過程調(diào)用的時(shí)候必須執(zhí)行的SQL語句。這個(gè)部分以關(guān)鍵字BEGIN開始，以關(guān)鍵字END結(jié)束。若存儲(chǔ)過程體中只有一條SQL語句，則可以省略BEGIN和END關(guān)鍵字。任務(wù)1

存儲(chǔ)過程【例題10.1】在cjgl數(shù)據(jù)庫中創(chuàng)建一個(gè)名稱為proc_rjxs的存儲(chǔ)過程，其功能是顯示學(xué)生xs表中軟件技術(shù)專業(yè)的學(xué)生的信息。執(zhí)行如下語句。USEcjgl;CREATEPROCEDUREproc_rjxs()

SELECT*FROMxsWHERE專業(yè)名='軟件技術(shù)';任務(wù)1

存儲(chǔ)過程【例題10.2】在cjgl數(shù)據(jù)庫中創(chuàng)建一個(gè)名稱為proc_xsxx的存儲(chǔ)過程，其功能是根據(jù)給定的學(xué)號(hào)顯示相應(yīng)學(xué)生的信息。執(zhí)行如下語句。DELIMITER//CREATEPROCEDUREproc_xsxx(xhCHAR(6))BEGIN

SELECT*FROMxsWHERE學(xué)號(hào)=xh;END//DELIMITER;【例題10.3】在cjgl數(shù)據(jù)庫中創(chuàng)建一個(gè)帶輸入輸出參數(shù)的存儲(chǔ)過程proc_xmxh，其功能是根據(jù)給定的學(xué)生姓名返回相應(yīng)學(xué)生的學(xué)號(hào)。執(zhí)行如下語句。DELIMITER//CREATEPROCEDUREproc_xmxh(INxmCHAR(8),OUTxhCHAR(6))BEGINSELECT學(xué)號(hào)INTOxhFROMxsWHERE姓名=xm;END//DELIMITER;任務(wù)1

存儲(chǔ)過程2．調(diào)用存儲(chǔ)過程在MySQL中使用CALL語句來調(diào)用存儲(chǔ)過程，其語法格式如下。CALL存儲(chǔ)過程名([參數(shù)[...]]);【例題10.4】調(diào)用存儲(chǔ)過程proc_rjxs，顯示學(xué)生表xs中軟件技術(shù)專業(yè)的學(xué)生的信息。調(diào)用存儲(chǔ)過程p_xsxx，顯示學(xué)號(hào)為001101的學(xué)生的信息。調(diào)用存儲(chǔ)過程proc_xmxh，查詢學(xué)生“王金華”的學(xué)號(hào)。分別執(zhí)行如下語句。CALLproc_rjxs();CALLproc_xsxx('001101');CALLproc_xmxh('王金華',@xh);SELECT@xh;任務(wù)1

存儲(chǔ)過程10.1.2管理存儲(chǔ)過程1．查看存儲(chǔ)過程（1）查看存儲(chǔ)過程的定義。

在MySQL中可以通過SHOWCREATE語句查看存儲(chǔ)過程的定義，其語法格式如下。

SHOWCREATEPROCEDURE存儲(chǔ)過程名;

如SHOWCREATEPROCEDUREproc_rjxs;。（2）查看存儲(chǔ)過程的狀態(tài)信息。

在MySQL中可以通過SHOWSTATUS語句查看存儲(chǔ)過程的狀態(tài)，其語法格式如下。SHOWPROCEDURESTATUSLIKE存儲(chǔ)過程名;如SHOWPROCEDURESTATUSLIKE'proc_xsxx';。任務(wù)1

存儲(chǔ)過程2．修改存儲(chǔ)過程的特征信息可以使用ALTERPROCEDURE語句修改存儲(chǔ)過程的某些特征，其語法格式如下。ALTERPROCEDURE存儲(chǔ)過程名[特征...]特征用于指定存儲(chǔ)過程的特性，特征內(nèi)容主要包括如下選項(xiàng)，其中特征信息的順序可任意設(shè)置。（1）COMMENT'注釋內(nèi)容'：表示注釋信息。（2）CONTAINSSQL：表示子程序包含SQL語句，但不包含讀或?qū)憯?shù)據(jù)的語句。（3）NOSQL：表示子程序中不包含SQL語句。（4）SQLSECURITYDEFINER：表示只有定義者才有權(quán)執(zhí)行存儲(chǔ)過程。（5）SQLSECURITYINVOKER：表示調(diào)用者有權(quán)執(zhí)行存儲(chǔ)過程。（6）READSSQLDATA：表示子程序中包含讀數(shù)據(jù)的語句。（7）MODIFIESSQLDATA：表示子程序中包含寫數(shù)據(jù)的語句。任務(wù)1

存儲(chǔ)過程注意：ALTERPROCEDURE語句不能用來修改存儲(chǔ)過程體。如果要修改存儲(chǔ)過程的內(nèi)容，可以先刪除原存儲(chǔ)過程，再以相同的名字創(chuàng)建新的存儲(chǔ)過程；如果要修改存儲(chǔ)過程的名稱，可以先刪除原存儲(chǔ)過程，再以不同的名字創(chuàng)建新的存儲(chǔ)過程?！纠}10.5】修改存儲(chǔ)過程proc_rjxs的定義，將讀寫權(quán)限改為MODIFIESSQLDATA，并指明可調(diào)用者。代碼如下。ALTERPROCEDUREproc_rjxsMODIFIESSQLDATASQLSECURITYINVOKER;任務(wù)1

存儲(chǔ)過程3．刪除存儲(chǔ)過程在MySQL中，可使用DROPPROCEDURE語句來刪除數(shù)據(jù)庫中已經(jīng)存在的存儲(chǔ)過程，其語法格式如下。DROPPROCEDURE[IFEXISTS]存儲(chǔ)過程名；【例題10.6】刪除名為proc_rjxs的存儲(chǔ)過程。代碼如下。DROPPROCEDUREIFEXISTSproc_rjxs;10.1.3使用MySQLWorkbench圖形化工具創(chuàng)建和管理存儲(chǔ)過程1．創(chuàng)建存儲(chǔ)過程【例10.7】在cjgl數(shù)據(jù)庫中創(chuàng)建一個(gè)名稱為proc_wlxs的存儲(chǔ)過程，其功能是顯示學(xué)生表xs中網(wǎng)絡(luò)技術(shù)專業(yè)的學(xué)生的信息。打開MySQLWorkbench，在工具欄中單擊“創(chuàng)建一個(gè)新存儲(chǔ)過程”按鈕，即可打開新建存儲(chǔ)過程界面。任務(wù)1

存儲(chǔ)過程在創(chuàng)建存儲(chǔ)過程的界面中，設(shè)置存儲(chǔ)過程的名稱和定義，單擊Apply按鈕，可以預(yù)覽當(dāng)前操作的SQL語句。然后單擊Apply按鈕，在彈出的對話框中單擊Finish按鈕，即可完成存儲(chǔ)過程proc_wlxs的創(chuàng)建。任務(wù)1

存儲(chǔ)過程調(diào)用存儲(chǔ)過程proc_wlxs，結(jié)果如圖10-3所示。任務(wù)1

存儲(chǔ)過程2．管理存儲(chǔ)過程在SCHEMAS欄中，展開cjgl數(shù)據(jù)庫中的StoredProcedures，在存儲(chǔ)過程proc_rjxs上右擊，通過彈出式菜單，可以實(shí)現(xiàn)創(chuàng)建存儲(chǔ)過程、修改存儲(chǔ)過程、刪除存儲(chǔ)過程等操作，如圖10-4所示。任務(wù)1

存儲(chǔ)過程圖10-4管理存儲(chǔ)過程的彈出式菜單10.2.1觸發(fā)器概述

觸發(fā)器是一種特殊類型的存儲(chǔ)過程，與存儲(chǔ)過程的區(qū)別在于存儲(chǔ)過程通過調(diào)用實(shí)現(xiàn)，而觸發(fā)器不能被顯式地調(diào)用，只有在預(yù)先定義好的事件發(fā)生時(shí)，才會(huì)被激活從而被執(zhí)行。觸發(fā)器與數(shù)據(jù)表關(guān)聯(lián)，在MySQL中，只有執(zhí)行INSERT、UPDATE和DELETE操作時(shí)才能激活觸發(fā)器，其他SQL語句則不會(huì)激活觸發(fā)器。例如，向數(shù)據(jù)表中插入數(shù)據(jù)前強(qiáng)制檢驗(yàn)或轉(zhuǎn)換數(shù)據(jù)，或在觸發(fā)器中的代碼執(zhí)行發(fā)生了錯(cuò)誤后撤銷已執(zhí)行成功的操作等。

觸發(fā)器主要用于加強(qiáng)業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性。觸發(fā)器可以實(shí)現(xiàn)比CHECK約束更復(fù)雜的檢查和操作，以保護(hù)表中的數(shù)據(jù)?？梢杂捎|發(fā)器通過數(shù)據(jù)庫中的相關(guān)表進(jìn)行級(jí)聯(lián)無痕更新操作，實(shí)現(xiàn)對數(shù)據(jù)的完全校驗(yàn)，在一定程度上保證了數(shù)據(jù)的完整性。一般在使用觸發(fā)器前應(yīng)優(yōu)先考慮用約束，在必要時(shí)才使用觸發(fā)器。

使用觸發(fā)器的不足之處是如果需要變動(dòng)的數(shù)據(jù)量較大，觸發(fā)器的執(zhí)行效率會(huì)非常低。使用觸發(fā)器實(shí)現(xiàn)的業(yè)務(wù)邏輯在出現(xiàn)問題時(shí)很難進(jìn)行定位，特別是在涉及多個(gè)觸發(fā)器的情況下，會(huì)增加維護(hù)成本。

根據(jù)觸發(fā)事件，觸發(fā)器分為3種：INSERT觸發(fā)器、UPDATE觸發(fā)器和DELETE觸發(fā)器

根據(jù)觸發(fā)器發(fā)生的時(shí)機(jī)，觸發(fā)器可分為BEFORE觸發(fā)器和AFTER觸發(fā)器。任務(wù)2

觸發(fā)器10.2.2創(chuàng)建觸發(fā)器

在MySQL中，可使用CREATETRIGGER語句創(chuàng)建觸發(fā)器，其語法格式如下。CREATETRIGGER觸發(fā)器名<BEFORE|AFTER><INSERT|UPDATE|DELETE>ON表名FOREACHROW[FOLLOWS|PRECEDES]

觸發(fā)器體說明：（1）BEFORE|AFTER：觸發(fā)時(shí)機(jī)，表示觸發(fā)器是在激活它的語句之前或之后觸發(fā)BEFORE：在觸發(fā)它的語句執(zhí)行之前執(zhí)行，一般用來驗(yàn)證新數(shù)據(jù)是否滿足條件。AFTER：在觸發(fā)它的語句執(zhí)行之后再執(zhí)行一些操作。（2）INSERT|UPDATE|DELETE：觸發(fā)事件，表示激活觸發(fā)器的操作類型。INSERT：將新行插入表時(shí)激活觸發(fā)器。DELETE：從表中刪除某一行數(shù)據(jù)時(shí)激活觸發(fā)器。UPDATE：更改表中某一行數(shù)據(jù)時(shí)激活觸發(fā)器。任務(wù)2

觸發(fā)器（3）ON表FOREACHROW：用于指定觸發(fā)器的操作對象。FOREACHROW：指行級(jí)觸發(fā)，對于觸發(fā)事件影響的每一行都要激活觸發(fā)器的動(dòng)作。（4）FOLLOWS|PRECEDES：觸發(fā)順序，表示指定同一個(gè)表中多個(gè)觸發(fā)器的執(zhí)行順序，默認(rèn)按創(chuàng)建順序激活。FOLLOWS：表示新觸發(fā)器在現(xiàn)有觸發(fā)器之后激活。PRECEDES：表示新觸發(fā)器在現(xiàn)有觸發(fā)器之前激活。任務(wù)2

觸發(fā)器當(dāng)對學(xué)生表進(jìn)行插入、更新或刪除操作時(shí)，有時(shí)希望自動(dòng)給出一些提示信息，或者自動(dòng)做一些相應(yīng)的處理，如在學(xué)生表中刪除一條學(xué)生信息時(shí)，需要?jiǎng)h除其成績表上的對應(yīng)記錄，這時(shí)就可以使用觸發(fā)器?！纠}10.8】創(chuàng)建觸發(fā)器xs_AFTER_INSERT，其功能是在向?qū)W生表中插入一條學(xué)生記錄后，可顯示“已向xs表中插入一條記錄”的消息。實(shí)現(xiàn)代碼如下，結(jié)果如圖10-5所示。DELIMITER//CREATETRIGGERxs_AFTER_INSERTAFTERINSERTONxsFOREACHROWBEGINDECLAREmessagevarchar(40);SELECT'已向xs表中插入一條記錄'INTO@message;END//DELIMITER;INSERTINTOxs(姓名,學(xué)號(hào),專業(yè)名,性別,出生時(shí)間,總學(xué)分,備注)VALUES('劉國梁','001112','計(jì)算機(jī)應(yīng)用','男','2000-1-300:0:0',46,NULL);SELECT@message;任務(wù)2

觸發(fā)器觸發(fā)器是因事件被觸發(fā)而執(zhí)行的。本例中，先向?qū)W生表xs中插入一條記錄，觸發(fā)器xs_AFTER_INSERT將被觸發(fā)并激活執(zhí)行，然后查看變量@message的值。MySQL提供了兩個(gè)邏輯表NEW和OLD。NEW和OLD表的結(jié)構(gòu)與觸發(fā)器所在數(shù)據(jù)表的結(jié)構(gòu)完全一致，當(dāng)觸發(fā)器執(zhí)行完成之后，這兩個(gè)表也會(huì)被自動(dòng)刪除。對于UPDATE事件，OLD表中存放的是更新前的記錄，NEW表用來存放更新后的記錄。對于INSERT事件，OLD表沒有記錄，NEW表中存放的是要插入的記錄。對于DELETE事件，OLD表中存放的是被刪除的記錄，NEW表中沒有記錄。任務(wù)2

觸發(fā)器【例題10.9】創(chuàng)建觸發(fā)器xs_BEFORE_DELETE，其功能是當(dāng)刪除學(xué)生表xs中某個(gè)學(xué)生的記錄時(shí)，成績表中該學(xué)生的相關(guān)成績信息也一并被刪除。實(shí)現(xiàn)代碼如下。DELIMITER//CREATETRIGGERxs_BEFORE_DELETEBEFOREDELETEONxsFOREACHROWBEGINDELETEFROMcjWHERE學(xué)號(hào)=old.學(xué)號(hào);END//DELIMITER;先向成績表cj中插入一條記錄，執(zhí)行如下語句。INSERTINTOcj(學(xué)號(hào),課程號(hào),成績)VALUES('001112','206,86);然后，刪除學(xué)生表中學(xué)號(hào)為“001112”的學(xué)生記錄，執(zhí)行如下語句。DELETEFROMxsWHERE學(xué)號(hào)='001112';此時(shí)，觸發(fā)器xs_AFTER_INSERT將被觸發(fā)并激活執(zhí)行，成績表中學(xué)號(hào)“001112”對應(yīng)的所有成績?nèi)勘粍h除，通過SELECT語句進(jìn)行檢查，如下所示。SELECT*FROMcj;任務(wù)2

觸發(fā)器10.2.3管理觸發(fā)器1．查看觸發(fā)器查看觸發(fā)器是指查看數(shù)據(jù)庫中已經(jīng)存在的觸發(fā)器的定義、狀態(tài)和語法信息等。（1）用SHOWTRIGGERS語句查看觸發(fā)器信息。在MySQL中，可以用SHOWTRIGGERS語句來查看觸發(fā)器的基本信息，其語法格式如下。SHOWTRIGGERS[FROM數(shù)據(jù)庫名][LIKE區(qū)配模式|WHERE條件表達(dá)式];【例題10.10】查看當(dāng)前數(shù)據(jù)庫中創(chuàng)建的所有觸發(fā)器的信息。執(zhí)行如下語句。SHOWTRIGGERS;（2）在triggers表中查看觸發(fā)器信息。在MySQL中，所有觸發(fā)器的信息都存在information_schema數(shù)據(jù)庫的triggers數(shù)據(jù)表中，可以使用SELECT語句直接從該表中查看指定觸發(fā)器或所有觸發(fā)器的信息，具體的語法格式如下。SELECT*FROMinformation_schema.triggersWHEREtrigger_name='觸發(fā)器名';任務(wù)2

觸發(fā)器【例題10.11】查看觸發(fā)器xs_BEFORE_DELETE的信息。執(zhí)行如下語句。SELECT*FROMinformation_schema.triggersWHEREtrigger_name='xs_BEFORE_DELETE'\G也可以查看所有觸發(fā)器的信息。執(zhí)行如下語句。SELECT*FROMinformation_schema.triggers\G2．修改觸發(fā)器觸發(fā)器的定義不可修改，如果要修改觸發(fā)器，可以先刪除原觸發(fā)器，再以相同的名稱創(chuàng)建新的觸發(fā)器。任務(wù)2

觸發(fā)器3．刪除觸發(fā)器可以使用DROPTRIGGER語句將觸發(fā)器從數(shù)據(jù)庫中刪除，其語法格式如下。DROPTRIGGER[IFEXISTS]觸發(fā)器名;執(zhí)行DROPTRIGGER語句需要SUPER權(quán)限。【例題10.12】刪除名為xs_BEFORE_DELETE的觸發(fā)器。執(zhí)行如下語句。DROPTRIGGERxs_BEFORE_DELETE;注意：在刪除表時(shí)，表中的觸發(fā)器也一并刪除。任務(wù)2

觸發(fā)器10.2.4使用MySQLWorkbench圖形化工具創(chuàng)建和管理觸發(fā)器

1．創(chuàng)建觸發(fā)器打開MySQLWorkbench，在SCHEMAS欄中單擊cjgl數(shù)據(jù)庫，右擊xs表，在彈出式菜單中選擇修改表結(jié)構(gòu)的菜單，打開修改表結(jié)構(gòu)的窗口，如圖10-6所示。單擊下方的Triggers選項(xiàng)卡，打開圖10-7所示的創(chuàng)建觸發(fā)器窗口。任務(wù)2

觸發(fā)器單擊左側(cè)BEFOREDELETE行后面的加號(hào)會(huì)生成觸發(fā)器名xs_BEFORE