MySQL教程（第4版） 課件 第7章 過(guò)程式數(shù)據(jù)庫(kù)對(duì)象

第7章

過(guò)程式數(shù)據(jù)庫(kù)對(duì)象——存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程使用存儲(chǔ)過(guò)程的優(yōu)點(diǎn)如下。（1）存儲(chǔ)過(guò)程在服務(wù)器端運(yùn)行，執(zhí)行速度快。（2）存儲(chǔ)過(guò)程執(zhí)行一次后，其執(zhí)行規(guī)劃就駐留在高速緩沖存儲(chǔ)器中，在以后的操作中，只需從高速緩沖存儲(chǔ)器中調(diào)用已編譯好的二進(jìn)制代碼即可，提高了系統(tǒng)性能。（3）確保數(shù)據(jù)庫(kù)安全。使用存儲(chǔ)過(guò)程可以完成所有數(shù)據(jù)庫(kù)操作，并可以通過(guò)編程方式控制上述操作對(duì)數(shù)據(jù)庫(kù)信息進(jìn)行訪問(wèn)的權(quán)限。01創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程主體存儲(chǔ)過(guò)程調(diào)用存儲(chǔ)過(guò)程顯示存儲(chǔ)過(guò)程的修改刪除存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程1.創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)句如下。CREATEPROCEDURE存儲(chǔ)過(guò)程名([參數(shù),...])[特征...] [存儲(chǔ)過(guò)程主體]存儲(chǔ)過(guò)程名：默認(rèn)在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建存儲(chǔ)過(guò)程。需要在特定數(shù)據(jù)庫(kù)中創(chuàng)建存儲(chǔ)過(guò)程時(shí)，則在名稱前面加上數(shù)據(jù)庫(kù)的名稱，格式為“數(shù)據(jù)庫(kù)名.存儲(chǔ)過(guò)程名”。參數(shù)：參數(shù)名后為參數(shù)的類(lèi)型，當(dāng)有多個(gè)參數(shù)時(shí)中間用逗號(hào)隔開(kāi)。存儲(chǔ)過(guò)程可以有0個(gè)、1個(gè)或多個(gè)參數(shù)。MySQL存儲(chǔ)過(guò)程支持3種類(lèi)型的參數(shù)：[IN|OUT|INOUT]參數(shù)名typeIN、OUT和INOUT分別是輸入?yún)?shù)、輸出參數(shù)和輸入/輸出參數(shù)關(guān)鍵字。特征：LANGUAGESQL|[NOT]DETERMINISTIC|{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}|COMMENT'注釋'創(chuàng)建存儲(chǔ)過(guò)程2.存儲(chǔ)過(guò)程主體存儲(chǔ)過(guò)程主體包含了在過(guò)程調(diào)用的時(shí)候必須執(zhí)行的若干個(gè)語(yǔ)句，這個(gè)部分總是以BEGIN開(kāi)始，以END結(jié)束。當(dāng)然，當(dāng)存儲(chǔ)過(guò)程主體中只有一個(gè)SQL語(yǔ)句時(shí)可以省略BEGIN-END標(biāo)志。在MySQL5.7中，服務(wù)器處理語(yǔ)句的時(shí)候是以分號(hào)為結(jié)束標(biāo)志的。但是在創(chuàng)建存儲(chǔ)過(guò)程的時(shí)候，存儲(chǔ)過(guò)程主體中可能包含多個(gè)SQL語(yǔ)句，每個(gè)SQL語(yǔ)句都是以分號(hào)為結(jié)尾的，服務(wù)器處理程序時(shí)遇到第一個(gè)分號(hào)就會(huì)認(rèn)為程序結(jié)束，這肯定是不行的。所以使用DELIMITER命令將MySQL語(yǔ)句的結(jié)束標(biāo)志臨時(shí)修改為其他符號(hào)，例如：DELIMITER$$說(shuō)明：這里$$是用戶定義的結(jié)束符，通常這個(gè)符號(hào)可以是一些特殊的符號(hào)，如兩個(gè)“#”、兩個(gè)“￥”等。當(dāng)使用DELIMITER命令時(shí)，應(yīng)該避免使用反斜杠（“\”）字符，因?yàn)樗荕ySQL的轉(zhuǎn)義字符。但Navicat中也可以不進(jìn)行設(shè)置?？梢杂孟铝姓Z(yǔ)句檢驗(yàn)一下：DELIMITER//USExscj//SELECT姓名FROMxsWHERE學(xué)號(hào)='221101'//DELIMITER;創(chuàng)建存儲(chǔ)過(guò)程【例】創(chuàng)建存儲(chǔ)過(guò)程，刪除一個(gè)指定學(xué)生記錄。USExscj;DELIMITER$$CREATEPROCEDUREdelete_xs(INxhchar(6))BEGIN DELETEFROMxs1WHERE學(xué)號(hào)=xh; SELECT*FROMxs;END$$DELIMITER;創(chuàng)建存儲(chǔ)過(guò)程3.存儲(chǔ)過(guò)程調(diào)用存儲(chǔ)過(guò)程創(chuàng)建完后，可以在命令窗口、觸發(fā)器或者其他存儲(chǔ)過(guò)程中調(diào)用。CALL存儲(chǔ)過(guò)程名([參數(shù),...])說(shuō)明：（1）如果要調(diào)用非當(dāng)前數(shù)據(jù)庫(kù)的存儲(chǔ)過(guò)程，則需要在存儲(chǔ)過(guò)程名前面加上該數(shù)據(jù)庫(kù)的名稱。（2）這條語(yǔ)句中的參數(shù)個(gè)數(shù)必須總是等于定義該存儲(chǔ)過(guò)程的參數(shù)個(gè)數(shù)。例如：CALLdelete_xs('221101');創(chuàng)建存儲(chǔ)過(guò)程4.存儲(chǔ)過(guò)程顯示（1）查看數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程的具體信息使用語(yǔ)句：SHOWCREATEPROCEDURE存儲(chǔ)過(guò)程名例如：USExscj;SHOWCREATEPROCEDUREdelete_student;顯示結(jié)果如圖。（2）查看所有存儲(chǔ)過(guò)程的狀態(tài)，包括系統(tǒng)存儲(chǔ)過(guò)程和用戶定義的存儲(chǔ)過(guò)程：SHOWPROCEDURESTATUS創(chuàng)建存儲(chǔ)過(guò)程5.存儲(chǔ)過(guò)程的修改ALTERPROCEDURE存儲(chǔ)過(guò)程名[特征...]其中，特征：{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}|COMMENT'注釋內(nèi)容'6.刪除存儲(chǔ)過(guò)程DROPPROCEDURE[IFEXISTS]存儲(chǔ)過(guò)程名例如，刪除存儲(chǔ)過(guò)程delete_xs：USExscj;DROPPROCEDUREdelete_xs;02存儲(chǔ)過(guò)程體局部變量分支語(yǔ)句循環(huán)語(yǔ)句存儲(chǔ)過(guò)程體1．局部變量（1）聲明局部變量要聲明局部變量必須使用DECLARE語(yǔ)句。在聲明局部變量的同時(shí)也可以對(duì)其賦一個(gè)初始值。DECLARE變量名[,...]數(shù)據(jù)類(lèi)型[DEFAULT值]說(shuō)明：DEFAULT子句給變量指定一個(gè)默認(rèn)值，如果不指定則默認(rèn)為NULL。例如，聲明一個(gè)整型變量和兩個(gè)字符變量：DECLAREnumint(4);DECLAREstr1,str2varchar(6);局部變量必須在存儲(chǔ)過(guò)程的開(kāi)頭就聲明，聲明完后，可以在聲明它的BEGIN…END語(yǔ)句塊中使用該變量，而其他語(yǔ)句塊中不可以使用。（2）使用SET語(yǔ)句給變量賦值要給局部變量賦值可以使用SET語(yǔ)句，它也是SQL本身的一部分。SET變量名=表達(dá)式,...例如，在存儲(chǔ)過(guò)程中給局部變量賦值：SETnum=1,str1='HELLO';存儲(chǔ)過(guò)程體（3）SELECT...INTO語(yǔ)句給變量賦值使用SELECT…INTO語(yǔ)句可以把選定的列值直接存儲(chǔ)到變量中。因此，返回的結(jié)果只能有一行。SELECT列名,...INTO變量名,...FROM表名,...【例】通過(guò)變量顯示xs表中指定學(xué)號(hào)學(xué)生姓名和專(zhuān)業(yè)名的值（分別賦給變量）。DELIMITER$$CREATEPROCEDURExscj.xs_vdisp (INxhchar(6))BEGINDECLAREname,projectvarchar(10);SELECT姓名,專(zhuān)業(yè)INTOname,project FROMxs WHERE學(xué)號(hào)=xh;SELECTname,project;END$$DELIMITER;

