《數(shù)據(jù)庫原理及應(yīng)用》數(shù)據(jù)庫虛擬教研室制作數(shù)據(jù)庫目錄01

數(shù)據(jù)庫編程概述02MySQL編程基本要素03存儲(chǔ)過程04存儲(chǔ)函數(shù)05觸發(fā)器06事件第8講MySQL編程數(shù)據(jù)庫編程概述SQL是關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)語言，它雖然提供了數(shù)據(jù)定義、數(shù)據(jù)操作和數(shù)據(jù)控制功能，但不支持過程化編程，不能完成復(fù)雜的應(yīng)用需求。如編程實(shí)現(xiàn)輸入某學(xué)生姓名查詢?cè)搶W(xué)生的信息；如編程實(shí)現(xiàn)根據(jù)學(xué)生的學(xué)號(hào)，統(tǒng)計(jì)學(xué)生的總學(xué)分等。

為什么需要學(xué)習(xí)數(shù)據(jù)庫編程？

面對(duì)復(fù)雜的應(yīng)用需求，需要DBMS提供一種過程化的編程方式。數(shù)據(jù)庫編程概述Oracle的PL/SQLSQLServer的Transact-SQLMySQL的數(shù)據(jù)庫編程達(dá)夢(mèng)數(shù)據(jù)庫（DMDatabase）和人大金倉數(shù)據(jù)庫（KingbaseES）的數(shù)據(jù)庫編程類似Oracle的PL/SQL風(fēng)格阿里云數(shù)據(jù)庫PolarDBMySQL版、RDSforMySQL完全兼容MySQL的過程化編程語言華為云數(shù)據(jù)庫GaussDB(forMySQL)和RDSforMySQL支持所有標(biāo)準(zhǔn)的MySQL過程化編程功能

不同數(shù)據(jù)庫工具的結(jié)構(gòu)化編程：

注意：不同的數(shù)據(jù)庫工具提供的數(shù)據(jù)庫編程的語法差別較大，造成工具間可移植性較差。數(shù)據(jù)庫編程概述

MySQL提供了存儲(chǔ)過程、存儲(chǔ)函數(shù)、觸發(fā)器和事件等不同類型的存儲(chǔ)程序。

存儲(chǔ)過程和存儲(chǔ)函數(shù)由一組SQL語句和流程控制語句構(gòu)成，它們可以被應(yīng)用程序、觸發(fā)器或其他存儲(chǔ)過程調(diào)用，這樣可避免開發(fā)人員重復(fù)編寫SQL代碼，提高代碼的可復(fù)用性。存儲(chǔ)過程和存儲(chǔ)函數(shù)由系統(tǒng)進(jìn)行預(yù)編譯，在服務(wù)器中存儲(chǔ)和執(zhí)行，因此存儲(chǔ)過程和存儲(chǔ)函數(shù)具有執(zhí)行速度快、性價(jià)比高、安全性高等特點(diǎn)。觸發(fā)器和事件是與數(shù)據(jù)表操作相關(guān)的特殊類型的存儲(chǔ)過程。觸發(fā)器是滿足一定條件自動(dòng)觸發(fā)執(zhí)行的數(shù)據(jù)庫對(duì)象，例如，對(duì)數(shù)據(jù)表更新記錄時(shí)，更新觸發(fā)器被系統(tǒng)自動(dòng)觸發(fā)執(zhí)行。事件是基于特定時(shí)刻或時(shí)間間隔，進(jìn)行周期性調(diào)用的數(shù)據(jù)庫對(duì)象，例如，在某一時(shí)刻定期激活事件向數(shù)據(jù)表中插入記錄。常量字符串常量：

是指用單引號(hào)或雙引號(hào)括起來的字符序列，如'中國'、"China!"。數(shù)值常量：分為整型常量和浮點(diǎn)常量，如123、3.14。日期時(shí)間型常量：按特定格式表示時(shí)間或日期信息的一種字符串。

日期型常量：包括年、月、日，如'2022-05-10'。

時(shí)間型常量：包括小時(shí)數(shù)、分鐘數(shù)、秒數(shù)及微秒數(shù)，如'03:28:45:00001'。

日期時(shí)間型常量：是日期和時(shí)間的組合，如'2022-05-1003:28:45'。布爾型常量：只包含兩個(gè)可能的值：TRUE和FALSE或0和1。NULL：是無類型的常量。變量MySQL支持3種類型的變量，分別為系統(tǒng)變量、用戶變量和局部變量。1.系統(tǒng)變量：在MySQL啟動(dòng)時(shí)由系統(tǒng)定義并初始化，默認(rèn)值可在系統(tǒng)配置文件my.ini或在命令行指定的選項(xiàng)中進(jìn)行更改。系統(tǒng)變量分為全局系統(tǒng)變量和會(huì)話系統(tǒng)變量。全局系統(tǒng)變量：影響服務(wù)器整體操作,

