《MySQL數(shù)據(jù)庫(kù)應(yīng)用與管理項(xiàng)目化教程》課件-項(xiàng)目7-3 存儲(chǔ)過(guò)程

項(xiàng)目七-3

存儲(chǔ)過(guò)程和函數(shù)任務(wù)目標(biāo)任務(wù)一創(chuàng)建存儲(chǔ)過(guò)程和函數(shù)任務(wù)二查看存儲(chǔ)過(guò)程和函數(shù)任務(wù)三 編程語(yǔ)法與流程控制任務(wù)四 修改存儲(chǔ)過(guò)程和函數(shù)任務(wù)六刪除存儲(chǔ)過(guò)程和函數(shù)

創(chuàng)建存儲(chǔ)過(guò)程和函數(shù)

主要內(nèi)容:創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)函數(shù)重點(diǎn)難點(diǎn):設(shè)計(jì)和應(yīng)用；存儲(chǔ)過(guò)程和函數(shù)應(yīng)用引入：db_shop系統(tǒng)中要完成一個(gè)購(gòu)買(mǎi)商品的訂單的處理，一般需要考慮以下幾步：（1）在生成訂單之前，首先需要查看商品庫(kù)存中是否有相應(yīng)商品；（2）如果商品庫(kù)存中不存在相應(yīng)商品，需要向供應(yīng)商訂貨；（3）如果商品庫(kù)存中存在相應(yīng)商品，需要預(yù)定商品，并修改庫(kù)存數(shù)量。針對(duì)表的一個(gè)完整事務(wù)操作往往不是單條SQL語(yǔ)句就能實(shí)現(xiàn)的，而是需要一組SQL語(yǔ)句來(lái)實(shí)現(xiàn)?？蓪⒁粋€(gè)完整事務(wù)操作中所包含的多條SQL語(yǔ)句創(chuàng)建為存儲(chǔ)過(guò)程或函數(shù)，以方便應(yīng)用。存儲(chǔ)過(guò)程和函數(shù)作用存儲(chǔ)過(guò)程和函數(shù)可以簡(jiǎn)單地理解為一組經(jīng)過(guò)編譯并保存在數(shù)據(jù)庫(kù)中的SQL語(yǔ)句的集合，可以隨時(shí)被調(diào)用。作用模塊化使用。將復(fù)雜的工作程序?qū)懗纱鎯?chǔ)過(guò)程和函數(shù)，以后可通過(guò)帶入不同的參數(shù)或不帶參重復(fù)調(diào)用，大大方便了用戶的使用。執(zhí)行效率高。存儲(chǔ)過(guò)程和函數(shù)，執(zhí)行多次時(shí)速度快。降低網(wǎng)絡(luò)流量。存儲(chǔ)過(guò)程是存在服務(wù)器端、并在服務(wù)器端執(zhí)行，調(diào)用執(zhí)行時(shí)用一個(gè)EXECUTE指令來(lái)代替存儲(chǔ)過(guò)程內(nèi)所包含的大量SQL語(yǔ)句的傳輸，大大降低了網(wǎng)絡(luò)流量。安全性。當(dāng)數(shù)據(jù)表需要保密時(shí)，可以利用存儲(chǔ)過(guò)程來(lái)作為數(shù)據(jù)存取的管道，來(lái)控制用戶對(duì)數(shù)據(jù)庫(kù)信息訪問(wèn)的權(quán)限。存儲(chǔ)過(guò)程創(chuàng)建查閱語(yǔ)法：官網(wǎng)文檔：/doc/refman/8.0/en/create-procedure.htmlCREATE[DEFINER=user]PROCEDUREsp_name([proc_parameter[,...]])[characteristic...]routine_bodycharacteristic:COMMENT'string'|LANGUAGESQL|[NOT]DETERMINISTIC|{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}routine_body:ValidSQLroutinestatement存儲(chǔ)過(guò)程創(chuàng)建創(chuàng)建基本語(yǔ)法：CREATEPROCEDUREproc_name([IN|OUT|INOUT]param_nametype[,…])BEGIN語(yǔ)句體（包括變量聲明、控制語(yǔ)句、SQL語(yǔ)句）END說(shuō)明：IN|OUT|INOUT表示：輸入?yún)?shù)、輸出參數(shù)、輸入輸出參數(shù)。語(yǔ)句體以BEGIN......END括住，每一個(gè)語(yǔ)句都要用分號(hào)“；”結(jié)尾。由于存儲(chǔ)過(guò)程內(nèi)部語(yǔ)句要以分號(hào)結(jié)束，需要在定義存儲(chǔ)過(guò)程前，用delimiter關(guān)鍵字定義其他字符作為結(jié)束標(biāo)志。存儲(chǔ)過(guò)程調(diào)用調(diào)用基本語(yǔ)法：CALLsp_name([parameter[,...]])CALLsp_name[()]存儲(chǔ)過(guò)程應(yīng)用使用不帶參數(shù)的存儲(chǔ)過(guò)程mysql>delimiter$$