CALLxscj.xs_vdisp('221101’);顯示結(jié)果如圖。存儲(chǔ)過(guò)程體2.分支語(yǔ)句（1）IF語(yǔ)句IF語(yǔ)句可根據(jù)不同的條件執(zhí)行不同的操作。IF條件1THEN語(yǔ)句序列1[ELSEIF條件2THEN語(yǔ)句序列2]...[ELSE語(yǔ)句序列0]ENDIF存儲(chǔ)過(guò)程體【例】創(chuàng)建存儲(chǔ)過(guò)程，比較第1個(gè)參數(shù)和第2個(gè)參數(shù)整數(shù)大小，顯示比較結(jié)果。DELIMITER$$CREATEPROCEDUREComp (INk1integer,INk2integer,OUTk3char(6))BEGIN IFk1>k2THEN SETk3='大于'; ELSEIFk1=k2THEN SETk3='等于'; ELSE SETk3='小于'; ENDIF;END$$DELIMITER;

CALLComp(12,46,@k3);SELECT@k3;顯示結(jié)果如圖。存儲(chǔ)過(guò)程體（2）CASE語(yǔ)句前面在介紹SELECT選擇列時(shí)涉及的CASE輸出項(xiàng)變換不是語(yǔ)句，這里介紹CASE語(yǔ)句在存儲(chǔ)過(guò)程中的用法，與之前略有不同。CASE表達(dá)式 WHEN值1 THEN語(yǔ)句序列1 [WHEN值2THEN語(yǔ)句序列2] ... [ELSE語(yǔ)句序列0]ENDCASE或者CASE WHEN條件1THEN語(yǔ)句序列1 [WHEN條件2THEN語(yǔ)句序列2] ... [ELSE語(yǔ)句序列0]ENDCASE存儲(chǔ)過(guò)程體【例】創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，把百分制變成等級(jí)。DELIMITER$$CREATEPROCEDURExscj.cjGrade (INcjtinyint(1),OUTgradevarchar(4))BEGINCASE WHENcj>=90THENSETgrade='優(yōu)秀'; WHENcj>=80ANDcj<90

THENSETgrade='良好'; WHENcj>=70ANDcj<80

THENSETgrade='中等'; WHENcj>=60ANDcj<70

THENSETgrade='及格'; ELSESETgrade='不及格'; SELECTgrade;ENDCASE;END$$DELIMITER;

CALLxscj.cjGrade(85,@gcj);SELECT@gcj; #顯示“良好”存儲(chǔ)過(guò)程體3.循環(huán)語(yǔ)句MySQL支持3種循環(huán)語(yǔ)句：WHILE、REPEAT和LOOP語(yǔ)句。循環(huán)語(yǔ)句可以嵌套。（1）先判斷條件再執(zhí)行語(yǔ)句序列：WHILE語(yǔ)句WHILE條件DO

語(yǔ)句序列ENDWHILE存儲(chǔ)過(guò)程體【例】采用WHILE計(jì)算1+2+3+…+n。USExscj;DROPPROCEDUREIFEXISTSproc_nsum;CREATEPROCEDUREproc_nsum()BEGIN DECLAREn,sintDEFAULT0;

WHILEn<=@nDO SETs=s+n; SETn=n+1;

ENDWHILE; SELECT'1+2+3+…+',@n,'=',s;END;

SET@n=50;CALLproc_nsum();其中，n是局部變量，@n是用戶定義變量。運(yùn)行結(jié)果如圖。存儲(chǔ)過(guò)程體（2）先執(zhí)行語(yǔ)句序列再判斷條件：REPEAT語(yǔ)句REPEAT

語(yǔ)句序列UNTIL條件ENDREPEAT【例】采用REPEAT語(yǔ)句計(jì)算1+2+3+…+n。將本例的BEGIN…END替換為：BEGIN DECLAREn,sintDEFAULT0;

REPEAT SETn=n+1; SETs=s+n;

UNTILn>=@nENDREPEAT; SELECT'1+2+3+…+',@n,'=',s;END;存儲(chǔ)過(guò)程體（3）通過(guò)語(yǔ)句體控制循環(huán)結(jié)束：LOOP和LEAVE標(biāo)簽或者ITERATE語(yǔ)句[標(biāo)簽:]LOOP

語(yǔ)句序列 LEAVE標(biāo)簽|ITERATE語(yǔ)句 ...ENDLOOP[標(biāo)簽]說(shuō)明：語(yǔ)句序列是需要重復(fù)執(zhí)行的語(yǔ)句。執(zhí)行到“LEAVE標(biāo)簽”語(yǔ)句終止循環(huán)，跳轉(zhuǎn)到ENDLOOP[標(biāo)簽]語(yǔ)句的下一個(gè)語(yǔ)句。存儲(chǔ)過(guò)程體【例】采用LOOP語(yǔ)句計(jì)算1+2+3+…+n。將本例的BEGIN…END替換為：BEGIN DECLAREn,sintDEFAULT0; SETn=0;

mylabel:LOOP SETs=s+n; IFn>=@nTHEN

LEAVEmylabel; ENDIF; SETn=n+1;

ENDLOOPmylabel; SELECT'1+2+3+…+',@n,'=',s;END;03錯(cuò)誤自動(dòng)處理?xiàng)l件名稱定義條件處理定義錯(cuò)誤自動(dòng)處理每一個(gè)錯(cuò)誤都有一個(gè)唯一錯(cuò)誤代碼和一個(gè)SQLSTATE代碼，例如，SQLSTATE23000屬于如下的錯(cuò)誤代碼：Error1022,"Can'twrite;duplicatekeyintable"Error1048,"Columncannotbenull"Error1052,"Columnisambiguous"Error1062,"Duplicateentryforkey"MySQL手冊(cè)中“錯(cuò)誤消息和代碼”列出了所有可能的錯(cuò)誤消息及它們各自的代碼。1．條件名稱定義DECLARE條件名稱CONDITIONFOR條件值其中：條件值：ERROR=MySQL錯(cuò)誤代碼|SQLSTATE[VALUE]SQL狀態(tài)值條件名稱定義不是必須的。錯(cuò)誤自動(dòng)處理2．條件處理定義DECLARE處理動(dòng)作HANDLERFOR條件值,...處理程序處理動(dòng)作：CONTINUE|EXIT|UNDOCONTINUE不中斷程序的處理；EXIT對(duì)當(dāng)前BEGIN...END復(fù)合語(yǔ)句的執(zhí)行被終止；UNDO撤銷(xiāo)之前的操作，但目前MySQL暫不支持撤銷(xiāo)操作。條件值：錯(cuò)誤代碼|SQLSTATE[VALUE]sql狀態(tài)碼|條件名稱|SQLWARNING|NOTFOUND|SQLEXCEPTION處理程序可以根據(jù)不同錯(cuò)誤定義不同的處理程序，或者采用統(tǒng)一的程序，甚至一個(gè)語(yǔ)句。錯(cuò)誤自動(dòng)處理【例】數(shù)據(jù)庫(kù)中不存在的表錯(cuò)誤處理程序測(cè)試。（1）在MySQL命令行窗口操作，系統(tǒng)會(huì)顯示出錯(cuò)信息，如圖。錯(cuò)誤自動(dòng)處理（2）在Navicat創(chuàng)建存儲(chǔ)過(guò)程執(zhí)行查詢操作，不加控制時(shí)顯示出錯(cuò)信息。USExscj;DROPPROCEDUREIFEXISTSproc_test;CREATEPROCEDUREPROC_test()BEGIN DESCxstest;END;