@@GLOBAL；會(huì)話系統(tǒng)變量：影響正在進(jìn)行的會(huì)話,

@@SESSION或@@。

例1：SET@@GLOBAL.auto_increment_increment=2;例2：SET@@auto_increment_increment=3;2.用戶變量:用戶變量也稱用戶會(huì)話變量，用戶在會(huì)話連接空間內(nèi)定義的變量，因此用戶變量屬于某個(gè)特定會(huì)話。并在變量名前加“@”變量用戶變量的賦值方式：（1）SET語句的語法格式SET@v_sno='02300';SET@v_sno=(SELECTsnoFROMstudentWHEREsname='王燕');（2）SELECT語句的語法格式SELECT@num:=(SELECTCOUNT(*)FROMstudent);SELECTCOUNT(*)into@numFROMstudent;3.局部變量局部變量是在語句塊(BEGIN…END)內(nèi)部定義的變量，其作用范圍僅限于定義該變量的語句塊。使用前需用DECLARE語句聲明，并指明數(shù)據(jù)類型。變量例1:定義長度為3的字符串型變量var_s，默認(rèn)值為“女”，并將其重新賦值為“男”DECLAREvar_sCHAR(3)DEFAULT'女';SETvar_s='男';例2:學(xué)號(hào)為“001101”學(xué)生的姓名賦給字符串型變量var_nameDECLAREvar_nameVARCHAR(10);SELECTsnameINTOvar_nameFROMstudentWHEREsno='001101';或SETvar_name=(SELECTsnameFROMstudentWHEREsno='001101');定義語句結(jié)束符在命令行客戶端中，如果有一行命令以分號(hào)結(jié)束，那么回車后，mysql將會(huì)執(zhí)行該命令。但有時(shí)候，一次執(zhí)行多條sql命令，此時(shí)必需改變結(jié)束符。DELIMITER命令可以重新定義代碼執(zhí)行的結(jié)束符，如“//”“$$”等符號(hào)。DELIMITER//CREATEPROCEDURE過程名()BEGIN

語句1;語句2;……[語句n;]END//DELIMITER;流程控制結(jié)構(gòu)流程控制結(jié)構(gòu)主要有順序結(jié)構(gòu)、分支（選擇）結(jié)構(gòu)和循環(huán)結(jié)構(gòu)?！纠?.7】使用LOOP和LEAVE語句，計(jì)算1～100的所有自然數(shù)的累加和DELIMITER//CREATEPROCEDUREc_sum()BEGINDECLARE_countINTDEFAULT0;DECLARE_sumINTDEFAULT0;label:LOOPSET_sum=_sum+_count;SET_count=_count+1;

IF_count>100THENLEAVElabel;ENDIF; ENDLOOPlabel;SELECT_sum;END//DELIMITER;存儲(chǔ)過程概述存儲(chǔ)過程(StoredProcedure)是由一些SQL語句和流程控制語句構(gòu)成的集合，它可以被應(yīng)用程序、觸發(fā)器或另外一個(gè)存儲(chǔ)過程所調(diào)用，執(zhí)行后能完成預(yù)先設(shè)定的功能。存儲(chǔ)過程具有以下優(yōu)點(diǎn)：一個(gè)存儲(chǔ)過程一次可以執(zhí)行一組SQL語句。存儲(chǔ)過程可調(diào)用其他存儲(chǔ)過程，體現(xiàn)模塊化編程。存儲(chǔ)過程在創(chuàng)建時(shí)就在服務(wù)器上進(jìn)行編譯，所以執(zhí)行速度比單條SQL語句快。存儲(chǔ)過程可以重復(fù)執(zhí)行，進(jìn)而減少重復(fù)工作，提高執(zhí)行效率。創(chuàng)建存儲(chǔ)過程定義存儲(chǔ)過程：CREATEPROCEDUREprocedure_name([[IN|OUT|INOUT]parameter_nametype[,...]])routine_body；存儲(chǔ)過程的參數(shù)有三種類型IN、OUT和INOUT。IN輸入?yún)?shù)，OUT輸出參數(shù)，INOUT輸入輸出參數(shù)，默認(rèn)是IN。routine_body；為過程體，一般為BENG……END格式。調(diào)用存儲(chǔ)過程：CALLprocedure_name[(procedure_parameter)];

