《MySQL數(shù)據(jù)庫(kù)原理與應(yīng)用案例教程》 課件 項(xiàng)目8 存儲(chǔ)過(guò)程

《MySQL數(shù)據(jù)庫(kù)項(xiàng)目化教程》項(xiàng)目8存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程是一組為了完成特定功能的SQL語(yǔ)句集，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中，用戶通過(guò)指定存儲(chǔ)過(guò)程的名字并給定參數(shù)（如果該存儲(chǔ)過(guò)程帶有參數(shù)）來(lái)調(diào)用執(zhí)行它。游標(biāo)就是按照指定要求把數(shù)據(jù)從數(shù)據(jù)庫(kù)中提取出形成相應(yīng)的數(shù)據(jù)集，然后逐條進(jìn)行數(shù)據(jù)處理。本項(xiàng)目通過(guò)典型任務(wù)學(xué)習(xí)存儲(chǔ)過(guò)程的創(chuàng)建、查看、調(diào)用、修改和刪除操作，復(fù)雜的存儲(chǔ)過(guò)程的編寫(xiě)，以及創(chuàng)建游標(biāo)、使用游標(biāo)循環(huán)遍歷查詢到的結(jié)果集。前言掌握如何創(chuàng)建無(wú)參數(shù)、有參數(shù)存儲(chǔ)過(guò)程。掌握如何查看、調(diào)用、修改和刪除存儲(chǔ)過(guò)程。掌握復(fù)雜的存儲(chǔ)過(guò)程的編寫(xiě)。掌握如何創(chuàng)建游標(biāo)、使用游標(biāo)循環(huán)遍歷查詢到的結(jié)果集。學(xué)習(xí)目標(biāo)知識(shí)重點(diǎn)創(chuàng)建無(wú)參數(shù)、有參數(shù)存儲(chǔ)過(guò)程。查看、調(diào)用、修改和刪除存儲(chǔ)過(guò)程。知識(shí)難點(diǎn)復(fù)雜的存儲(chǔ)過(guò)程的編寫(xiě)。聲明游標(biāo)、打開(kāi)游標(biāo)、讀取游標(biāo)和關(guān)閉游標(biāo)。知識(shí)重點(diǎn)與難點(diǎn)創(chuàng)建、調(diào)用和管理存儲(chǔ)過(guò)程任務(wù)1任務(wù)2維護(hù)數(shù)據(jù)表學(xué)習(xí)任務(wù)外鍵約束任務(wù)3創(chuàng)建、調(diào)用和管理存儲(chǔ)過(guò)程任務(wù)1任務(wù)描述任務(wù)目標(biāo)任務(wù)分析任務(wù)實(shí)施123知識(shí)鏈接45

