數(shù)據(jù)庫技術(shù)與應(yīng)用 課件 項目8 學(xué)生成績管理系統(tǒng)中存儲過程的操作

項目8學(xué)生成績管理系統(tǒng)中存儲過程的操作數(shù)據(jù)庫技術(shù)與應(yīng)用掌握使用SQL語句創(chuàng)建存儲過程、刪除存儲過程的方法掌握創(chuàng)建無參數(shù)存儲過程的方法掌握創(chuàng)建帶參數(shù)存儲過程的方法掌握創(chuàng)建帶流程控制的存儲過程的方法本章目標(biāo)在學(xué)生成績管理系統(tǒng)數(shù)據(jù)庫中，教師和學(xué)生對數(shù)據(jù)進行錄入、查詢、更新、刪除的操作可查詢語句和維護語句。部分教師和學(xué)生查詢的數(shù)據(jù)是相同的，這樣便出現(xiàn)了大量重復(fù)是作，每個人都要重復(fù)編寫查詢語句，降低了查詢效率。另一方面，教師和學(xué)生可以對數(shù)中的所有數(shù)據(jù)進行查詢操作，這樣使數(shù)據(jù)庫信息很不安全，為了解決這些問題，本項目引入存儲過程，通過存儲過程隱藏表的細(xì)節(jié)，提高數(shù)據(jù)庫系統(tǒng)的安全性。一、使用SQL語句創(chuàng)建無參的存儲過程并調(diào)用1.存儲過程的概念存儲過程(StoredProcedure）是一組用于完成特定功能的SQL語句集，經(jīng)編譯后存儲在數(shù)據(jù)庫中。用戶通過指定存儲過程的名字并給出參數(shù)（如果該存儲過程帶有參數(shù)）來執(zhí)行存儲過程。2.創(chuàng)建存儲過程創(chuàng)建存過程的基本語法格式如下。CREATEPROCEDURE存儲過程名([[IN|OUT|INOUT]參數(shù)名稱參數(shù)類型])[characteristic…]routine_body上述語法格式中，存儲過程的參數(shù)是可選的，使用參數(shù)時，如果參數(shù)有多個，參數(shù)之間使用逗號分隔。參數(shù)和選項的具體含義如下。IN：表示輸入?yún)?shù)，該參數(shù)需要在調(diào)用存儲過程時傳入。OUT：表示輸出參數(shù)，初始值為NULL，它是將存儲過程中的值保存到OUT指定的參數(shù)中，返回給調(diào)用者。INOUT：表示輸入輸出參數(shù)，既可以作為輸入?yún)?shù)也可以作為輸出參數(shù)。一、使用SQL語句創(chuàng)建無參的存儲過程并調(diào)用3.調(diào)用存儲過程基本語法格式：CALL[數(shù)據(jù)庫名稱.]存儲過程名稱([實參列表])；在上述語法格式中，實參列表傳遞的參數(shù)需要與創(chuàng)建存儲過程的形參相對應(yīng)。當(dāng)形參被指定為IN時，實參值可以為變量或者是具體的數(shù)據(jù)；當(dāng)形參被指定為OUT或INOUT時，調(diào)用存儲過程傳遞的參數(shù)必須是一個變量，用于接收返回給調(diào)用者的數(shù)據(jù)。一、使用SQL語句創(chuàng)建無參的存儲過程并調(diào)用圖8-1Navicat工具中使用SQL語句創(chuàng)建存儲過程子任務(wù)1：創(chuàng)建無參的存儲過程PD1_S1教師經(jīng)常會查詢學(xué)生的學(xué)號、姓名、電話號碼和家庭住址等信息（需設(shè)置別名），創(chuàng)建存儲過程PD1_S1，并執(zhí)行實現(xiàn)查詢的操作。一、使用SQL語句創(chuàng)建無參的存儲過程并調(diào)用圖8-2Navicat工具中使用SQL語句調(diào)用存儲過程子任務(wù)1：創(chuàng)建無參的存儲過程PD1_S1