SET@ERR=0;CALLproc_test();SELECT@ERRAS'CALL后';調(diào)用存儲(chǔ)過(guò)程后，系統(tǒng)顯示錯(cuò)誤信息如圖。錯(cuò)誤自動(dòng)處理（3）若在存儲(chǔ)過(guò)程中控制該錯(cuò)誤，僅用一個(gè)語(yǔ)句對(duì)其進(jìn)行處理。USExscj;DROPPROCEDUREIFEXISTSproc_test;CREATEPROCEDUREPROC_test()BEGIN DECLARECONTINUEHANDLERFOR1146 SET@ERR=1; DESCxstest;END;

SET@ERR=0;CALLproc_test();SELECT@ERRAS'CALL后';調(diào)用存儲(chǔ)過(guò)程后，系統(tǒng)顯示如圖。錯(cuò)誤自動(dòng)處理（4）若在存儲(chǔ)過(guò)程中錯(cuò)誤控制需要使用多個(gè)語(yǔ)句進(jìn)行處理如下：USExscj;DROPPROCEDUREIFEXISTSproc_test;CREATEPROCEDUREPROC_test()BEGIN DECLARECONTINUEHANDLERFORSQLSTATE'42S02' BEGIN SET@ERR=1; SELECT'文件不存在！'AS出錯(cuò)信息; #（a） END; SET@ERR=2; DESCxstest; SELECT@ERRAS'DESC后'; #（b） SET@ERR=3;END;

