教案首頁課程名稱MySQL數(shù)據(jù)庫開發(fā)本節(jié)課題任務4.3存儲過程和函數(shù)的開發(fā)授課方式理實一體化教學參考及教具姜云橋主編.MySQL數(shù)據(jù)庫開發(fā).臨科院：教務處,2023計算機MySQL數(shù)據(jù)庫軟件多媒體教學系統(tǒng)資料包教學目標及基本要求理解存儲過程了解DELIMITER命令掌握創(chuàng)建存儲過程的方法掌握修改和刪除存儲過程的方法培養(yǎng)自主學習能力教學重點教學難點創(chuàng)建、執(zhí)行、修改和刪除存儲過程創(chuàng)建存儲過程教學小結作業(yè)及要求按照任務書做好預習任務創(chuàng)建、執(zhí)行、修改和刪除存儲過程教后反思注意：用微課視頻每個典型操作做示范

教案用紙教學內(nèi)容、方法和過程附記任務4.3存儲過程和函數(shù)的開發(fā)【任務背景】銀行經(jīng)常需要計算用戶的利息，但不同類別的用戶的利率是不一樣的。這就可以將計算利率的SQL代碼寫成一個程序存放起來，用指定的用戶類別作參數(shù)。這樣的程序叫作存儲過程或者存儲函數(shù)。使用時只要調用這個存儲過程或者存儲函數(shù)，根據(jù)指定的用戶類別，就可以將不同類別用戶的利息計算出來。再如，在編制學生管理系統(tǒng)時，當某個學生某門課程的成績修改后，根據(jù)成績CJ是否高于60分更新credit表，將符合條件的學生某門課的學分累加到該生的總學分里。這是一組重復使用的一段SQL語句?？梢詫⑦@段SQL語句寫成存儲過程或存儲函數(shù)存儲在MySQL服務器中，然后再調用，就可以執(zhí)行多次重復的操作。【任務要求】本任務將從認識存儲過程著手，學習創(chuàng)建、執(zhí)行、修改和刪除存儲過程的方法。包括創(chuàng)建基本的存儲過程，創(chuàng)建帶有變量的存儲過程，創(chuàng)建帶有流程控制語句的存儲過程?！救蝿毡貍渲R】4.3.1存儲過程和函數(shù)的基本操作從MySQL5.1版本開始支持存儲過程和存儲函數(shù)。在MySQL中，可以定義一段完成特定功能的SQL語句集，經(jīng)編譯后存儲在數(shù)據(jù)庫中，用戶通過指定存儲過程的名字并給出參數(shù)（如果該存儲過程帶有參數(shù)）來執(zhí)行它，這樣的語句集稱為存儲過程。一、創(chuàng)建存儲過程創(chuàng)建存儲過程可以使用createprocedure語句。createprocedure[ifnotexists]sp_name([in|out|inout]param_nametype,...)routine_body說明:1)sp_name是存儲過程的名稱。需要在特定數(shù)據(jù)庫中創(chuàng)建存儲過程時，否則要在名稱前面加上數(shù)據(jù)庫的名稱，格式為db_name.sp_name。2)param_name和type分別表示參數(shù)名和參數(shù)的類型，當有多個參數(shù)的時候中間用逗號分隔，MySQL存儲過程支持3種類型的參數(shù)：輸入?yún)?shù)、輸出參數(shù)和輸入/輸出參數(shù)，關鍵字分別是IN、OUT和INOUT。存儲過程也可以不加參數(shù)，但是名稱后面的括號是不可省略的。3)routine_body是存儲過程體。里面包含了在過程調用的時候必須執(zhí)行的語句，這個部分總是以begin開始，以end結束。當然，當存儲過程體中只有一個SQL語句時可以省略BEGIN-END標志?！救蝿?.3.1】創(chuàng)建存儲過程sp_delete_student，用指定的學號作為參數(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ù)。mysql>delimiter$$mysql>createproceduresp_count_student(outstu_cntintunsigned)->begin->selectcount(*)intostu_cntfromstudent;->end$$mysql>delimiter;【任務4.3.3】創(chuàng)建存儲過程sp_search_teacher，以指定的系別號為參數(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;說明：在MySQL中，服務器處理語句默認是以分號(;)為結束標志的，如果有一行命令以分號(;)結束，那么按<Enter>后，MySQL將會執(zhí)行該命令。但是在存儲過程中，可能要輸入較多的語句，且語句中包含有分號。如果還以分號作為結束標志，那么執(zhí)行完第一個分號語句后，就會認為程序結束，不能再往下執(zhí)行其他語句。因此，必須將MySQL語句的結束標志修改為其他符號。這時，可以使用delimiter來改變默認結束標志，從上面示例可以看出，delimiter不屬于存儲過程的內(nèi)容，另外，最后不要忘記再將結束標志改回分號。二、調用存儲過程創(chuàng)建完存儲過程之后，調用存儲過程需要使用call語句。callsp_name(parameter,...)說明:1)sp_name為存儲過程的名稱，如果要調用某個特定數(shù)據(jù)庫的存儲過程，則需要在前面加上該數(shù)據(jù)庫的名稱。2)parameter為調用該存儲過程使用的參數(shù)，這條語句中的參數(shù)個數(shù)必須總是等于存儲過程的參數(shù)個數(shù)?！救蝿?.3.4】調用存儲過程sp_delete_student,刪除學號為122001的學生的信息。mysql>callsp_delete_student('122001');【任務4.3.5】調用存儲過程sp_count_student,統(tǒng)計學生人數(shù)。mysql>callsp_count_student(@num);mysql>select@num;++|@num|++|14|++【任務4.3.6】調用存儲過程sp_search_teacher,查詢院系編號D001的教師姓名和院系名稱。mysql>callsp_search_teacher('D001');三、查看存儲過程1.查看存儲過程的狀態(tài)信息showprocedurestatus[like'pattern'|whereexpr]說明：1)like匹配存儲過程的名稱。2)where可以指定更多的過濾條件?！救蝿?.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或者執(zhí)行下列sql語句:mysql>showprocedurestatuswherename='sp_search_teacher'\G說明：上面兩條命令輸出內(nèi)容相同，故僅顯示其中一條命令的結果，包括數(shù)據(jù)庫、存儲過程名稱、類型、創(chuàng)建者、修改時間、創(chuàng)建時間、調用權限以及字符集等信息。2.查看存儲過程的定義類似與查看數(shù)據(jù)庫和數(shù)據(jù)表的定義，存儲過程的定義可通過SHOWCREATEPROCEDURE命令查看。【任務4.3.8】查看存儲過程sp_search_teacher的定義。mysql>showcreateproceduresp_search_teacher\G四、刪除存儲過程可以使用dropprocedure刪除已經(jīng)存在的存儲過程。dropprocedure[ifexists]sp_name;說明：1)sp_name是要刪除的存儲過程的名稱。2)使用ifexists可以避免存儲過程不存在返回錯誤信息?！救蝿?.3.9】刪除存儲過程sp_search_teacher。mysql>dropproceduresp_search_teacher;五、存儲函數(shù)MySQL存儲函數(shù)和存儲過程類似，也是存儲在數(shù)據(jù)庫中的程序，其查看、刪除過程和存儲過程一樣，只不過將procedure換成function即可，主要在于創(chuàng)建和調用過程稍有不同。首先看一下創(chuàng)建過程：createfunction[ifnotexists]sp_name(param_nametype,...)returnstype[deterministic|nosql|readssqldata]routine_body說明：1)sp_name是存儲函數(shù)名稱。2)param_name是參數(shù)名稱，所有的參數(shù)都是輸入?yún)?shù)。3)type是參數(shù)或者返回值的數(shù)據(jù)類型。4)returns定義了返回值的類型。5)deterministic屬性表示這是一個確定性的存儲函數(shù)，對于相同的輸入?yún)?shù)一定會返回相同的結果；MySQL默認創(chuàng)建的是非確定性函數(shù)（notdeterministic）;6)nosql表示程序不包含任何sql語句;7)readssqldata表示程序包含讀取操作（例如select），但不會修改數(shù)據(jù)表?！救蝿?.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;存儲函數(shù)的調用與普通函數(shù)一樣，直接使用函數(shù)名，傳入必要的參數(shù)即可?！救蝿?.3.11】使用自定義的存儲函數(shù)func_count_score，獲取超過90分的成績記錄數(shù)。mysql>selectfunc_count_score(90)as'>=90記錄數(shù)';++|>=90記錄數(shù)|++|11|++【任務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.13】刪除存儲函數(shù)func_count_score。mysql>dropfunctionfunc_count_score;存儲過程和存儲函數(shù)除了參數(shù)和返回值方式不同之外，最重要的區(qū)別在于存儲過程可以修改數(shù)據(jù)庫對象的狀態(tài)，而存儲函數(shù)卻不能。這也意味這存儲函數(shù)中主要以查詢?yōu)橹鳎梢苑旁诓樵冋Z句中使用，存儲過程卻不行。不過，存儲過程的功能更加強大，能夠執(zhí)行對表的操作（比如創(chuàng)建表，刪除表,插入數(shù)據(jù)，刪除數(shù)據(jù)等）和事務操作，這些功能是存儲函數(shù)不具備的。4.3.2存儲過程和函數(shù)的編程功能一、變量存儲過程和函數(shù)可以定義和使用變量,它們可以用來存儲臨時結果。1.變量聲明用戶可以使用declare關鍵字來定義變量，這些變量的作用范圍只適用于begin…end程序段中，所以是局部變量。過程和函數(shù)使用的局部變量必須在開頭就聲明。在聲明局部變量的同時也可以為其賦一個初始值：declarevar_nametype[defaultvalue];說明：1)var_name為變量名；type為變量類型。2)default子句給變量指定一個默認值，如果不指定，默認為null。2.變量賦值給變量賦值有兩種方式，一種通過SET語句：setvar1=expr1,var2=expr2,...;另外一種方式通過selectINTO語句完成賦值：selectexpr1,expr2,...intovar1,var2,...from...;說明：select返回的表達式個數(shù)和變量的個數(shù)相同，查詢語句最多只能返回一行數(shù)據(jù)?！救蝿?.1.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;調用函數(shù)，查詢課程號A002不及格人數(shù)mysql>selectfunc_fail_count('A002')as'A002不及格人數(shù)';++|A002不及格人數(shù)|++|1|++二、條件控制語句MySQL提供了兩種條件控制語句：if語句和case語句。1.if語句IF語句可根據(jù)不同的條件執(zhí)行不同的操作。ifsearch_conditionthenstatement_listelseifsearch_conditionthenstatement_list...elsestatement_listendif說明：search_condition為判斷條件，statement_list包含一個或多個sql語句，當search_condition的條件為真時，就執(zhí)行statement_list包含的sql語句。【任務4.3.15】創(chuàng)建一個存儲過程，根據(jù)指定的參數(shù)（學號）查看某位學生的不及格科目數(shù)，如果不及格科目數(shù)超過2門（含2門），則輸出“啟動成績預警！”并輸出該生的成績單，否則輸出“成績在可控范圍”。首先，創(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;調用存儲過程，查詢學號122001的成績狀況：mysql>callproc_query('122001',@msg);mysql>select@msg;++|@msg|++|成績在可控范圍|++2.case語句case語句可以用于構造復雜的條件判斷，類似于case表達式，case語句也存在兩種形式：簡單case語句和搜索case語句：(1) 簡單case語句casecase_valuewhenwhen_valuethenstatement_listwhenwhen_valuethenstatement_list...elsestatement_listendcase;【任務4.3.16】創(chuàng)建一個存儲過程，根據(jù)專業(yè)類型（專業(yè)課4分、專業(yè)基礎課3分、必須課2分、選修課1分）更新學分，參數(shù)為專業(yè)類型。首先，創(chuàng)建存儲過程（不能創(chuàng)建函數(shù)，函數(shù)中不能更新表格數(shù)據(jù)）mysql>delimiter$$mysql>createprocedureproc_update_credit(intypevarchar(10))->begin->casetype->when'專業(yè)課'then->updatecoursesetc_credit=4wherec_type=type;->when'專業(yè)基礎課'then->updatecoursesetc_credit=3wherec_type=type;->when'必修課'then->updatecoursesetc_credit=2wherec_type=type;->else->updatecoursesetc_credit=1;->endcase;->end$$mysql>delimiter;調用存儲過程，更新必修課學分。mysql>callproc_update_credit('必修課');(2) 搜索case語句casewhensearch_conditionthenstatement_listwhensearch_conditionthenstatement_list...elsestatement_listendcase;【任務4.3.17】創(chuàng)建一個存儲函數(shù)，根據(jù)學號輸出學生的成績等級，平均分>=90優(yōu)秀，>=80良好，>=60及格，否則不及格。首先，創(chuàng)建存儲函數(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;調用存儲函數(shù)，根據(jù)傳入的成績輸出等級：mysql>selectfunc_score_level('122003');++|func_score_level('122003')|++|及格|++三、 循環(huán)控制語句1.loop語句[label:]loopstatement_listendloop[label]說明：重復執(zhí)行statement_list直到循環(huán)被終止，也就是說退出循環(huán)的方式只能由執(zhí)行語句實現(xiàn)，主要包括兩種方式，一種使用leavelabel語句退出循環(huán)，另外一種使用函數(shù)的RETURN語句退出整個函數(shù)?！救蝿?.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;->endif;->ifi%2=0theniteratelabel;->elseseti_sum=i_sum+i;->endif;->endlooplabel;->selecti_sum;->end$$mysql>delimiter;調用存儲過程：mysql>callsp_loop_sum();++|i_sum|++|25|++2.while語句[label:]whilesearch_conditiondostatement_listendwhile[label]說明：先判斷search_condition是否為true；是則執(zhí)行statement_list語句列表，否則退出循環(huán)語句。因此，while語句可能一次也不執(zhí)行，類似Java中的while循環(huán)?！救蝿?.3.19】創(chuàng)建存儲過程，使用while語句求10以內(nèi)奇數(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;調用存儲過程：mysql>callsp_while_sum();++|i_sum|++|25|++四、repeat語句[label:]repeatstatement_list