主講教師：姜云橋任務4.3存儲過程和函數(shù)的開發(fā)子項目4數(shù)據(jù)庫高級應用開發(fā)任務背景銀行經(jīng)常需要計算用戶的利息，但不同類別的用戶的利率是不一樣的。這就可以將計算利率的SQL代碼寫成一個程序存放起來，用指定的用戶類別作參數(shù)。這樣的程序叫作存儲過程或者存儲函數(shù)。任務目標1.能創(chuàng)建存儲過程2.能執(zhí)行存儲過程3.能修改和刪除存儲過程知識目標1.了解DElimitER命令2.理解存儲過程3.掌握創(chuàng)建存儲過程的方法4.掌握修改和刪除存儲過程的方法能力目標任務要求通過本次任務，使學生能夠創(chuàng)建、執(zhí)行、修改和刪除存儲過程；掌握創(chuàng)建基本的存儲過程的方法，掌握創(chuàng)建帶有變量的存儲過程的方法，理解創(chuàng)建帶有流程控制語句的存儲過程的方法。任務必備知識想一想試解釋存儲過程？銀行經(jīng)常需要計算用戶的利息，但不同類別的用戶的利率是不一樣的。這就可以將計算利率的sql代碼寫成一個程序存放起來，用指定的用戶類別作參數(shù)。這樣的程序叫作存儲過程或者存儲函數(shù)。使用時只要調(diào)用這個存儲過程或者存儲函數(shù)，根據(jù)指定的用戶類別，就可以將不同類別用戶的利息計算出來。任務必備知識任務必備知識4.3.1存儲過程和函數(shù)的基本操作一、創(chuàng)建存儲過程創(chuàng)建存儲過程可以使用createprocedure語句。createprocedure[ifnotexists]sp_name([in|out|inout]param_nametype,...)routine_body任務必備知識【任務4.3.1】創(chuàng)建存儲過程sp_delete_student，用指定的學號作為參數(shù)刪除某一學生的記錄4.3.1存儲過程和函數(shù)的基本操作mysql>delimiter$$mysql>createproceduresp_delete_student(innochar(8))->begin->deletefromstudentwheres_no=no;->end$$mysql>delimiter;任務必備知識【任務4.3.2】創(chuàng)建帶輸出參數(shù)的存儲過程sp_count_student，求學生人數(shù)4.3.1存儲過程和函數(shù)的基本操作mysql>delimiter$$mysql>createproceduresp_count_student(outstu_cntintunsigned)->begin->selectcount(*)intostu_cntfromstudent;->end$$mysql>delimiter;任務必備知識【任務4.3.3】創(chuàng)建存儲過程sp_search_teacher，以指定的系別號為參數(shù)，查找某學院的老師姓名、所在院系名稱4.3.1存儲過程和函數(shù)的基本操作mysql>delimiter$$mysql>createproceduresp_search_teacher(innochar(8))->begin->selectt.t_name,d.d_name->fromteachertjoindepartmentdont.d_no=d.d_no->wheret.d_no=no;->end$$mysql>delimiter;任務必備知識4.3.1存儲過程和函數(shù)的基本操作二、調(diào)用存儲過程創(chuàng)建完存儲過程之后，調(diào)用存儲過程需要使用call語句。callsp_name(parameter,...)【任務4.3.4】調(diào)用存儲過程sp_delete_student,刪除學號為122001的學生的信息mysql>callsp_delete_student('122001');任務必備知識4.3.1存儲過程和函數(shù)的基本操作【任務4.3.5】調(diào)用存儲過程sp_count_student,統(tǒng)計學生人數(shù)mysql>callsp_count_student(@num);mysql>select@num;【任務4.3.6】調(diào)用存儲過程sp_search_teacher,查詢院系編號D001的教師姓名和院系名稱mysql>callsp_search_teacher('D001');任務必備知識4.3.1存儲過程和函數(shù)的基本操作三、查看存儲過程showprocedurestatus[like'pattern'|whereexpr]說明：（1）like匹配存儲過程的名稱。（2）where可以指定更多的過濾條件。1.查看存儲過程的狀態(tài)信息任務必備知識4.3.1存儲過程和函數(shù)的基本操作【任務4.3.7】查看存儲過程sp_search_teacher的狀態(tài)信息mysql>showprocedurestatuslike'sp_search_teacher'\G***************************1.row***************************Db:jxglName:sp_search_teacherType:PROCEDUREDefiner:root@localhostModified:2023-01-2523:07:38Created:2023-01-2523:07:38Security_type:DEFINERComment:character_set_client:utf8mb4collation_connection:utf8mb4_0900_ai_ciDatabaseCollation:utf8mb4_0900_ai_ci任務必備知識4.3.1存儲過程和函數(shù)的基本操作類似與查看數(shù)據(jù)庫和數(shù)據(jù)表的定義，存儲過程的定義可通過SHOWCREATEPROCEDURE命令查看。2.查看存儲過程的定義【任務4.3.8】查看存儲過程sp_search_teacher的定義mysql>showcreateproceduresp_search_teacher\G任務必備知識4.3.1存儲過程和函數(shù)的基本操作四、刪除存儲過程可以使用dropprocedure刪除已經(jīng)存在的存儲過程。dropprocedure[ifexists]sp_name;【任務4.3.9】刪除存儲過程sp_search_teachermysql>dropproceduresp_search_teacher;任務必備知識4.3.1存儲過程和函數(shù)的基本操作五、存儲函數(shù)MySQL存儲函數(shù)和存儲過程類似，也是存儲在數(shù)據(jù)庫中的程序，其查看、刪除過程和存儲過程一樣，只不過將procedure換成function即可，主要在于創(chuàng)建和調(diào)用過程稍有不同。創(chuàng)建過程：createfunction[ifnotexists]sp_name(param_nametype,...)returnstype[deterministic|nosql|readssqldata]routine_body任務必備知識4.3.1存儲過程和函數(shù)的基本操作【任務4.3.10】定義存儲函數(shù)func_count_score,獲取score表中分數(shù)超過指定成績的記錄數(shù)mysql>delimiter$$mysql>createfunctionfunc_count_score(sdecimal)returnsint->deterministic->begin->declares_countint;->selectcount(*)intos_countfromscorewheremark>=s;->returns_count;->end$$mysql>delimiter;任務必備知識4.3.1存儲過程和函數(shù)的基本操作【任務4.3.11】使用自定義的存儲函數(shù)func_count_score，獲取超過90分的成績記錄數(shù)mysql>selectfunc_count_score(90)as‘優(yōu)秀記錄數(shù)';任務必備知識4.3.1存儲過程和函數(shù)的基本操作【任務4.3.12】查看存儲函數(shù)func_count_score狀態(tài)信息mysql>showfunctionstatuslike'func_count_score'\G***************************1.row***************************Db:jxglName:func_count_scoreType:FUNCTIONDefiner:root@localhostModified:2023-01-2617:12:49Created:2023-01-2617:12:49Security_type:DEFINERComment:character_set_client:utf8mb4collation_connection:utf8mb4_0900_ai_ciDatabaseCollation:utf8mb4_0900_ai_ci1rowinset(0.01sec)或者執(zhí)行下列語句mysql>showfunctionstatuswherename='func_count_score'\G任務必備知識4.3.1存儲過程和函數(shù)的基本操作【任務4.3.13】刪除存儲函數(shù)func_count_scoremysql>dropfunctionfunc_count_score;任務必備知識4.3.1存儲過程和函數(shù)的基本操作存儲過程和存儲函數(shù)除了參數(shù)和返回值方式不同之外，最重要的區(qū)別在于存儲過程可以修改數(shù)據(jù)庫對象的狀態(tài)，而存儲函數(shù)卻不能。這也意味這存儲函數(shù)中主要以查詢?yōu)橹鳎梢苑旁诓樵冋Z句中使用，存儲過程卻不行。任務必備知識一、變量1.變量聲明存儲過程和函數(shù)可以定義和使用變量,它們可以用來存儲臨時結(jié)果。用戶可以使用declare關(guān)鍵字來定義變量，這些變量的作用范圍只適用于begin…end程序段中，所以是局部變量。過程和函數(shù)使用的局部變量必須在開頭就聲明。在聲明局部變量的同時也可以為其賦一個初始值：declarevar_nametype[defaultvalue];4.3.2存儲過程和函數(shù)的編程功能任務必備知識2.變量賦值給變量賦值有兩種方式，一種通過SET語句：setvar1=expr1,var2=expr2,...;另外一種方式通過selectINTO語句完成賦值：selectexpr1,expr2,...intovar1,var2,...from...;4.3.2存儲過程和函數(shù)的編程功能任務必備知識【任務4.3.14】創(chuàng)建一個存儲函數(shù)，根據(jù)課程號查詢不及格學生的數(shù)量mysql>delimiter$$mysql>createfunctionfunc_fail_count(nochar(4))returnsint->readssqldata->begin->declares_countintdefault0;->selectcount(*)intos_countfromscorewherec_no=noandmark<60;->returns_count;->end$$mysql>delimiter;4.3.2存儲過程和函數(shù)的編程功能任務必備知識【任務4.3.14】創(chuàng)建一個存儲函數(shù)，根據(jù)課程號查詢不及格學生的數(shù)量調(diào)用函數(shù)，查詢課程號A002不及格人數(shù)mysql>selectfunc_fail_count('A002')as'A002不及格人數(shù)';+---------------------+4.3.2存儲過程和函數(shù)的編程功能任務必備知識二、條件控制語句1.If語句MySQL提供了兩種條件控制語句：if語句和case語句IF語句可根據(jù)不同的條件執(zhí)行不同的操作。ifsearch_conditionthenstatement_listelseifsearch_conditionthenstatement_list...elsestatement_listendif4.3.2存儲過程和函數(shù)的編程功能任務必備知識【任務4.3.15】創(chuàng)建一個存儲過程，根據(jù)指定的參數(shù)（學號）查看某位學生的不及格科目數(shù)，如果不及格科目數(shù)超過2門（含2門），則輸出“啟動成績預警！”并輸出該生的成績單，否則輸出“成績在可控范圍”4.3.2存儲過程和函數(shù)的編程功能任務必備知識首先，創(chuàng)建存儲過程：mysql>delimiter$$mysql>createprocedureproc_query(innochar(8),outstrvarchar(20))->begin->declarefail_counttinyintunsigneddefault0;->selectcount(*)intofail_countfromscorewheres_no=noandmark<60;->iffail_count>=2then->setstr='啟動成績預警';->else->setstr='成績在可控范圍';->endif;->end$$mysql>delimiter;4.3.2存儲過程和函數(shù)的編程功能任務必備知識調(diào)用存儲過程，查詢學號122001的成績狀況：mysql>callproc_query('122001',@msg);mysql>select@msg;4.3.2存儲過程和函數(shù)的編程功能任務必備知識1.case語句case語句可以用于構(gòu)造復雜的條件判斷，類似于case表達式，case語句也存在兩種形式：簡單case語句和搜索case語句4.3.2存儲過程和函數(shù)的編程功能任務必備知識casecase_valuewhenwhen_valuethenstatement_listwhenwhen_valuethenstatement_list...elsestatement_listendcase;4.3.2存儲過程和函數(shù)的編程功能（1）簡單case語句任務必備知識【任務4.3.16】創(chuàng)建一個存儲過程，根據(jù)專業(yè)類型（專業(yè)課4分、專業(yè)基礎(chǔ)課3分、必須課2分、選修課1分）更新學分，參數(shù)為專業(yè)類型首先，創(chuàng)建存儲過程（不能創(chuàng)建函數(shù)，函數(shù)中不能更新表格數(shù)據(jù)）4.3.2存儲過程和函數(shù)的編程功能任務必備知識mysql>delimiter$$mysql>createprocedureproc_update_credit(intypevarchar(10))->begin->casetype->when'專業(yè)課'then->updatecoursesetc_credit=4wherec_type=type;->when'專業(yè)基礎(chǔ)課'then->updatecoursesetc_credit=3wherec_type=type;->when'必修課'then->updatecoursesetc_credit=2wherec_type=type;->else->updatecoursesetc_credit=1;->endcase;->end$$mysql>delimiter;4.3.2存儲過程和函數(shù)的編程功能任務必備知識casewhensearch_conditionthenstatement_listwhensearch_conditionthenstatement_list...elsestatement_listendcase;4.3.2存儲過程和函數(shù)的編程功能（2）搜索case語句任務必備知識【任務4.3.17】創(chuàng)建一個存儲函數(shù)，根據(jù)學號輸出學生的成績等級，平均分>=90優(yōu)秀，>=80良好，>=60及格，否則不及格首先，創(chuàng)建存儲函數(shù):4.3.2存儲過程和函數(shù)的編程功能任務必備知識mysql>delimiter$$mysql>createfunctionfunc_score_level(nochar(8))returnsvarchar(20)->readssqldata->begin->declaremsgvarchar(20);->declareavg_markdecimal(4,1)default0;->->selectavg(mark)intoavg_markfromscorewheres_no=no;->case->whenavg_mark>=90thensetmsg='優(yōu)秀';->whenavg_mark>=80thensetmsg='良好';->whenavg_mark>=60thensetmsg='及格';->elsesetmsg='不及格';->endcase;->returnmsg;->end$$mysql>delimiter;4.3.2存儲過程和函數(shù)的編程功能調(diào)用存儲函數(shù)，根據(jù)傳入的成績輸出等級：mysql>selectfunc_score_level('122003');任務必備知識三、循環(huán)控制語句1.loop語句[label:]loopstatement_listendloop[label]4.3.2存儲過程和函數(shù)的編程功能任務必備知識【任務4.3.18】創(chuàng)建存儲過程，使用loop語句求10以內(nèi)奇數(shù)之和創(chuàng)建存儲過程:mysql>delimiter$$mysql>createproceduresp_loop_sum()->begin->declareiintdefault0;->declarei_sumintdefault0;->label:loop->seti=i+1;->ifi>10thenleavelabel;4.3.2存儲過程和函數(shù)的編程功能->endif;->ifi%2=0theniteratelabel;->elseseti_sum=i_sum+i;->endif;->endlooplabel;->selecti_sum;->end$$mysql>delimiter;任務必備知識【任務4.3.18】創(chuàng)建存儲過程，使用loop語句求10以內(nèi)奇數(shù)之和調(diào)用存儲過程：mysql>callsp_loop_sum();4.3.2存儲過程和函數(shù)的編程功能任務必備知識三、循環(huán)控制語句2.while語句[label:]whilesearch_conditiondostatement_listendwhile[label]4.3.2存儲過程和函數(shù)的編程功能任務必備知識【任務4.3.19】創(chuàng)建存儲過程，使用while語句求10以內(nèi)奇數(shù)之和4.3.2存儲過程和函數(shù)的編程功能創(chuàng)建存儲過程：mysql>delimiter$$mysql>createproceduresp_while_sum()->begin->declareiintdefault0;->declarei_sumintdefault0;->->label:whilei<10do->seti=i+1;->ifi%2=1then->seti_sum=i_sum+i;->endif;->endwhilelabel;->->selecti_sum;->end$$mysql>delimiter;任務必備知識調(diào)用存儲過程：mysql>callsp_while_sum();4.3.2存儲過程和函數(shù)的編程功能【任務4.3.19】創(chuàng)建存儲過程，使用while語句求10以內(nèi)奇數(shù)之和任務必備知識四、repeat語句[label:]repeatstatement_listuntilsearch_conditionendrepeat[label]4.3.2存儲過程和函數(shù)的編程功能任務必備知識【任務4.3.20】創(chuàng)建存儲過程，使用repeat語句