SET@ERR=0;CALLproc_test();SELECT@ERRAS'CALL后'; #（c）調(diào)用存儲(chǔ)過(guò)程后，系統(tǒng)顯示如圖。

錯(cuò)誤自動(dòng)處理（5）修改第（4）步代碼中的語(yǔ)句，將CONTINUE改成EXIT：DECLAREEXITHANDLERFORSQLSTATE'42S02'運(yùn)行結(jié)果變成如圖。第7章

過(guò)程式數(shù)據(jù)庫(kù)對(duì)象——存儲(chǔ)函數(shù)存儲(chǔ)函數(shù)1.創(chuàng)建存儲(chǔ)函數(shù)CREATEFUNCTION存儲(chǔ)函數(shù)名([參數(shù),...]) RETURNS數(shù)據(jù)類(lèi)型 [特征...]

存儲(chǔ)函數(shù)的主體說(shuō)明：存儲(chǔ)函數(shù)的主體包含下列語(yǔ)句：RETURN表達(dá)式;其中，表達(dá)式的值與定義的RETURNS后的數(shù)據(jù)類(lèi)型相同。2.存儲(chǔ)函數(shù)修改ALTERFUNCTION存儲(chǔ)函數(shù)名[特征...]3.存儲(chǔ)函數(shù)刪除DROPFUNCTION[IFEXISTS]存儲(chǔ)函數(shù)名存儲(chǔ)函數(shù)【例】創(chuàng)建一個(gè)存儲(chǔ)函數(shù)，把百分制變成等級(jí)。USExscj;DROPFUNCTIONIFEXISTSxscj.cjGradeFun;DELIMITER$$CREATEFUNCTIONxscj.cjGradeFun(cjtinyint(1))RETURNSchar(3)BEGIN DECLAREgradechar(3); CASE WHENcj>=90THENSETgrade='優(yōu)秀'; WHENcj>=80ANDcj<90THENSETgrade='良好'; WHENcj>=70ANDcj<80THENSETgrade='中等'; WHENcj>=60ANDcj<70THENSETgrade='及格'; ELSESETgrade='不及格'; ENDCASE; RETURNgrade;END$$DELIMITER;