在實(shí)際工作過(guò)程中，數(shù)據(jù)庫(kù)開(kāi)發(fā)人員經(jīng)常需要重復(fù)編寫(xiě)相同的SQL語(yǔ)句，可以通過(guò)創(chuàng)建存儲(chǔ)過(guò)程，將這些相同的SQL語(yǔ)句封裝成一個(gè)代碼塊，然后直接調(diào)用這些存儲(chǔ)過(guò)程來(lái)執(zhí)行已經(jīng)定義好的SQL語(yǔ)句。從而減少數(shù)據(jù)庫(kù)開(kāi)發(fā)人員的工作量，提高工作效率。并且，存儲(chǔ)過(guò)程是在MySQL服務(wù)器中存儲(chǔ)和執(zhí)行，可以減少客戶端和服務(wù)器端的數(shù)據(jù)傳輸。根據(jù)需求，完成存儲(chǔ)過(guò)程的創(chuàng)建、查看、調(diào)用、修改和刪除操作。任務(wù)描述（1）會(huì)創(chuàng)建無(wú)參數(shù)存儲(chǔ)過(guò)程。（2）會(huì)創(chuàng)建帶in、out、inout參數(shù)的存儲(chǔ)過(guò)程。（3）會(huì)查看、調(diào)用存儲(chǔ)過(guò)程。（4）會(huì)修改、刪除存儲(chǔ)過(guò)程。（5）通過(guò)使用存儲(chǔ)過(guò)程，培養(yǎng)學(xué)生在工作過(guò)程中應(yīng)具備提高工作效率的意識(shí)。任務(wù)目標(biāo)學(xué)習(xí)使用存儲(chǔ)過(guò)程的場(chǎng)景，以及創(chuàng)建、查看、調(diào)用、修改和刪除存儲(chǔ)過(guò)程的語(yǔ)句，完成無(wú)參數(shù)存儲(chǔ)過(guò)程、有參數(shù)存儲(chǔ)過(guò)程的創(chuàng)建，以及查看、調(diào)用、修改和刪除存儲(chǔ)過(guò)程等各種操作。任務(wù)分析存儲(chǔ)過(guò)程的優(yōu)點(diǎn)創(chuàng)建存儲(chǔ)過(guò)程delimiter語(yǔ)句存儲(chǔ)過(guò)程任務(wù)實(shí)施查看存儲(chǔ)過(guò)程調(diào)用存儲(chǔ)過(guò)程修改存儲(chǔ)過(guò)程刪除存儲(chǔ)過(guò)程步驟1創(chuàng)建無(wú)參數(shù)存儲(chǔ)過(guò)程（1）創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，名稱為pro_stu，功能為完成在tb_student數(shù)據(jù)表中查詢計(jì)算機(jī)應(yīng)用專業(yè)的學(xué)生的學(xué)號(hào)、姓名和專業(yè)。mysql>usechjgl_db;mysql>delimiter//mysql>createprocedurepro_stu()->begin->selectsnoas學(xué)號(hào),snameas姓名,sspecialtyas專業(yè)fromtb_studentwheresspecialty='計(jì)算機(jī)應(yīng)用';->end//mysql>delimiter;任務(wù)實(shí)施任務(wù)實(shí)施小貼士：在創(chuàng)建存儲(chǔ)過(guò)程中，一般首先使用delimiter命令臨時(shí)將語(yǔ)句結(jié)束標(biāo)志修改為其他字符，比如，執(zhí)行“delimiter//”這條命令后，sql語(yǔ)句的結(jié)束符就換成了雙斜杠符號(hào)“//”了。并且在存儲(chǔ)過(guò)程創(chuàng)建之后再將語(yǔ)句結(jié)束符修改為默認(rèn)的“;”。（2）調(diào)用存儲(chǔ)過(guò)程pro_stu。mysql>callpro_stu;任務(wù)實(shí)施步驟2創(chuàng)建帶in參數(shù)的存儲(chǔ)過(guò)程（1）創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，名稱為pro_grade，輸入?yún)?shù)為指定的分?jǐn)?shù)，功能為完成在tb_grade數(shù)據(jù)表中查詢綜合成績(jī)大于指定分?jǐn)?shù)的學(xué)生的成績(jī)單。mysql>delimiter//mysql>createprocedurepro_grade(inscorefloat)->begin->select*fromtb_gradewheretotalgrade>score;->end//mysql>delimiter;任務(wù)實(shí)施任務(wù)實(shí)施（2）調(diào)用存儲(chǔ)過(guò)程pro_grade。mysql>callpro_grade(95);執(zhí)行結(jié)果顯示如圖所示。任務(wù)實(shí)施步驟3創(chuàng)建帶out參數(shù)的存儲(chǔ)過(guò)程（1）創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，名稱為pro_grade1，輸入?yún)?shù)為指定的分?jǐn)?shù)，功能為完成在tb_grade數(shù)據(jù)表中查詢綜合成績(jī)低于指定分?jǐn)?shù)的學(xué)生的人數(shù)，符合條件的學(xué)生人數(shù)通過(guò)存儲(chǔ)過(guò)程的out參數(shù)返回。mysql>delimiter//mysql>createprocedurepro_grade1(inscorefloat,outtotalint)->begin->selectcount(*)intototalfromtb_gradewheretotalgrade<score;->end//mysql>delimiter;任務(wù)實(shí)施任務(wù)實(shí)施（2）調(diào)用存儲(chǔ)過(guò)程pro_grade1。mysql>callpro_grade1(80,@total);mysql>select@total;任務(wù)實(shí)施步驟4創(chuàng)建帶inout參數(shù)的存儲(chǔ)過(guò)程（1）創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，名稱為pro_stu1，輸入?yún)?shù)為專業(yè)名稱、需要增加的人數(shù)，并將在該專業(yè)的學(xué)生的人數(shù)加上需要增加的人數(shù)，返回增加后的人數(shù)。mysql>delimiter//mysql>createprocedurepro_stu1(inspecialtyvarchar(20),inouttotalint)->begin->selectcount(*)+totalintototalfromtb_studentwheresspecialty=specialty;->end//mysql>delimiter;任務(wù)實(shí)施任務(wù)實(shí)施（2）調(diào)用存儲(chǔ)過(guò)程pro_stu1。mysql>set@total=10;mysql>callpro_stu1('計(jì)算機(jī)應(yīng)用',@total);mysql>select@total;任務(wù)實(shí)施步驟5查看存儲(chǔ)過(guò)程（1）查看存儲(chǔ)過(guò)程pro_stu的狀態(tài)。mysql>showprocedurestatuslike'pro_stu'\G;任務(wù)實(shí)施（2）查看存儲(chǔ)過(guò)程pro_stu的定義。mysql>showcreateprocedurepro_stu\G;任務(wù)實(shí)施步驟6修改存儲(chǔ)過(guò)程修改存儲(chǔ)過(guò)程pro_stu的定義，將讀寫(xiě)權(quán)限修改為MODIFIESSQLDATA。并查看修改后的結(jié)果。mysql>alterprocedurepro_stumodifiessqldata;mysql>showcreateprocedurepro_stu\G;任務(wù)實(shí)施任務(wù)實(shí)施步驟7刪除存儲(chǔ)過(guò)程刪除存儲(chǔ)過(guò)程pro_grade1。mysql>dropprocedureifexistspro_grade1;任務(wù)實(shí)施存儲(chǔ)過(guò)程是一組為了完成特定功能的SQL語(yǔ)句集合。使用存儲(chǔ)過(guò)程的目的是將常用或復(fù)雜的工作預(yù)先用SQL語(yǔ)句寫(xiě)好并用一個(gè)指定名稱存儲(chǔ)起來(lái)，這個(gè)過(guò)程經(jīng)編譯和優(yōu)化后存儲(chǔ)在數(shù)據(jù)庫(kù)服務(wù)器中。當(dāng)以后需要數(shù)據(jù)庫(kù)提供與已定義好的存儲(chǔ)過(guò)程的功能相同的服務(wù)時(shí)，只需調(diào)用存儲(chǔ)過(guò)程即可自動(dòng)完成。存儲(chǔ)過(guò)程在數(shù)據(jù)庫(kù)中創(chuàng)建并保存，一般由SQL語(yǔ)句和一些特殊的控制結(jié)構(gòu)組成。適用于在不同的應(yīng)用程序或平臺(tái)上執(zhí)行相同的特定功能時(shí)調(diào)用。知識(shí)鏈接——存儲(chǔ)過(guò)程（1）一次創(chuàng)建，多次調(diào)用：存儲(chǔ)過(guò)程可以重復(fù)使用，從而可以減少開(kāi)發(fā)人員的工作量。（2）減少網(wǎng)絡(luò)流量：存儲(chǔ)過(guò)程存儲(chǔ)在數(shù)據(jù)庫(kù)服務(wù)器中，調(diào)用的時(shí)候只需要傳遞存儲(chǔ)過(guò)程的名稱以及參數(shù)就可以了，因此降低了網(wǎng)絡(luò)傳輸?shù)臄?shù)據(jù)量。（3）很強(qiáng)的靈活性，存儲(chǔ)過(guò)程可以使用流控制語(yǔ)句編寫(xiě)，有很強(qiáng)的靈活性，可以完成復(fù)雜的判斷和較復(fù)雜的運(yùn)算。知識(shí)鏈接——存儲(chǔ)過(guò)程的優(yōu)點(diǎn)CREATEPROCEDURE：創(chuàng)建存儲(chǔ)過(guò)程關(guān)鍵字。sp_name：要?jiǎng)?chuàng)建的存儲(chǔ)過(guò)程名稱。[IN|OUT|INOUT]param_nametype：表示存儲(chǔ)過(guò)程的參數(shù)。其中，IN表示輸入?yún)?shù)；OUT表示輸出參數(shù)；INOUT表示即可以是輸入?yún)?shù)，也可以是輸出參數(shù)；param_nam表示存儲(chǔ)過(guò)程參數(shù)名稱，type指定存儲(chǔ)過(guò)程的參數(shù)的數(shù)據(jù)類型。存儲(chǔ)過(guò)程也可以不加參數(shù)，但是存儲(chǔ)過(guò)程名稱后面的括號(hào)是不可以省略的。characteristic：指定存儲(chǔ)過(guò)程的特性。有以下幾種情況：（1）COMMENT'string'：表明存儲(chǔ)過(guò)程的注釋信息，注釋信息要寫(xiě)到單引號(hào)里，只能寫(xiě)單行注釋。知識(shí)鏈接——?jiǎng)?chuàng)建存儲(chǔ)過(guò)程（2）LANGUAGESQL：聲明存儲(chǔ)過(guò)程中使用的語(yǔ)言，默認(rèn)是SQL。（3）[NOT]DETERMINISTIC：指明存儲(chǔ)過(guò)程執(zhí)行的結(jié)果是否確定。DETERMINISTIC表示結(jié)果是確定的。即每次執(zhí)行存儲(chǔ)過(guò)程時(shí)，相同的輸入會(huì)得到相同的輸出；NOTDETERMINISTIC表示結(jié)果是不確定的，即相同的輸入可能輸出不同。如果不指定任意一個(gè)值，默認(rèn)為NOTDETERMINISTIC。（4）{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}：指明子程序使用SQL語(yǔ)句的限制。其中，CONTAINSSQL表明子程序包含SQL語(yǔ)句，但是不包含讀寫(xiě)數(shù)據(jù)的語(yǔ)句；NOSQL表明子程序不包含SQL語(yǔ)句；READSSQLDATA：說(shuō)明子程序包含讀數(shù)據(jù)的語(yǔ)句；MODIFIESSQLDATA表明子程序包含寫(xiě)數(shù)據(jù)的語(yǔ)句。默認(rèn)情況下，系統(tǒng)會(huì)指定為CONTAINSSQL。知識(shí)鏈接——?jiǎng)?chuàng)建存儲(chǔ)過(guò)程（5）SQLSECURITY{DEFINER|INVOKER}：指明誰(shuí)有權(quán)限來(lái)執(zhí)行存儲(chǔ)過(guò)程。其中，DEFINER表示只有存儲(chǔ)過(guò)程的定義者才能夠執(zhí)行存儲(chǔ)過(guò)程。INVOKER表示存儲(chǔ)過(guò)程的調(diào)用者可以執(zhí)行。routine_body：存儲(chǔ)過(guò)程體，是SQL語(yǔ)句的內(nèi)容，用“begin...end”來(lái)標(biāo)注SQL語(yǔ)句的開(kāi)始和結(jié)束。若存儲(chǔ)過(guò)程體中只有一條SQL語(yǔ)句，則可以省略“begin...end”標(biāo)志。知識(shí)鏈接——?jiǎng)?chuàng)建存儲(chǔ)過(guò)程知識(shí)鏈接——delimiter語(yǔ)句MySQL中，服務(wù)器處理SQL語(yǔ)句默認(rèn)是以分號(hào)作為語(yǔ)句結(jié)束標(biāo)志的。然而，在創(chuàng)建存儲(chǔ)過(guò)程時(shí)，存儲(chǔ)過(guò)程體可能包含有多條SQL語(yǔ)句，這些SQL語(yǔ)句如果仍以分號(hào)作為語(yǔ)句結(jié)束符，那么MySQL服務(wù)器在處理時(shí)會(huì)以遇到的第一條SQL語(yǔ)句結(jié)尾處的分號(hào)作為整個(gè)程序的結(jié)束標(biāo)志，而不再去處理存儲(chǔ)過(guò)程體中后面的SQL語(yǔ)句，這樣顯然不行。delimiter語(yǔ)句的作用就是將MySQL語(yǔ)句的結(jié)束標(biāo)志修改為其他的符號(hào)。語(yǔ)法格式為：$$：用戶定義的結(jié)束標(biāo)志。語(yǔ)法分析：SHOWPROCEDURESTATUS：查看存儲(chǔ)過(guò)程關(guān)鍵字。LIKE'pattern'：用來(lái)匹配存儲(chǔ)過(guò)程名稱。知識(shí)鏈接——查看存儲(chǔ)過(guò)程CALL：調(diào)用存儲(chǔ)過(guò)程關(guān)鍵字。sp_name：表示存儲(chǔ)過(guò)程名稱。parameter：表示存儲(chǔ)過(guò)程的參數(shù)。知識(shí)鏈接——調(diào)用存儲(chǔ)過(guò)程MySQL針對(duì)存儲(chǔ)工程的修改功能只提供了修改存儲(chǔ)過(guò)程特性，不提供修改存儲(chǔ)過(guò)程體，如果需要修改存儲(chǔ)過(guò)程，則需要先刪除存儲(chǔ)過(guò)程，再創(chuàng)建同名的存儲(chǔ)過(guò)程。修改存儲(chǔ)過(guò)程的基本語(yǔ)法結(jié)構(gòu)為：知識(shí)鏈接——修改存儲(chǔ)過(guò)程PROCEDURE：修改存儲(chǔ)過(guò)程關(guān)鍵字。sp_name：存儲(chǔ)過(guò)程名。characteristic：指定存儲(chǔ)過(guò)程的特性。語(yǔ)法分析：DROPPROCEDURE：刪除存儲(chǔ)過(guò)程關(guān)鍵字。[IFEXISTS]：如果存儲(chǔ)過(guò)程不存在，刪除時(shí)可以防止發(fā)生錯(cuò)誤。sp_name：表示要?jiǎng)h除的存儲(chǔ)過(guò)程名稱。知識(shí)鏈接——?jiǎng)h除存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程的高級(jí)應(yīng)用任務(wù)2任務(wù)描述任務(wù)目標(biāo)任務(wù)分析任務(wù)實(shí)施123知識(shí)鏈接45