打開如圖8-2所示界面，在該窗口中輸入以下語句調(diào)用存儲過程。運行效果如圖8-2所示。一、使用SQL語句創(chuàng)建無參的存儲過程并調(diào)用圖8-3Navicat工具中使用SQL語句創(chuàng)建存儲過程子任務(wù)2：創(chuàng)建無參的存儲過程PD1_S2系主任要查詢“軟件2101”班的學(xué)生姓名、班級、課程名稱、成績等信息（需設(shè)置別名)，創(chuàng)建存儲過程

PD1_S2

并執(zhí)行實現(xiàn)查詢的操作。一、使用SQL語句創(chuàng)建無參的存儲過程并調(diào)用圖8-4Navicat工具中使用SQL語句調(diào)用存儲過程子任務(wù)2：創(chuàng)建無參的存儲過程PD1_S2打開如圖8-4所示界面，在該窗口中輸入以下語句調(diào)用存儲過程。運行效果如圖8-4所示。一、使用SQL語句創(chuàng)建無參的存儲過程并調(diào)用二、使用SQL語句創(chuàng)建帶參的存儲過程并調(diào)用MySQL存儲過程參數(shù)簡介IN是默認(rèn)模式。IN在存儲過程中定義參數(shù)時，調(diào)用程序必須將參數(shù)傳遞給存儲過程。此外，IN參數(shù)的值受到保護。這意味著即使IN參數(shù)的值在存儲過程內(nèi)部發(fā)生更改，其原始值也會在存儲過程結(jié)束后保留??。換句話說，存儲過程僅適用于IN參數(shù)的副本。OUT可以在存儲過程內(nèi)更改參數(shù)的值，并將其新值傳遞回調(diào)用程序。請注意，存儲過程OUT在啟動時無法訪問參數(shù)的初始值。INOUT參數(shù)是IN和OUT參數(shù)的組合。這意味著調(diào)用程序可以傳遞參數(shù)，并且存儲過程可以修改INOUT參數(shù)，并將新值傳遞回調(diào)用程序。在存儲過程中定義參數(shù)的語法如下：MODEparam_nameparam_type(param_size)MODE可能是IN，OUT或INOUT，根據(jù)在存儲過程中的參數(shù)的目的。這param_name是參數(shù)的名稱。參數(shù)的名稱必須遵循MySQL中列名的命名規(guī)則。遵循參數(shù)名稱是其數(shù)據(jù)類型和大小。與變量一樣，參數(shù)的數(shù)據(jù)類型可以是任何有效的MySQL數(shù)據(jù)類型。如果存儲過程具有多個參數(shù)，則每個參數(shù)由逗號(,)分隔。圖8-5Navicat工具中使用SQL語句創(chuàng)建存儲過程子任務(wù)1：創(chuàng)建帶輸入?yún)?shù)的存儲過程PD2_S1查詢指定學(xué)生姓名的學(xué)生學(xué)號、姓名、電話號碼和家庭住址等信息，創(chuàng)建存儲過程PD2_S1,使用參數(shù)“王麗”，執(zhí)行存儲過程。二、使用SQL語句創(chuàng)建帶參的存儲過程并調(diào)用圖8-6Navicat工具中使用SQL語句調(diào)用存儲過程子任務(wù)1：創(chuàng)建帶輸入?yún)?shù)的存儲過程PD2_S1打開如圖8-6所示界面，在該窗口中輸入以下語句調(diào)用存儲過程。運行效果如圖8-6所示。二、使用SQL語句創(chuàng)建帶參的存儲過程并調(diào)用圖8-7Navicat工具中使用SQL語句創(chuàng)建存儲過程子任務(wù)2：創(chuàng)建帶輸入?yún)?shù)的存儲過程PD2_S2創(chuàng)建存儲過程PD2_S2，檢索指定學(xué)生姓名的學(xué)生學(xué)號、姓名、課程號、成績信息，使用參數(shù)“王麗”，執(zhí)行存儲過程。二、使用SQL語句創(chuàng)建帶參的存儲過程并調(diào)用圖8-8Navicat工具中使用SQL語句調(diào)用存儲過程子任務(wù)2：創(chuàng)建帶輸入?yún)?shù)的存儲過程PD2_S2打開如圖8-8所示界面，在該窗口中輸入以下語句調(diào)用存儲過程。運行效果如圖8-8所示。二、使用SQL語句創(chuàng)建帶參的存儲過程并調(diào)用圖8-9Navicat工具中使用SQL語句創(chuàng)建存儲過程子任務(wù)3：創(chuàng)建帶輸出參數(shù)的存儲過程PD2_S3創(chuàng)建存儲過程PD2_S3，查詢所有學(xué)生的人數(shù)，并調(diào)用存儲過程。二、使用SQL語句創(chuàng)建帶參的存儲過程并調(diào)用圖8-10Navicat工具中使用SQL語句調(diào)用存儲過程