SET@cj=85;SELECT@cj,xscj.cjGradeFun(@cj);執(zhí)行結(jié)果如圖。第7章

過(guò)程式數(shù)據(jù)庫(kù)對(duì)象——游

標(biāo)游

標(biāo)1.聲明游標(biāo)DECLARE游標(biāo)名CURSORFORSELECT語(yǔ)句說(shuō)明：（1）游標(biāo)名使用與表名同樣的規(guī)則。（2）SELECT語(yǔ)句返回的是一行或多行數(shù)據(jù)。（2）在存儲(chǔ)過(guò)程中定義多個(gè)游標(biāo)，但是一個(gè)塊中的每一個(gè)游標(biāo)都必須有唯一的名字。（4）SELECT語(yǔ)句不能有INTO子句。例如：DECLARExs_cur1CURSOR FOR SELECT學(xué)號(hào),姓名,性別,出生日期,總學(xué)分 FROMxscj WHERE專(zhuān)業(yè)='計(jì)算機(jī)';游

標(biāo)2.打開(kāi)游標(biāo)聲明游標(biāo)后，想要使用游標(biāo)從中提取數(shù)據(jù)，就必須先打開(kāi)游標(biāo)。OPEN游標(biāo)名在程序中，一個(gè)游標(biāo)可以打開(kāi)多次，由于其他的用戶或程序可能已經(jīng)更新了表，所以每次打開(kāi)結(jié)果可能不同。3.讀取數(shù)據(jù)游標(biāo)打開(kāi)后，就可以從中讀取數(shù)據(jù)。FETCH游標(biāo)名INTO變量名,...說(shuō)明：FETCH...INTO語(yǔ)句與SELECT...INTO語(yǔ)句具有相同的意義，F(xiàn)ETCH語(yǔ)句是將游標(biāo)指向的一行數(shù)據(jù)賦給一些變量，子句中變量的數(shù)目必須等于聲明游標(biāo)時(shí)SELECT子句中列的數(shù)目。4.關(guān)閉游標(biāo)游標(biāo)使用完以后要及時(shí)關(guān)閉。關(guān)閉游標(biāo)使用CLOSE語(yǔ)句。CLOSE游標(biāo)名例如：CLOSExs_cur2游