--改變MySQLdelimiter為：“//”改變命令提交執(zhí)行的標(biāo)志符號(hào)，默認(rèn)情況下，delimiter是分號(hào);

mysql>CREATEPROCEDUREp_search()BEGIN SELECT*FROMgoods;END$$mysql>delimiter;--改回默認(rèn)的MySQLdelimiter：“;”mysql>CALLp_Search();存儲(chǔ)過(guò)程應(yīng)用使用帶輸入（IN）參數(shù)的存儲(chǔ)過(guò)程mysql>delimiter//--改變MySQLdelimiter為：“//”

mysql>CREATEPROCEDUREp_staferSearch(INsidCHAR(5))BEGINSELECT*FROMstafferWHEREstaff_id=sid;END//mysql>delimiter;--改回默認(rèn)的MySQLdelimiter：“;”mysql>CALLp_staferSearch('10503');存儲(chǔ)過(guò)程應(yīng)用使用帶輸入（IN）參數(shù)和輸出（OUT

）參數(shù)的存儲(chǔ)過(guò)程mysql>DELIMITER//--改變MySQLdelimiter為：“//”mysql>CREATEPROCEDUREp_count(INdeptidCHAR(5),OUTnINT)BEGINSELECTcount(*)INTOnFROMstafferWHEREdept_id=deptid;END//mysql>DELIMITER;--改回默認(rèn)的MySQLdelimiter：“;”mysql>CALLp_count(1,@a);mysql>SELECT@a;mysql>CALLp_count(2,@a);mysql>SELECT@a;存儲(chǔ)過(guò)程應(yīng)用使用帶INOUT

參數(shù)的存儲(chǔ)過(guò)程mysql>DELIMITER//--改變MySQLdelimiter為：“//”mysql>CREATEPROCEDUREitem_count(INidINT,INOUTnINT)

BEGIN

DECLAREcount1INT;

SELECTcount(*)INTOcount1FROMitemWHEREorder_id=id;

SETn=count1*n;

END//mysql>DELIMITER;--改回默認(rèn)的MySQLdelimiter：“;”mysql>SET@n=10;mysql>CALLitem_count(1,@n);mysql>SELECT@n;函數(shù)創(chuàng)建和調(diào)用查閱語(yǔ)法：官網(wǎng)文檔：/doc/refman/8.0/en/create-function.html創(chuàng)建語(yǔ)法：CREATE[DEFINER=user]FUNCTIONfunc_name[func_parameter[,...]])RETURNStype[characteristic...]routine_body調(diào)用語(yǔ)法：SET@var_name=func_name(parameter[,......]);SELECTfunc_name(parameter[,......]);函數(shù)創(chuàng)建mysql>showvariableslike'log_bin_trust_function_creators';#查看設(shè)置，可以在f配置文件中添加：log_bin_trust_function_creators=1mysql>setgloballog_bin_trust_function_creators=1;#如果出現(xiàn)ThisfunctionhasnoneofDETERMINISTIC,NOSQL,orREADSSQLDATAinitsdeclarationandbinaryloggingisenabled例1：建立一個(gè)2個(gè)整數(shù)相乘的函數(shù)。mysql>DROPFUNCTIONIFEXISTSSimpleCompare;mysql>DELIMITER//mysql>CREATEFUNCTIONSimpleCompare(nINT,mINT)RETURNSINT