在實(shí)際工作過(guò)程中，使用存儲(chǔ)過(guò)程的場(chǎng)合一般是比較復(fù)雜的業(yè)務(wù)邏輯，需要使用流程控制語(yǔ)句來(lái)控制程序的流程。MySQL中流程控制語(yǔ)句有：if語(yǔ)句、case語(yǔ)句、loop語(yǔ)句、leave語(yǔ)句、iterate語(yǔ)句、repeat語(yǔ)句和while語(yǔ)句等。根據(jù)需求，完成復(fù)雜的存儲(chǔ)過(guò)程的編寫(xiě)。任務(wù)描述（1）會(huì)定義變量。（2）會(huì)為變量賦值。（3）會(huì)使用條件語(yǔ)句：if語(yǔ)句、case語(yǔ)句。（4）會(huì)使用循環(huán)語(yǔ)句：loop語(yǔ)句、repeat語(yǔ)句和while語(yǔ)句等。（5）通過(guò)完成復(fù)雜的存儲(chǔ)過(guò)程的編寫(xiě)，培養(yǎng)學(xué)生用系統(tǒng)思考的方法分析解決復(fù)雜問(wèn)題的能力。任務(wù)目標(biāo)學(xué)習(xí)MySQL變量的定義與賦值，掌握流程控制語(yǔ)句if語(yǔ)句、case語(yǔ)句、loop語(yǔ)句、leave語(yǔ)句、iterate語(yǔ)句、repeat語(yǔ)句和while語(yǔ)句等的基本語(yǔ)法格式，完成復(fù)雜的存儲(chǔ)過(guò)程的編寫(xiě)。任務(wù)分析為變量賦值任務(wù)實(shí)施定義變量循環(huán)語(yǔ)句條件語(yǔ)句步驟1存儲(chǔ)過(guò)程的條件語(yǔ)句（1）創(chuàng)建存儲(chǔ)過(guò)程pro_stuavg，查詢某專業(yè)學(xué)生的綜合成績(jī)的平均分，如果平均分大于等于80分，輸出“本次考試成績(jī)優(yōu)秀”，否則輸出“本次考試成績(jī)一般”。調(diào)用存儲(chǔ)過(guò)程，輸入?yún)?shù)“計(jì)算機(jī)應(yīng)用”進(jìn)行測(cè)試。mysql>usechjgl_db;mysql>delimiter//mysql>createprocedurepro_stuavg(inspecialtyvarchar(20))->begin->declaregavgfloat;->selectround(avg(g.totalgrade),2)into@gavgfromtb_studentsinnerjointb_gradegons.sno=g.snowheres.sspecialty=specialty;任務(wù)實(shí)施->If(@gavg>=80)then->select'本次考試成績(jī)優(yōu)秀'asgrade;->else->select'本次考試成績(jī)一般'asgrade;->endif;->end//mysql>delimiter;mysql>callpro_stuavg('計(jì)算機(jī)應(yīng)用');任務(wù)實(shí)施任務(wù)實(shí)施任務(wù)實(shí)施（2）創(chuàng)建存儲(chǔ)過(guò)程pro_level，完成統(tǒng)計(jì)各專業(yè)學(xué)生的綜合成績(jī)平均成績(jī)優(yōu)秀（90分-100分）、良好（76分-89分）、及格（60分-75分）和不及格（0分-59分）的人數(shù)。調(diào)用存儲(chǔ)過(guò)程，輸入?yún)?shù)“良好”進(jìn)行測(cè)試。mysql>delimiter//mysql>createprocedurepro_level(inlevelvarchar(20))->begin->caselevel->when'優(yōu)秀'then->selects.sspecialtyas專業(yè),count(*)as優(yōu)秀人數(shù)fromtb_studentsinnerjointb_gradegons.sno=g.snogroupbys.sspecialtyhavingavg(g.totalgrade)>=90;任務(wù)實(shí)施->when'良好'then->selects.sspecialtyas專業(yè),count(*)as良好人數(shù)fromtb_studentsinnerjointb_gradegons.sno=g.snogroupbys.sspecialtyhavingavg(g.totalgrade)<90andavg(g.totalgrade)>75;->when'及格'then->selects.sspecialtyas專業(yè),count(*)as及格人數(shù)fromtb_studentsinnerjointb_gradegons.sno=g.snogroupbys.sspecialtyhavingavg(g.totalgrade)<=75andavg(g.totalgrade)>60;->when'不及格'then->selects.sspecialtyas專業(yè),count(*)as不及格人數(shù)fromtb_studentsinnerjointb_gradegons.sno=g.snogroupbys.sspecialtyhavingavg(g.totalgrade)<60;任務(wù)實(shí)施->endcase;->end//mysql>delimiter;mysql>callpro_level('良好');任務(wù)實(shí)施步驟2存儲(chǔ)過(guò)程的循環(huán)語(yǔ)句（1）創(chuàng)建存儲(chǔ)過(guò)程pro_while，使用while循環(huán)語(yǔ)句，計(jì)算n！，并調(diào)用存儲(chǔ)過(guò)程進(jìn)行驗(yàn)證。mysql>delimiter//mysql>createprocedurepro_while(innint,outresultint)->begin->setresult=1;->whilen>1do->setresult=n*result;->setn=n-1;->endwhile;任務(wù)實(shí)施->end//mysql>delimiter;mysql>callpro_while(5,@result);mysql>select'5!=',@result;任務(wù)實(shí)施任務(wù)實(shí)施（2）創(chuàng)建存儲(chǔ)過(guò)程pro_repeat，使用repeat循環(huán)語(yǔ)句，計(jì)算n！，并調(diào)用存儲(chǔ)過(guò)程進(jìn)行驗(yàn)證。mysql>delimiter//mysql>createprocedurepro_repeat(innint,outresultint)->begin->setresult=1;->repeat->setresult=n*result;->setn=n-1;->untiln<=1->endrepeat;