標(biāo)【例】創(chuàng)建一個(gè)包含游標(biāo)的存儲(chǔ)過(guò)程，統(tǒng)計(jì)xs表中指定班級(jí)人數(shù)和獲得最大學(xué)號(hào)。USExscj;DELIMITER$$DROPPROCEDUREIFEXISTSclass;CREATEPROCEDUREclass(INbjchar(4),OUTnumberinteger,OUTxhmaxchar(6))BEGIN DECLARExhchar(6); DECLAREfoundbitDEFAULTTRUE; DECLAREnumber_xsCURSORFOR SELECT學(xué)號(hào)FROMxsWHERELEFT(學(xué)號(hào),4)=bjORDERBY學(xué)號(hào); DECLARECONTINUEHANDLERFORNOTFOUND SETfound=false; SETnumber=0; OPENnumber_xs; FETCHnumber_xsINTOxh; WHILEfoundDO SETnumber=number+1;SETxhmax=xh; FETCHnumber_xsINTOxh; ENDWHILE; CLOSEnumber_xs;END$$DELIMITER;游

標(biāo)調(diào)用此存儲(chǔ)過(guò)程并查看結(jié)果：SET@bj='2211';CALLxscj.class('2211',@num,@xh);SELECT@bj班級(jí),@num人數(shù),@xh最大學(xué)號(hào);執(zhí)行結(jié)果如圖。第7章

過(guò)程式數(shù)據(jù)庫(kù)對(duì)象——觸

發(fā)

器觸

發(fā)

器1.創(chuàng)建觸發(fā)器CREATETRIGGER觸發(fā)器名觸發(fā)時(shí)刻觸發(fā)事件ON表名FOREACHROW觸發(fā)器動(dòng)作說(shuō)明：觸發(fā)器名：觸發(fā)器在當(dāng)前數(shù)據(jù)庫(kù)中必須具有唯一的名稱。觸發(fā)時(shí)刻：有AFTER和BEFORE兩個(gè)選項(xiàng)。觸發(fā)事件：指明激活觸發(fā)程序的語(yǔ)句類(lèi)型。表名：只有在該表上發(fā)生觸發(fā)事件才會(huì)激活觸發(fā)器。FOREACHROW：這個(gè)聲明用來(lái)指定對(duì)于受觸發(fā)事件影響的每一行，都要激活觸發(fā)器的動(dòng)作。觸發(fā)器動(dòng)作：包含觸發(fā)器激活時(shí)將要執(zhí)行的語(yǔ)句。觸

發(fā)

器2.查看當(dāng)前數(shù)據(jù)庫(kù)中觸發(fā)器SHOWTRIGGERS3.刪除觸發(fā)器DROPTRIGGERIFEXISTS觸發(fā)器名【例】創(chuàng)建觸發(fā)器，當(dāng)向cj表插入一行數(shù)據(jù)時(shí)，根據(jù)成績(jī)對(duì)xs表的總學(xué)分進(jìn)行修改。如果成績(jī)≥60分，則總學(xué)分加上該課程的學(xué)分，否則總學(xué)分不變。（1）創(chuàng)建觸發(fā)器USExscj;DROPTRIGGERIFEXISTScj_insert;DELIMITER$$CREATETRIGGERcj_insertAFTERINSERT ONcjFOREACHROWBEGIN DECLARExfint(1); SELECT學(xué)分INTOxfFROMkcWHERE課程號(hào)=NEW.課程號(hào); IFnew.成績(jī)>=60THEN UPDATExsSET總學(xué)分=總學(xué)分+xfWHERE學(xué)號(hào)=NEW.學(xué)號(hào); ENDIF;END$$DELIMITER;觸