#-----------------------------------注意是RETURNS,有個(gè)SBEGINDECLAREmul

INT;SETmul=n*m;RETURNmul;

#-------------------------這里才是RETURN，沒(méi)有SEND//mysql>DELIMITER;mysql>SELECTSimpleCompare(2,3);函數(shù)創(chuàng)建例2：編寫(xiě)一個(gè)函數(shù)，統(tǒng)計(jì)指定商品編號(hào)的訂單詳細(xì)表的商品銷售筆數(shù)mysql>DELIMITER//mysql>CREATEFUNCTIONitem_count(idINT)RETURNSINTBEGIN DECLAREnint; SELECTCOUNT(*)INTOnFROMitem WHEREgoods_id=id; RETURNn; END//mysql>DELIMITER;mysql>SET@n=item_count(1);mysql>SELECT@n,item_count(2);函數(shù):應(yīng)用（引出流程編程）例2：編寫(xiě)一個(gè)函數(shù)，通過(guò)職員編號(hào)查詢職員姓名mysql>setgloballog_bin_trust_function_creators=1;#如果出現(xiàn)ThisfunctionhasnoneofDETERMINISTIC,NOSQL,orREADSSQLDATAinitsdeclarationandbinaryloggingisenabledmysql>DROPFUNCTIONIFEXISTSstaffer_search;mysql>DELIMITER//--改變MySQLdelimiter為：“//”mysql>CREATEFUNCTIONstaffer_search(idCHAR(10))RETURNSVARCHAR(10)BEGINDECLAREsnameVARCHAR(10);SELECTstaff_nameINTOsnameFROMstafferWHEREstaff_id=id;IFISNULL(sname)THENRETURN'無(wú)人';ELSERETURNsname;ENDIF;END//mysql>DELIMITER;mysql>SET@sname=staffer_search('10501');mysql>SELECT@sname,staffer_search('10401');查看存儲(chǔ)過(guò)程和函數(shù)查閱語(yǔ)法：官網(wǎng)文檔：/doc/refman/8.0/en/show.html查看狀態(tài)：SHOWSTATUS語(yǔ)句語(yǔ)法：SHOW{PROCEDURE|FUNCTION}STATUS

[LIKE'pattern'|WHEREexpr]mysql>SHOWPROCEDURESTATUSLIKE'%staffer%';mysql>SHOWFUNCTIONSTATUSLIKE'%staffer%';查看定義：SHOWCREATE語(yǔ)句語(yǔ)法：SHOWCREATE{PROCEDURE|FUNCTION}proc_or_func_namemysql>SHOWCREATEPROCEDUREp_searchStaffer;mysql>SHOWCREATEFUNCTIONstaffer_search;查看存儲(chǔ)過(guò)程和函數(shù)查看詳細(xì)信息：使用系統(tǒng)數(shù)據(jù)庫(kù)的表information_schema.routinesmysql>SELECT*FROMinformation_schema.routines;mysql>SELECT*FROMinformation_schema.routinesWHEREROUTINE_NAMELIKE'%search%';修改存儲(chǔ)過(guò)程和函數(shù)查閱語(yǔ)法：官網(wǎng)文檔：/doc/refman/8.0/en/alter-procedure.html語(yǔ)法：ALTER{PROCEDURE|FUNCTION}

proc_or_func_name