procedure_parameter表示實(shí)際參數(shù)。如果不需要參數(shù)，則語法格式可簡化為CALLprocedure_name;或CALLprocedure_name();。創(chuàng)建存儲(chǔ)過程【例8.9】創(chuàng)建存儲(chǔ)過程pro_del_sc，根據(jù)學(xué)號(hào)和課程號(hào)查詢score表中的數(shù)據(jù)，學(xué)號(hào)和課程號(hào)由存儲(chǔ)過程的參數(shù)傳入。DELIMITER//CREATEPROCEDUREpro_del_sc2(v_snoCHAR(6),v_cnoCHAR(3))BEGINDELETEFROMscoreWHEREsno=v_snoANDcno=v_cno;END//DELIMITER;--調(diào)用CALLpro_del_sc2('001106','102');創(chuàng)建存儲(chǔ)過程【例8.10】創(chuàng)建存儲(chǔ)過程pro_inquire，該存儲(chǔ)過程通過學(xué)生姓或名實(shí)現(xiàn)模糊查詢，查詢出符合條件的學(xué)號(hào)和姓名，存儲(chǔ)過程的參數(shù)為學(xué)生的姓或名。DELIMITER//CREATEPROCEDUREpro_inquire(v_nameVARCHAR(20))BEGINSELECTsno,snameFROMstudentWHEREsnameLIKECONCAT('%',v_name,'%');END//DELIMITER;--調(diào)用CALLpro_inquire('林');1.定義游標(biāo)DCELARE<游標(biāo)名>

CURSORFOR<查詢語句>；游標(biāo)2.打開游標(biāo)OPEN<游標(biāo)名>；3.使用游標(biāo)FETCH<游標(biāo)名>INTO變量名1[,變量名2]……;4.關(guān)閉游標(biāo)CLOSE<游標(biāo)名>;發(fā)生NOTFOUND異常游標(biāo)（Cursor）是處理數(shù)據(jù)的一種方法，為了查看或者處理結(jié)果集中的數(shù)據(jù)，游標(biāo)提供了在結(jié)果集中一次一行遍歷數(shù)據(jù)的能力。游標(biāo)使用的流程為：游標(biāo)【例8.11】根據(jù)學(xué)號(hào)統(tǒng)計(jì)某同學(xué)所獲得的總學(xué)分，如果課程成績達(dá)不到60分，該門課程的學(xué)分不能計(jì)入。DELIMITER//DROPPROCEDUREIFEXISTSupdateToealcredit//CREATEPROCEDUREupdateToealcredit()BEGINDECLAREdoneBOOLEANDEFAULTFALSE;DECLARE_snoCHAR(6);DECLARE_totalcreditDECIMAL(5,1);DECLAREstudent_cursorCURSORFORSELECTsnoFROMstudent;DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=TRUE;OPENstudent_cursor;read_loop:LOOPFETCHstudent_cursorINTO_sno;游標(biāo)IFdoneTHENLEAVEread_loop;ENDIF;SELECTSUM(IF(grade>=60,credit,0))INTO_totalcreditFROMcourseJOINscoreONo=oWHEREscore.sno=_sno;UPDATEstudentSETtotalcredit=_totalcreditWHEREsno=_sno;ENDLOOP;

CLOSEstudent_cursor;END//DELIMITER;1.定義條件定義條件的語句用于將MySQL中的錯(cuò)誤代碼命名為異常名，并將異常名與指定的錯(cuò)誤條件關(guān)聯(lián)，其語法格式為：DECLAREcondition_nameCONDITIONFOR{SQLSTATEsqlstate_value|MySQL_error_code};異常處理例違反外鍵約束條件異常的定義條件的語句為：DECLAREerror_foreignkeyCONDITIONFOR1452;

DECLAREerror_foreignkeyCONDITIONFORSQLSTATE'23000';存儲(chǔ)過程調(diào)用中，常常有異常情況出現(xiàn)。為保證順利調(diào)用存儲(chǔ)過程，需要對(duì)異常進(jìn)行處理。流程為：先對(duì)存儲(chǔ)程序執(zhí)行中可能出現(xiàn)的異常進(jìn)行聲明，再對(duì)出現(xiàn)的異常進(jìn)行適當(dāng)?shù)奶幚?，保證存儲(chǔ)程序在系統(tǒng)警告或報(bào)錯(cuò)的情況下仍能繼續(xù)執(zhí)行。條件處理器既增強(qiáng)了存儲(chǔ)程序處理問題的能力，也避免了程序因異常而停止運(yùn)行。2.定義處理程序語法格式為：DECLARE{CONTINUE|EXIT}HANDLERFOR{condition_value}statement;異常處理CONTINUE：表示遇到錯(cuò)誤不處理，繼續(xù)執(zhí)行。