發(fā)

器（2）驗(yàn)證觸發(fā)器功能USExscj;INSERTINTOxs VALUES('221199','王大慶','計(jì)算機(jī)',1,'2004-08-14',16,NULL,NULL);SELECT學(xué)號(hào),姓名,總學(xué)分FROMxsWHERE學(xué)號(hào)='221199'; #（a）INSERTINTOcj(學(xué)號(hào),課程號(hào),成績(jī))VALUES('221199','206',60);SELECT學(xué)號(hào),姓名,總學(xué)分FROMxsWHERE學(xué)號(hào)='221199'; #（b）執(zhí)行結(jié)果如圖。

觸

發(fā)

器【例】創(chuàng)建一個(gè)觸發(fā)器，當(dāng)修改cj表中數(shù)據(jù)時(shí)，根據(jù)修改前后成績(jī)是否大于等于60，調(diào)整xs表總學(xué)分。（1）創(chuàng)建一個(gè)觸發(fā)器USExscj;DROPTRIGGERIFEXISTScj_update;DELIMITER$$CREATETRIGGERcj_updateBEFOREUPDATE ONcjFOREACHROWBEGIN DECLARExfint(1); SELECT學(xué)分INTOxfFROMkcWHERE課程號(hào)=NEW.課程號(hào); IFOLD.成績(jī)>=60ANDNEW.成績(jī)<60THEN UPDATExsSET總學(xué)分=總學(xué)分-xfWHERE學(xué)號(hào)=OLD.學(xué)號(hào); ENDIF; IFOLD.成績(jī)<60ANDNEW.成績(jī)>=60THEN UPDATExsSET總學(xué)分=總學(xué)分+xfWHERE學(xué)號(hào)=OLD.學(xué)號(hào); ENDIF; END$$DELIMITER;觸

發(fā)

器（2）驗(yàn)證觸發(fā)器功能USExscj;UPDATEcjSET成績(jī)=50WHERE學(xué)號(hào)='221199';SELECT學(xué)號(hào),姓名,總學(xué)分FROMxsWHERE學(xué)號(hào)='221199';執(zhí)行結(jié)果如圖。觸

發(fā)

器【例】創(chuàng)建一個(gè)觸發(fā)器，當(dāng)刪除表xs中某個(gè)學(xué)生的信息時(shí)，同時(shí)將cj表中與該學(xué)生有關(guān)的記錄全部刪除。（1）創(chuàng)建觸發(fā)器USExscj;DROPTRIGGERIFEXISTSxs_delete;DELIMITER$$CREATETRIGGERxs_deleteAFTERDELETE ONxsFOREACHROWBEGIN DELETEFROMcjWHERE學(xué)號(hào)=OLD.學(xué)號(hào);END$$DELIMITER;（2）驗(yàn)證一下觸發(fā)器的功能：刪除xs表學(xué)號(hào)為'221199'學(xué)生記錄。SELECT*FROMcjWHERE學(xué)號(hào)='221199'; #（a）DELETEFROMxsWHERE學(xué)號(hào)='221199';SELECT*FROMcjWHERE學(xué)號(hào)='221199'; #（b）執(zhí)行結(jié)果如圖。

第7章

過(guò)程式數(shù)據(jù)庫(kù)對(duì)象——事

件01創(chuàng)

建

事