子任務(wù)3：創(chuàng)建帶輸出參數(shù)的存儲過程PD2_S3打開如圖8-10所示界面，在該窗口中輸入以下語句調(diào)用存儲過程。運行效果如圖8-10所示二、使用SQL語句創(chuàng)建帶參的存儲過程并調(diào)用1判斷語句1.1IF語句IF語句可以對條件進行判斷，根據(jù)條件的真假來執(zhí)行不同的語句，其語法格式如下：IF條件表達式1THEN語句列表[ELSEIF條件表達式2THEN語句列表]...ENDIF[ELSE語句列表]在上述語法格式中，當(dāng)條件表達式1結(jié)果為真時，執(zhí)行THEN子句后的語句列表：條件表達式1結(jié)果為假時，繼續(xù)判斷條件表達式2，如果條件表達式2結(jié)果為真，則行對應(yīng)的THEN子句后的語句列表，以此類推；如果所有的條件表達式結(jié)果都為假，執(zhí)行ELSE子句后的語句列表。需要注意的是，每個語句列表中至少必須包含一個SQL語句。三、使用SQL語句創(chuàng)建帶流程控制的存儲過程并調(diào)用1判斷語句1.2CASE語句CASE語句也可以對條件進行判斷，它可以實現(xiàn)比IF語句更復(fù)雜的條件判段CASE語句的語法格式有兩種，具體如下。#語法格式1CASE表達式WHEN值1THEN語句列表[WHEN值2THEN語句列表]...[ELSE語句列表］ENDCASE從上述語法格式可以看出，CASE語句中可以有多個WHEN子句，CASE后面的表達式的結(jié)果決定哪一個WHEN子句會被執(zhí)行。當(dāng)WHEN子句后的值與表達式結(jié)果值相同時，執(zhí)行對應(yīng)的THEN關(guān)鍵字后的語句列表；如果所有WHEN子句后的值都和表達式結(jié)果值不同，則執(zhí)行ELSE后的語句列表。ENDCASE表示CASE語句的結(jié)束。三、使用SQL語句創(chuàng)建帶流程控制的存儲過程并調(diào)用1判斷語句#語法格式2CASEWHEN條件表達式1THEN語句列表[WHEN條件表達式2THEN語句列表]...[ELSE語句列表]ENDCASE在上述語法格式中，當(dāng)WHEN子句后的條件表達式結(jié)果為真時，執(zhí)行對應(yīng)THEN后的語句列表；當(dāng)所有WHEN子句后的條件表達式都不為真時，執(zhí)行ELSE后的語句列表。三、使用SQL語句創(chuàng)建帶流程控制的存儲過程并調(diào)用2循環(huán)語句循環(huán)語句指的是在符合條件的情況下重復(fù)執(zhí)行一段代碼，例如計算給定區(qū)間內(nèi)數(shù)據(jù)的累加和。MySQL提供的循環(huán)語句有LOOP、REPEAT和WHILE3種，下面分別進行介紹。1.1LOOP語句LOOP語句通常用于實現(xiàn)一個簡單的循環(huán)，其基本語法格式如下。[標(biāo)簽:]LOOP語句列表ENDLOOP[標(biāo)簽]；在上述語法格式中，標(biāo)簽是可選參數(shù)，用于標(biāo)志循環(huán)的開始和結(jié)束。標(biāo)簽的定義只需要符合MySQL標(biāo)識符的定義規(guī)則即可，但兩個位置的標(biāo)簽名稱必須相同。LOOP會重復(fù)執(zhí)行語句列表，因此在循環(huán)時務(wù)必給出結(jié)束循環(huán)的條件，否則會出現(xiàn)死循環(huán)。LOOP語句本身沒有停止語句，如果要退出LOOP循環(huán)，需要使用LEAVE語句。三、使用SQL語句創(chuàng)建帶流程控制的存儲過程并調(diào)用2循環(huán)語句1.2.REPEAT語句REPEAT語句用于循環(huán)執(zhí)行符合條件的語句列表，每次循環(huán)時，都會對語句中的條件表達式進行判斷。如果表達式返回值為TRUE，則結(jié)束循環(huán)，否則重復(fù)執(zhí)行循環(huán)中的語句。REPEAT語句的基本語法格式如下。[標(biāo)簽:]REPEAT語句列表UNTIL條件表達式ENDREPEAT[標(biāo)簽]在上述語法格式中，程序會無條件地先執(zhí)行一次REPEAT語句中的語句列表，然后再判斷UNTIL后的條件表達式的結(jié)果是否為TRUE。如果為TRUE，則結(jié)束循環(huán)；如果不為TRUE，則繼續(xù)執(zhí)行語句列表。三、使用SQL語句創(chuàng)建帶流程控制的存儲過程并調(diào)用2循環(huán)語句1.3WHILE語句WHILE語句也用于循環(huán)執(zhí)行符合條件的語句列表，但與REPEAT語句不同的是WHILE語句是先判斷條件表達式，再根據(jù)判斷結(jié)果確定是否執(zhí)行循環(huán)內(nèi)的語句列表WHILE語句的