任務(wù)實(shí)施->end//mysql>delimiter;mysql>callpro_repeat(6,@result);mysql>select'6!=',@result;任務(wù)實(shí)施（3）創(chuàng)建存儲(chǔ)過(guò)程pro_loop，使用loop循環(huán)語(yǔ)句，計(jì)算n！，并調(diào)用存儲(chǔ)過(guò)程進(jìn)行驗(yàn)證。mysql>delimiter//mysql>createprocedurepro_loop(innint,outresultint)->begin->setresult=1;->label:loop->setresult=n*result;->setn=n-1;->ifn<=1then->leavelabel;任務(wù)實(shí)施->endif;->endlooplabel;->end//mysql>delimiter;mysql>callpro_loop(5,@result);mysql>select'5!=',@result;任務(wù)實(shí)施任務(wù)實(shí)施任務(wù)實(shí)施（4）創(chuàng)建存儲(chǔ)過(guò)程pro_loop1，使用loop循環(huán)語(yǔ)句，輸出n以內(nèi)的偶數(shù)，并調(diào)用存儲(chǔ)過(guò)程進(jìn)行驗(yàn)證。mysql>delimiter//mysql>createprocedurepro_loop1(innint)->begin->declareiint;->declarestrvarchar(255);->seti=1;->setstr='';->label:loop任務(wù)實(shí)施->ifi>=nthen->leavelabel;->endif;->seti=i+1;->if(imod2)then->iteratelabel;->else->setstr=concat(str,i,',');->endif;->endloop;->selectstr;->end//mysql>delimiter;mysql>callpro_loop1(20);任務(wù)實(shí)施在創(chuàng)建存儲(chǔ)過(guò)程中，有時(shí)會(huì)使用變量來(lái)保存數(shù)據(jù)處理過(guò)程中的值。MySQL中可以使用DECLARE關(guān)鍵字來(lái)定義變量，其基本語(yǔ)法格式如下：知識(shí)鏈接——定義變量DECLARE：定義變量關(guān)鍵字。var_name：變量名。type：變量的數(shù)據(jù)類型。[DEFAULTvalue]：定義變量的默認(rèn)值。沒(méi)有使用DEFAULT子句時(shí)，默認(rèn)值為NULL。MySQL中可以使用SET關(guān)鍵字來(lái)為變量賦值，一個(gè)SET語(yǔ)句可以同時(shí)為多個(gè)變量賦值，各個(gè)變量的賦值語(yǔ)句之間用逗號(hào)隔開(kāi)。其基本語(yǔ)法格式如下：知識(shí)鏈接——為變量賦值語(yǔ)法分析：SET：為變量賦值關(guān)鍵字。variable：變量名。expr：賦值表達(dá)式。（1）IF語(yǔ)句IF語(yǔ)句用來(lái)進(jìn)行條件判斷，根據(jù)是否滿足條件（可包含多個(gè)條件），將執(zhí)行不同的語(yǔ)句，是流程控制中最常用的條件判斷語(yǔ)句。其基本語(yǔ)法格式如下：知識(shí)鏈接——條件語(yǔ)句search_condition：表示條件判斷語(yǔ)句，如果返回值為TRUE，相應(yīng)的statement_list中的語(yǔ)句被執(zhí)行；如果返回值為FALSE，則ELSE關(guān)鍵字后面statement_list中的語(yǔ)句被執(zhí)行。statement_list：SQL語(yǔ)句列表，可以包括一個(gè)或多個(gè)語(yǔ)句。（2）case語(yǔ)句CASE語(yǔ)句也是用來(lái)進(jìn)行條件判斷的，它提供了多個(gè)條件進(jìn)行選擇，可以實(shí)現(xiàn)比IF語(yǔ)句更復(fù)雜的條件判斷。CASE語(yǔ)句有兩種語(yǔ)法格式，第一種其基本語(yǔ)法格式如下：知識(shí)鏈接——條件語(yǔ)句case_value：表示條件判斷的變量，決定了哪一個(gè)WHEN子句會(huì)被執(zhí)行；when_value：表示變量的取值，如果某個(gè)when_value的值與case_value變量的值相同，則執(zhí)行對(duì)應(yīng)的THEN關(guān)鍵字后的statement_list中的語(yǔ)句；如果沒(méi)有條件匹配，ELSE子句里的語(yǔ)句被執(zhí)行。知識(shí)鏈接——條件語(yǔ)句statement_list：SQL語(yǔ)句列表，可以包括一個(gè)或多個(gè)語(yǔ)句。CASE語(yǔ)句都要使用ENDCASE結(jié)束。第二種其基本語(yǔ)法格式如下：search_condition：表示條件判斷語(yǔ)句。statement_list：SQL語(yǔ)句列表，可以包括一個(gè)或多個(gè)語(yǔ)句。該語(yǔ)句中的WHEN語(yǔ)句將被逐個(gè)執(zhí)行，直到某個(gè)search_condition表達(dá)式為真，則執(zhí)行對(duì)應(yīng)THEN關(guān)鍵字后面的statement_list中的語(yǔ)句。如果沒(méi)有條件匹配，ELSE子句里的語(yǔ)句被執(zhí)行。（1）while語(yǔ)句while語(yǔ)句是有條件控制的循環(huán)語(yǔ)句，當(dāng)滿足條件時(shí)，執(zhí)行循環(huán)內(nèi)的語(yǔ)句，否則退出循環(huán)。其基本語(yǔ)法格式如下：知識(shí)鏈接——循環(huán)語(yǔ)句search_condition：表示循環(huán)執(zhí)行的條件，滿足該條件時(shí)循環(huán)執(zhí)行。statement_list：循環(huán)的SQL執(zhí)行語(yǔ)句。WHILE循環(huán)需要使用ENDWHILE來(lái)結(jié)束。（2）repeat語(yǔ)句repeat語(yǔ)句是有條件控制的循環(huán)語(yǔ)句，每次語(yǔ)句執(zhí)行完畢后，會(huì)對(duì)條件表達(dá)式進(jìn)行判斷，如果表達(dá)式返回值為TRUE，則循環(huán)結(jié)束，否則重復(fù)執(zhí)行循環(huán)中的語(yǔ)句。其基本語(yǔ)法格式如下：知識(shí)鏈接——循環(huán)語(yǔ)句search_condition：表示循環(huán)執(zhí)行的條件，滿足該條件時(shí)循環(huán)執(zhí)行。statement_list：循環(huán)的SQL執(zhí)行語(yǔ)句。WHILE循環(huán)需要使用ENDWHILE來(lái)結(jié)束。（3）loop語(yǔ)句使某些特定的語(yǔ)句重復(fù)執(zhí)行。LOOP語(yǔ)句只實(shí)現(xiàn)一個(gè)簡(jiǎn)單的循環(huán)，并不進(jìn)行條件判斷。LOOP語(yǔ)句本身沒(méi)有停止循環(huán)的語(yǔ)句，必須使用LEAVE語(yǔ)句等才能停止循環(huán)，跳出循環(huán)過(guò)程。其基本語(yǔ)法格式如下：知識(shí)鏈接——循環(huán)語(yǔ)句begin_label和end_label：分別表示循環(huán)開(kāi)始和結(jié)束的標(biāo)志，這兩個(gè)標(biāo)志必須相同，而且都可以省略。statement_list：表示需要循環(huán)執(zhí)行的語(yǔ)句。（4）leave語(yǔ)句主要用于跳出循環(huán)控制。其基本語(yǔ)法格式如下：知識(shí)鏈接——循環(huán)語(yǔ)句label：表示循環(huán)的標(biāo)志。（5）iterate語(yǔ)句iterate是“再次循環(huán)”的意思，用來(lái)跳出本次循環(huán)，直接進(jìn)入下一次循環(huán)。其基本語(yǔ)法格式如下：小貼士：LEAVE語(yǔ)句和ITERATE語(yǔ)句都用來(lái)跳出循環(huán)語(yǔ)句，但兩者的功能是不一樣的。LEAVE語(yǔ)句是跳出整個(gè)循環(huán)，然后執(zhí)行循環(huán)后面的程序。而ITERATE語(yǔ)句是跳出本次循環(huán)，然后進(jìn)入下一次循環(huán)。游標(biāo)任務(wù)3任務(wù)描述任務(wù)目標(biāo)任務(wù)分析任務(wù)實(shí)施123知識(shí)鏈接45