件創(chuàng)建事件創(chuàng)建事件的語(yǔ)句如下。CREATEEVENT[IFNOTEXISTS]事件名 ONSCHEDULE時(shí)間描述 [ONCOMPLETION[NOT]PRESERVE] [ENABLE|DISABLE|DISABLEONSLAVE] [COMMENT'注釋內(nèi)容'] DOSQL語(yǔ)句;其中，時(shí)間描述：AT時(shí)間點(diǎn)[+INTERVAL時(shí)間間隔]|EVERY時(shí)間間隔[STARTS時(shí)間點(diǎn)[+INTERVAL時(shí)間間隔]][ENDS時(shí)間點(diǎn)[+INTERVAL時(shí)間間隔]]時(shí)間間隔：數(shù)值{YEAR|QUARTER|MONTH|DAY|HOUR|MINUTE|WEEK|SECOND|YEAR_MONTH|DAY_HOUR|DAY_MINUTE|DAY_SECOND|HOUR_MINUTE|HOUR_SECOND|MINUTE_SECOND}創(chuàng)建事件MySQL事件調(diào)度器負(fù)責(zé)調(diào)用事件。要?jiǎng)?chuàng)建事件，必須打開(kāi)調(diào)度器?？梢允褂孟到y(tǒng)變量EVENT_SCHEDULER來(lái)打開(kāi)調(diào)度器，TRUE為打開(kāi)，F(xiàn)ALSE為關(guān)閉：SETGLOBALEVENT_SCHEDULER=TRUE;【例】2022年11月11日0時(shí)0分所有商品單價(jià)下調(diào)10%。（1）創(chuàng)建test數(shù)據(jù)庫(kù)商品表sp：USEtest;CREATETABLEsp( id int(2)NOTNULL, price float(6.2));INSERTINTOsp VALUES(1,20.50),(2,68.00);SELECT*FROMsp;顯示結(jié)果如圖。創(chuàng)建事件（2）創(chuàng)建事件USEtest;DROPEVENTIFEXISTSchangePrice;CREATEEVENTchangePrice ONSCHEDULEAT'2022-11-11' DO #在商品數(shù)據(jù)庫(kù)中進(jìn)行調(diào)整價(jià)格的操作 updatetest.spSETsp.price=ROUND(0.9*sp.price);SETGLOBALEVENT_SCHEDULER=TRUE;（3）測(cè)試事件Windows10及以上版本具有聯(lián)網(wǎng)同步系統(tǒng)時(shí)間的功能，如果臨時(shí)斷開(kāi)網(wǎng)絡(luò)修改系統(tǒng)日期和時(shí)間，事件就不會(huì)啟動(dòng)。所以為了測(cè)試事件功能，可以臨時(shí)修改事件啟動(dòng)時(shí)間點(diǎn)。例如，修改為ATnow()+INTERVAL10SECOND，重新創(chuàng)建事件，在10秒鐘后運(yùn)行下列語(yǔ)句測(cè)試：USEtest;SELECT*FROMsp;顯示結(jié)果如圖。創(chuàng)建事件【例】從2022年開(kāi)始，每年9月30日將已經(jīng)畢業(yè)的學(xué)生表和成績(jī)表記錄轉(zhuǎn)移到歷史數(shù)據(jù)庫(kù)中，至2030年結(jié)束。（1）創(chuàng)建與學(xué)生（xs）表和成績(jī)（cj）表相同結(jié)構(gòu)的歷史表xs_ls和cj_ls。USExscj;CREATETABLExs_lsLIKExs;CREATETABLEcj_lsLIKEcj;（2）準(zhǔn)備學(xué)生（xs）表和成績(jī)（cj）表拷貝，將進(jìn)校最早的學(xué)生（學(xué)號(hào)'20xxxx'）根據(jù)當(dāng)前系統(tǒng)時(shí)間，修改為畢業(yè)班學(xué)生學(xué)號(hào)，這樣既可以測(cè)試事件，又不破壞原表數(shù)據(jù)記錄。USExscj;CREATETABLExs_copyAS(SELECT*FROMxs);CREATETABLEcj_copyAS(SELECT*FROMcj);UPDATExs_copySET學(xué)號(hào)=CONCAT(@byyear,SUBSTR(學(xué)號(hào),3,4))WHERELEFT(學(xué)號(hào),2)='20';UPDATEcj_copySET學(xué)號(hào)=CONCAT(@byyear,SUBSTR(學(xué)號(hào),3,4))WHERELEFT(學(xué)號(hào),2)='20';創(chuàng)建事件（3）創(chuàng)建事件調(diào)用的存儲(chǔ)過(guò)程，實(shí)現(xiàn)事件功能。USExscj;DELIMITER$$DROPPROCEDUREIFEXISTSxscj.xscj_lsp;CREATE