基本語法格式如下。[標(biāo)簽:]WHILE條件表達式DO語句列表ENDWHILE[標(biāo)簽]在上述語法格式中，只有條件表達式為真時，才會執(zhí)行DO后面的語句列表。語句列表執(zhí)行完之后，再次判斷條件表達式的結(jié)果，如果結(jié)果為真，繼續(xù)執(zhí)行語句列表；如同結(jié)果為假，則退出循環(huán)。在使用WHILE循環(huán)語句時，可以在語句列表中設(shè)置循環(huán)進出口，以防出現(xiàn)死循環(huán)的現(xiàn)象。三、使用SQL語句創(chuàng)建帶流程控制的存儲過程并調(diào)用3跳轉(zhuǎn)語句跳轉(zhuǎn)語句用于實現(xiàn)執(zhí)行過程中的流程跳轉(zhuǎn)。MySQL中常用的跳轉(zhuǎn)語句有LEAVE語句和ITERATE語句，其基本語法格式如下。(ITERATE|LEAVE]標(biāo)簽名；在上述語法格式中，ITERATE語句用于結(jié)束本次循環(huán)的執(zhí)行，開始下一輪循環(huán)的執(zhí)行；而LEAVE語句用于終止當(dāng)前循環(huán)，跳出循環(huán)體。三、使用SQL語句創(chuàng)建帶流程控制的存儲過程并調(diào)用圖8-10Navicat工具中使用SQL語句創(chuàng)建存儲過程子任務(wù)1：創(chuàng)建帶流程控制的存儲過程PD3_S1創(chuàng)建存儲過程PD3_S1，學(xué)生成績管理系統(tǒng)中經(jīng)常需要根據(jù)輸入的學(xué)生姓名返回對應(yīng)的學(xué)生信息，如果輸入為空，則顯示輸入的值為空；如果輸入的學(xué)生姓名在學(xué)生表中不存在，則顯示學(xué)生不存在。技術(shù)人員決定將這個需求編寫成存儲過程。并調(diào)用存儲過程。三、使用SQL語句創(chuàng)建帶流程控制的存儲過程并調(diào)用圖8-11Navicat工具中使用SQL語句調(diào)用存儲過程子任務(wù)1：創(chuàng)建帶流程控制的存儲過程PD3_S1打開如圖8-10所示界面，在該窗口中輸入以下語句調(diào)用存儲過程。運行效果如圖8-11所示。三、使用SQL語句創(chuàng)建帶流程控制的存儲過程并調(diào)用圖8-12Navicat工具中使用SQL語句調(diào)用存儲過程子任務(wù)1：創(chuàng)建帶流程控制的存儲過程PD3_S1在該窗口中輸入以下語句調(diào)用存儲過程。運行效果如圖8-12所示。三、使用SQL語句創(chuàng)建帶流程控制的存儲過程并調(diào)用圖8-13Navicat工具中使用SQL語句創(chuàng)建存儲過程子任務(wù)2：創(chuàng)建帶流程控制的存儲過程PD3_S2創(chuàng)建存儲過程PD3_S2，在存儲過程中實現(xiàn)0-9的整數(shù)的累加計算，并調(diào)用存儲過程。三、使用SQL語句創(chuàng)建帶流程控制的存儲過程并調(diào)用圖8-14Navicat工具中使用SQL語句調(diào)用存儲過程子任務(wù)2：創(chuàng)建帶流程控制的存儲過程PD3_S2