EXIT：表示遇到錯(cuò)誤退出。condition_value包括:SQLSTATE‘sqlstate_value’中的’sqlstate_value’是長度為5的字符串錯(cuò)誤代碼；MySQL_error_code：指數(shù)值類型錯(cuò)誤代碼；condition_name：指通過定義條件語句命名的異常名；SQLWARNING指以01開頭的所有SQLSTATE碼所對(duì)應(yīng)的異常；NOTFOUND指以02開頭的所有SQLSTATE碼所對(duì)應(yīng)的異常；SQLEXCEPTION指不以01或02開頭的SQLSTATE碼所對(duì)應(yīng)的異?！纠?.12】創(chuàng)建存儲(chǔ)過程insert_sc，該存儲(chǔ)過程將完成向score表插入數(shù)據(jù)，其中學(xué)號(hào)和課程號(hào)由輸入?yún)?shù)給出，同時(shí)程序返回是否插入成功的結(jié)果。DELIMITER//CREATEPROCEDUREinsert_sc(v_snoCHAR(6),v_cnoCHAR(3),v_gradeINT,OUTv_messageVARCHAR(100))BEGINDECLAREdoneBOOLEANDEFAULT0;DECLAREfk_errorCONDITIONFOR1452;//1452是違反外鍵約束代碼DECLARECONTINUEHANDLERFORfk_errorSETdone=1;//fk_error也可以直接用1452代替，省略上行INSERTINTOscoreVALUES(NULL,v_sno,v_cno,v_grade);IFdone=1THENSETv_message:='error_foreignkey';ELSESETv_message:='success';ENDIF;END//DELIMITER;異常處理調(diào)用、查看和刪除存儲(chǔ)過程查看存儲(chǔ)過程SHOWCREATEPROCEDUREprocedure_name;刪除存儲(chǔ)過程DROPPROCEDUREprocedure_name;調(diào)用帶參存儲(chǔ)過程形參與實(shí)參一一對(duì)應(yīng)，輸出參數(shù)要定義變量保存返回的值CALLinsert_sc('001101','701',60,@message);SELECT@message;存儲(chǔ)函數(shù)存儲(chǔ)函數(shù)可以由用戶自定義創(chuàng)建，能通過RETURN語句返回一個(gè)函數(shù)值。創(chuàng)建用戶自定義函數(shù)語句:CREATEFUNCTIONfunc_name([func_param[...]])RETURNStype[characteristic]func_body;[characteristic...]特征參數(shù)是可選的，通常取:DETERMINISTIC[d??t??m??n?st?k]：確定的輸入得到確定的輸出NOSQL：子程序不包含sqlREADSSQLDATA：子程序包含讀數(shù)據(jù)的語句，但是不包含寫數(shù)據(jù)的語句。注意：這些特征值目前提供給服務(wù)器，并沒有根據(jù)這些特征值來約束過程實(shí)際使用數(shù)據(jù)的情況，因此選擇這三者中的任何一項(xiàng)都可以?！纠?.14】創(chuàng)建存儲(chǔ)函數(shù)fun_st_num，統(tǒng)計(jì)student表中某個(gè)系別的人數(shù)，系名由函數(shù)的參數(shù)給出DELIMITER//DROPFUNCTIONIFEXISTSfun_st_num//CREATEFUNCTIONfun_st_num(v_deptVARCHAR(20))RETURNSINTREADSSQLDATABEGINDECLAREv_numINTDEFAULT0;SELECTCOUNT(*)INTOv_numFROMstudentWHEREdept=v_dept;