在實(shí)際工作過(guò)程中，經(jīng)常會(huì)遇到這樣一種情況，需要對(duì)存儲(chǔ)過(guò)程或存儲(chǔ)函數(shù)中查詢的結(jié)果進(jìn)行遍歷操作，并對(duì)遍歷到的每一條數(shù)據(jù)進(jìn)行處理，這時(shí)候就會(huì)使用到游標(biāo)。根據(jù)需求，創(chuàng)建游標(biāo)，循環(huán)遍歷查詢到的結(jié)果集。任務(wù)描述（1）會(huì)聲明游標(biāo)、打開(kāi)游標(biāo)、讀取游標(biāo)和關(guān)閉游標(biāo)。（2）會(huì)循環(huán)遍歷查詢到的結(jié)果集。（3）會(huì)聲明處理程序。（4）通過(guò)使用游標(biāo)，培養(yǎng)學(xué)生追求卓越的精神。任務(wù)目標(biāo)學(xué)習(xí)游標(biāo)的作用，以及聲明游標(biāo)、打開(kāi)游標(biāo)、讀取游標(biāo)和關(guān)閉游標(biāo)的語(yǔ)句，了解如何聲明處理程序，使用游標(biāo)和循環(huán)語(yǔ)句完成循環(huán)遍歷查詢到的結(jié)果集。任務(wù)分析聲明游標(biāo)打開(kāi)游標(biāo)使用游標(biāo)游標(biāo)任務(wù)實(shí)施關(guān)閉游標(biāo)聲明處理程序步驟1創(chuàng)建一個(gè)簡(jiǎn)單的游標(biāo)（1）在數(shù)據(jù)庫(kù)chjgl_db中創(chuàng)建數(shù)據(jù)表tb_department（系部），數(shù)據(jù)表結(jié)構(gòu)如表4-7。任務(wù)實(shí)施mysql>usechjgl_db;mysql>delimiter//mysql>createprocedurepro_cur1()->begin->declarerow_snovarchar(20);->declarerow_snamevarchar(20);->declarerow_specialtyvarchar(20);->declarecur_stucursorforselectsnoas學(xué)號(hào),snameas姓名,sspecialtyas專業(yè)fromtb_studentwheresspecialty='計(jì)算機(jī)應(yīng)用';