打開如圖8-14所示界面，在該窗口中輸入以下語句調(diào)用存儲過程。運行效果如圖8-14所示。三、使用SQL語句創(chuàng)建帶流程控制的存儲過程并調(diào)用圖8-15Navicat工具中使用SQL語句創(chuàng)建存儲過程子任務(wù)3：創(chuàng)建帶流程控制的存儲過程PD3_S3創(chuàng)建存儲過程PD3_S3，實現(xiàn)計算5以下的正偶數(shù)的累加和，并調(diào)用存儲過程。三、使用SQL語句創(chuàng)建帶流程控制的存儲過程并調(diào)用圖8-16Navicat工具中使用SQL語句調(diào)用存儲過程子任務(wù)3：創(chuàng)建帶流程控制的存儲過程PD3_S3上述程序定義了一個存儲過程PD3_S3。在該存儲過程中，首先定義了局部變量num并設(shè)置num的默認(rèn)初始值為0；接著執(zhí)行LOOP語句，LOOP語句的語句列表中執(zhí)行的順序為先設(shè)置num的值自增2，然后判斷num的值是否小于5。如果是，則使用ITERATE語句結(jié)束當(dāng)前順序并執(zhí)行下一輪循環(huán)；如果不是，則查詢num的值并跳出my_loop循環(huán)。存儲過程PD3_S3通過LEAVE語句和ITERATE語句控制循環(huán)的跳轉(zhuǎn)。打開如圖8-16所示界面，在該窗口中輸入以下語句調(diào)用存儲過程。運行效果如圖8-16所示三、使用SQL語句創(chuàng)建帶流程控制的存儲過程并調(diào)用1查看存儲過程1.1使用SHOWPROCEDURESTATUS語句顯示存儲過程的狀態(tài)信息基本語法格式如下：SHOWPROCEDURESTATUS[LIKE‘pattern’]在上述語法格式中，PROCEDURE表示存儲過程；LIKE‘paten’表示匹配存儲過程的名稱。1.2使用SHOWCREATEPROCEDURE語句顯示存儲過程的創(chuàng)建信息其基本語法格式如下：SHOWCREATEPROCEDURE存儲過程名；在上述語法格式中，PROCEDURE表示存儲過程，存儲過程名為顯示創(chuàng)建信息的存儲過程名稱。1.3從information_schema.Routines表中查看存儲過程的信息查詢語句如下：SELECT*FROMinformation_schema.RoutinesWHEREROUTINE_NAME='pro_emp'ANDROUTINE_TYPE='PROCEDURE'\G需要注意的是，information_schema數(shù)據(jù)庫下的Routines表存儲著所有存儲過程的定義。使用SELECT語句查詢Routines表中某一存儲過程的信息時，一定要使用ROUTINE_NAME字段指定存儲過程的名稱，否則將查詢出所有存儲過程的定義。如果有存儲過程和函數(shù)名稱相同，則需要同時指定ROUTINE_TYPE字段表明查詢的是哪種類型的存儲程序。四、使用SQL語句對存儲過程進行維護2修改存儲過程在實際開發(fā)中，業(yè)務(wù)需求更改的情況時有發(fā)生，這樣就不可避免地需要修改存儲過程。在MySQL中，可以使用ALTER語句修改存儲過程，其基本語法格式如下。ALTERPROCEDURE存儲過程名稱[characteristic···];需要注意的是，上述語法格式不能修改存儲過程的參數(shù)，只能修改存儲過程的特征值，可修改的特征值包含表8-1中除“[NOT]DETERMINISTIC”之外的其他8個。存儲過程的例程默認(rèn)情況是該存儲過程的定義者才