RETURNv_num;END//DELIMITER;存儲(chǔ)函數(shù)調(diào)用函數(shù)在MySQL中，自定義函數(shù)的調(diào)用方法與MySQL內(nèi)置函數(shù)的調(diào)用方法是一樣的。MySQL內(nèi)置函數(shù)是系統(tǒng)自帶的，自定義函數(shù)為用戶創(chuàng)建的。調(diào)用方式一：在語句中直接調(diào)用func_name（func_parameter）調(diào)用方式二：SELECTfunc_name（func_parameter）;存儲(chǔ)函數(shù)存儲(chǔ)函數(shù)查看存儲(chǔ)函數(shù)創(chuàng)建信息SHOWCREATEFUNCTIONfunction_name;刪除存儲(chǔ)函數(shù)DROPFUNCTIONfunction_name;練習(xí)【例8.11】根據(jù)學(xué)號(hào)統(tǒng)計(jì)某同學(xué)所獲得的總學(xué)分，如果課程成績達(dá)不到60分，該門課程的學(xué)分不能計(jì)入。要求將根據(jù)學(xué)號(hào)計(jì)算總學(xué)分的部分改為存儲(chǔ)函數(shù)來實(shí)現(xiàn)。DELIMITER$$DROPFUNCTIONIFEXISTSsumTotalcredit$$CREATEFUNCTIONsumTotalcredit(_snoCHAR(6))RETURNSDECIMALNOSQLBEGINDECLARE_totalcreditDECIMAL(5,1);SELECTSUM(IF(grade>=60,credit,0))INTO_totalcreditFROMcourseJOINscoreONo=oWHEREscore.sno=_sno;RETURN_totalcredit;END$$DELIMITER;UPDATEstudentSETtotalcredit=sumTotalcredit(_sno)WHEREsno=_sno;觸發(fā)器觸發(fā)器（trigger）是一種特殊的存儲(chǔ)過程，編譯后存儲(chǔ)在數(shù)據(jù)庫服務(wù)器中。當(dāng)特定事件發(fā)生時(shí)，由系統(tǒng)自動(dòng)調(diào)用執(zhí)行。觸發(fā)器不接受任何輸入?yún)?shù)。觸發(fā)器是在特定表上進(jìn)行定義的，該表也稱為觸發(fā)器表。當(dāng)有針對(duì)觸發(fā)器表的操作時(shí)，觸發(fā)器就自動(dòng)觸發(fā)執(zhí)行。操作通常為：插入（Insert）刪除（Delete）修改（Update）觸發(fā)事件：插入（Insert）、刪除（Delete）、修改（Update）；觸發(fā)時(shí)機(jī)：事件前（before）、事件后(after)；觸發(fā)級(jí)別：表級(jí)(只觸發(fā)一次)、行級(jí)(foreachrow每行觸發(fā)一次)；觸發(fā)器引用記錄：新記錄表(new)舊記錄表(old)：

插入：新記錄在插入時(shí)放入new表：

刪除：原記錄在刪除時(shí)放入old表：

修改：原記錄→(old)新記錄→(new)。創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器的格式：CREATETRIGGER[IFNOTEXISTS]<trigger_name>{BEFORE|AFTER}{UPDATE|INSERT|DELETE}ON<table_name>[FOREACHROW]TRIGGER_BODY;觸發(fā)事件：指定INSERT、DELETE或UPDATE事件，MySQL不支持多個(gè)事件觸發(fā)，即一個(gè)觸發(fā)器只支持一個(gè)事件?！纠?.16】創(chuàng)建一個(gè)保存成績表修改操作的日志表score_log(id,operate_date,operate_user，log_text)，

用于記錄對(duì)score表所做的修改操作的相關(guān)信息。（1）創(chuàng)建日志表CREATETABLEscore_log(idINTNOTNULLAUTO_INCREMENTPRIMARYKEY,operate_dateDATE,operate_userVARCHAR(20),log_textVARCHAR(200));創(chuàng)建觸發(fā)器（2）創(chuàng)建觸發(fā)器trig_score。DELIMITER//DROPTRIGGERIFEXISTStrig_score//CREATETRIGGERtrig_scoreAFTERUPDATEONscoreFOREACHROWBEGININSERTINTOscore_log(id,operate_date,operate_user,log_text)VALUES(NULL,NOW(),USER(),CONCAT(‘學(xué)號(hào):’,OLD.sno,‘,’課程號(hào):’,OLD.CNO,’原成績:',OLD.grade,'新成績',NEW.grade));END//DELIMITER;創(chuàng)建觸發(fā)器（3）驗(yàn)證觸發(fā)器，修改score表數(shù)據(jù)后，查看score_log內(nèi)容。

UPDATEscoreSETgrade=80WHEREsno=‘001103’ANDcno=‘102‘;SELECT*FROMscore_log;創(chuàng)建觸發(fā)器【例8.18】創(chuàng)建觸發(fā)器sum_credit，實(shí)現(xiàn)對(duì)student表總學(xué)分的累加，當(dāng)score中添加記錄時(shí)，student表總學(xué)分的值做相應(yīng)改變。當(dāng)課程成績大于等于60分時(shí)，將該課程的學(xué)分加到該學(xué)生的總學(xué)分中。DELIMITER$$DROPTRIGGERIFEXISTSsum_credit$$CREATETRIGGERsum_creditAFTERINSERTONscoreFOREACHROWBEGINUPDATEstudentSETtot