任務(wù)實(shí)施->opencur_stu;->fetchcur_stuintorow_sno,row_sname,row_specialty;->selectrow_sno,row_sname,row_specialty;->closecur_stu;->end//mysql>delimiter;mysql>callpro_cur1();任務(wù)實(shí)施步驟2循環(huán)遍歷查詢到的結(jié)果集創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，名稱為pro_cur2，功能為完成在tb_student數(shù)據(jù)表中查詢計(jì)算機(jī)應(yīng)用專業(yè)的學(xué)生的學(xué)號(hào)、姓名和專業(yè)，并創(chuàng)建游標(biāo)cur_stu，循環(huán)遍歷查詢到的結(jié)果集。（1）使用計(jì)數(shù)器來(lái)控制循環(huán)mysql>delimiter//mysql>createprocedurepro_cur2()->begin->declarenintdefault0;->declareiintdefault0;->declarerow_snovarchar(20);

任務(wù)實(shí)施->declarerow_snamevarchar(20);->declarerow_specialtyvarchar(20);->declarecur_stucursorforselectsnoas學(xué)號(hào),snameas姓名,sspecialtyas專業(yè)fromtb_studentwheresspecialty='計(jì)算機(jī)應(yīng)用';->selectcount(*)intonfromtb_studentwheresspecialty='計(jì)算機(jī)應(yīng)用';->opencur_stu;->repeat->fetchcur_stuintorow_sno,row_sname,row_specialty;->selectrow_sno,row_sname,row_specialty;->seti=i+1;->untili>=nendrepeat;任務(wù)實(shí)施->closecur_stu;->end//mysql>delimiter;mysql>callpro_cur2();任務(wù)實(shí)施（2）使用越界標(biāo)志來(lái)控制循環(huán)。mysql>delimiter//mysql>createprocedurepro_cur21()->begin->declarehaveintdefault1;->declarerow_snovarchar(20);->declarerow_snamevarchar(20);->declarerow_specialtyvarchar(20);->declarecur_stucursorforselectsnoas學(xué)號(hào),snameas姓名,sspecialtyas專業(yè)fromtb_studentwheresspecialty='計(jì)算機(jī)應(yīng)用';->declareexithandlerfornotfoundsethave=0;

任務(wù)實(shí)施->opencur_stu;->repeat->fetchcur_stuintorow_sno,row_sname,row_specialty;->selectro