[characteristic...]characteristic:COMMENT'string'|LANGUAGESQL|{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}作用：修改存儲(chǔ)過(guò)程和函數(shù)的特性。修改存儲(chǔ)過(guò)程和函數(shù)例如：將讀寫(xiě)權(quán)限改為MODIFIESSQLDATA，并指明調(diào)用者可以執(zhí)行。mysql>ALTERPROCEDUREp_searchStafferMODIFIESSQLDATASQLSECURITYINVOKER;例如：將讀寫(xiě)權(quán)限改為READSSQLDATA，并加上注釋信息'FINDNAME'。mysql>ALTERPROCEDUREp_searchStafferREADSSQLDATACOMMENT'FINDNAME';mysql>SELECT*FROMinformation_schema.routines#查看修改結(jié)果WHEREROUTINE_NAMELIKE'%search%';刪除存儲(chǔ)過(guò)程語(yǔ)法：DROP{PROCEDURE|FUNCTION}[IFEXISTS]sp_name例如：mysql>DROPFUNCTIONIFEXISTSstaffer_search;mysql>DROPPROCEDUREIFEXISTSitem_count;考考你對(duì)部門(mén)表，分別建立相應(yīng)的插入、刪除、更新存儲(chǔ)過(guò)程對(duì)員工表，分別建立按職員號(hào)、按姓名的查詢函數(shù)；任務(wù)實(shí)施按下列操作完成db_shopping數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程和函數(shù)的查看。1.選擇db_shopping數(shù)據(jù)庫(kù)，執(zhí)行語(yǔ)句如下：mysql>USEdb_shopping;2.建立按部門(mén)名稱查看部門(mén)信息的存儲(chǔ)過(guò)程，并調(diào)用測(cè)試，執(zhí)行語(yǔ)句如下：mysql>delimiter//--改變MySQLdelimiter為：“//”mysql>CREATEPROCEDUREp_dept(INdnameVARCHAR(20))BEGINSELECT*FROMdepartmentWHEREdept_name=dname;END//mysql>delimiter;--改回默認(rèn)的MySQLdelimiter：“;”mysql>CALLp_dept(‘銷售部’);3.建立一個(gè)存儲(chǔ)過(guò)程，實(shí)現(xiàn)按部門(mén)編號(hào)修改所有部門(mén)信息，執(zhí)行語(yǔ)句如下：mysql>USEdb_shop;mysql>delimiter//mysql>CREATEPROCEDUREp_dept_up(d_idint,d_namevarchar(20),d_phonechar(13),d_memovarchar(100))BEGINUPDATEdepartmentSETdept_name=d_name,dept_phone=d_phone,dept_memo=d_memoWHEREid=d_id;END//mysql>delimiter;mysql>CALLp_dept_up(6,'外聯(lián)部',,'對(duì)外聯(lián)系');4.建立一個(gè)存儲(chǔ)過(guò)程，按訂購(gòu)號(hào)查看其訂購(gòu)額，把訂購(gòu)額通過(guò)輸出參數(shù)輸出，并調(diào)用，執(zhí)行語(yǔ)句如下：mysql>DELIMITER//--改變MySQLdelimiter為：“//”mysql>CREATEPROCEDUREp_orders(INOrd_idINT,OUTxFLOAT) BEGIN SELECTamount_moneyINTOxFROMordersWHEREid=Ord_id;END//mysql>DELIMITER;--改回默認(rèn)的MySQLdelimiter：“;”mysql>CALLitem_promotion(1,@x);mysql>SELECT@x;任務(wù)實(shí)施按下列操作完成db_shopping數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程和函數(shù)的查看。（1）選擇db_shopping數(shù)據(jù)庫(kù)，執(zhí)行語(yǔ)句如下：mysql>USEdb_shopping;（2）建立一個(gè)函數(shù)，可按訂購(gòu)號(hào)查看其訂購(gòu)額，返回訂購(gòu)額，并調(diào)用測(cè)試，執(zhí)行語(yǔ)句如下：mysql>DELIMITER//--改變MySQLdelimiter為：“//”mysql>CREA