《MySQL數(shù)據(jù)庫應(yīng)用案例教程》651-5（馬潔）教案 第18課 存儲過程和函數(shù)（二）

PAGE6PAGE6PAGE5PAGE5

課題存儲過程和函數(shù)（二）課時2課時（90min）教學(xué)目標(biāo)知識技能目標(biāo)：（1）掌握查看存儲過程和函數(shù)的方法（2）掌握修改及刪除存儲過程和函數(shù)的方法素質(zhì)目標(biāo)：（1）培養(yǎng)探究意識（2）了解數(shù)據(jù)庫的發(fā)展及主流國產(chǎn)數(shù)據(jù)庫的應(yīng)用，增強(qiáng)民族自豪感教學(xué)重難點教學(xué)重點：掌握查看存儲過程和函數(shù)的方法教學(xué)難點：掌握修改及刪除存儲過程和函數(shù)的方法教學(xué)方法案例分析法、問答法、討論法、講授法、實踐法教學(xué)用具電腦、投影儀、多媒體課件、教材教學(xué)設(shè)計第1節(jié)課：→→→傳授新知（38min）第2節(jié)課：→傳授新知（20min）→課堂實訓(xùn)（15min）→課堂小結(jié)（3min）→作業(yè)布置（2min）教學(xué)過程主要教學(xué)內(nèi)容及步驟設(shè)計意圖第一節(jié)課課前任務(wù)【教師】布置課前任務(wù)，和學(xué)生負(fù)責(zé)人取得聯(lián)系，讓其提醒同學(xué)通過文旌課堂APP或其他學(xué)習(xí)軟件，預(yù)習(xí)本節(jié)課要學(xué)習(xí)的知識【學(xué)生】完成課前任務(wù)通過課前任務(wù)，使學(xué)生預(yù)習(xí)本節(jié)課要學(xué)習(xí)的知識，增加學(xué)生的學(xué)習(xí)興趣考勤（2min）【教師】使用文旌課堂APP進(jìn)行簽到【學(xué)生】班干部報請假人員及原因培養(yǎng)學(xué)生的組織紀(jì)律性，掌握學(xué)生的出勤情況問題導(dǎo)入（5min）【教師】提出以下問題：創(chuàng)建完存儲過程和函數(shù)后該怎么查看呢？·通過問題導(dǎo)入，引導(dǎo)學(xué)生主動思考，激發(fā)學(xué)生的學(xué)習(xí)興趣傳授新知（38min）【教師】通過學(xué)生的回答引入要講的知識，講解查看存儲過程和函數(shù)的相關(guān)知識13.4查看存儲過程和函數(shù)?【教師】組織學(xué)生掃碼觀看“查看與編輯存儲過程和函數(shù)”視頻（詳見教材），讓學(xué)生對相關(guān)知識有一個大致了解創(chuàng)建完存儲過程和函數(shù)后，MySQL存儲了其狀態(tài)信息和定義語句，用戶可以分別使用SHOWSTATUS和SHOWCREATE語句進(jìn)行查看，也可以在系統(tǒng)數(shù)據(jù)庫information_schema中查看。13.4.1查看存儲過程和函數(shù)的狀態(tài)使用SHOWSTATUS語句可以查看存儲過程和函數(shù)的狀態(tài)?；菊Z法形式如下：SHOW{PROCEDURE|FUNCTION}STATUS[LIKE'pf_name'];PROCEDURE或FUNCTION指定查看的是存儲過程還是函數(shù)，LIKE語句指定存儲過程和函數(shù)的名稱?！緦嵗?3-9】執(zhí)行SQL語句，查看存儲過程proc的基本信息。首先登錄MySQL，然后執(zhí)行SHOWSTATUS語句，執(zhí)行結(jié)果如下：mysql>SHOWPROCEDURESTATUSLIKE'proc'\G***************************1.row***************************Db:db_shopName:procType:PROCEDUREDefiner:root@localhostModified:2018-06-0915:06:48Created:2018-06-0915:06:48Security_type:DEFINERComment:character_set_client:gbkcollation_connection:gbk_chinese_ciDatabaseCollation:utf8_general_ci1rowinset(0.02sec)其中的主要參數(shù)及其意義如下：Db：表示存儲過程或函數(shù)所屬數(shù)據(jù)庫。Name：表示存儲過程或函數(shù)名。Type：表示是存儲過程還是函數(shù)。Definer：表示創(chuàng)建存儲過程或函數(shù)的用戶。Modified：表示最后修改日期。Created：表示創(chuàng)建日期。Security_type：表示MySQL在執(zhí)行存儲過程和函數(shù)的時候，是以創(chuàng)建用戶的權(quán)限來執(zhí)行，還是以調(diào)用者的權(quán)限來執(zhí)行。13.4.2查看存儲過程和函數(shù)的定義使用SHOWCREATE語句可以查看存儲過程和函數(shù)的定義語句，語法形式如下：SHOWCREATE{PROCEDURE|FUNCTION}pf_name;PROCEDURE或FUNCTION指定查看的是存儲過程還是函數(shù)，pf_name指定存儲過程或函數(shù)名?！緦嵗?3-10】執(zhí)行SQL語句，查看存儲過程proc的定義語句。首先登錄MySQL，然后執(zhí)行SHOWCREATE語句，執(zhí)行結(jié)果如下：mysql>SHOWCREATEPROCEDUREdb_c\G***************************1.row***************************Procedure:procsql_mode:STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTIONCreateProcedure:CREATEDEFINER=`root`@`localhost`PROCEDURE`proc`(OUTnumINT)BEGINSELECTCOUNT(*)INTOnumFROMgoods;ENDcharacter_set_client:gbkcollation_connection:gbk_chinese_ciDatabaseCollation:utf8_general_ci1rowinset(0.00sec)其中的主要參數(shù)及其意義如下：Procedure：表示存儲過程名。sql_mode：表示SQL語句的模式。CreateProcedure：表示存儲過程的定義語句。13.4.3查看存儲過程和函數(shù)的信息在MySQL中，存儲過程和函數(shù)的信息存儲在系統(tǒng)數(shù)據(jù)庫information_schema中的routines表中，查看存儲過程和函數(shù)詳細(xì)信息的語法形式如下：SELECT*FROMinformation_schema.routinesWHEREROUTINE_NAME='pf_name';上述語句中，ROUTINE_NAME指定存儲過程或函數(shù)名，如果有存儲過程和存儲函數(shù)名稱相同，還可以使用ROUTINE_TYPE指定類型?！緦嵗?3-11】執(zhí)行SQL語句，查看存儲過程proc的詳細(xì)信息。登錄MySQL，并執(zhí)行SQL語句，執(zhí)行結(jié)果如下：mysql>SELECT*FROMinformation_schema.routinesWHEREROUTINE_NAME='proc'ANDROUTINE_TYPE='PROCEDURE'\G***************************1.row***************************SPECIFIC_NAME:procROUTINE_CATALOG:defROUTINE_SCHEMA:db_shopROUTINE_NAME:procROUTINE_TYPE:PROCEDUREDATA_TYPE:CHARACTER_MAXIMUM_LENGTH:NULLCHARACTER_OCTET_LENGTH:NULLNUMERIC_PRECISION:NULLNUMERIC_SCALE:NULLDATETIME_PRECISION:NULLCHARACTER_SET_NAME:NULLCOLLATION_NAME:NULLDTD_IDENTIFIER:NULLROUTINE_BODY:SQLROUTINE_DEFINITION:BEGINSELECTCOUNT(*)INTOnumFROMgoods;ENDEXTERNAL_NAME:NULLEXTERNAL_LANGUAGE:NULLPARAMETER_STYLE:SQLIS_DETERMINISTIC:NOSQL_DATA_ACCESS:CONTAINSSQLSQL_PATH:NULLSECURITY_TYPE:DEFINERCREATED:2018-06-0915:06:48LAST_ALTERED:2018-06-0915:06:48SQL_MODE:STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTIONROUTINE_COMMENT:DEFINER:root@localhostCHARACTER_SET_CLIENT:gbkCOLLATION_CONNECTION:gbk_chinese_ciDATABASE_COLLATION:utf8_general_ci1rowinset(0.05sec)其中的主要參數(shù)及其意義如下：ROUTINE_CATALOG：表示存儲過程或函數(shù)的目錄。ROUTINE_SCHEMA：表示存儲過程或函數(shù)所屬數(shù)據(jù)庫。ROUTINE_NAME：表示存儲過程或函數(shù)名。ROUTINE_TYPE：表示是存儲過程還是存儲函數(shù)。ROUTINE_DEFINITION：表示BEGIN…END語句。SECURITY_TYPE：表示MySQL在執(zhí)行存儲過程和函數(shù)的時候，是以創(chuàng)建用戶的權(quán)限來執(zhí)行，還是以調(diào)用者的權(quán)限來執(zhí)行?！緦W(xué)生】聆聽、思考、記錄通過教師的講解和演示，使學(xué)生了解查看存儲過程和函數(shù)的相關(guān)知識第二節(jié)課問題導(dǎo)入（5min）【教師】提出以下問題：存儲過程和函數(shù)創(chuàng)建完畢后發(fā)現(xiàn)出錯該怎么進(jìn)行修改或刪除呢？【學(xué)生】思考、舉手回答通過問題導(dǎo)入，引導(dǎo)學(xué)生主動思考，激發(fā)學(xué)生的學(xué)習(xí)興趣傳授新知（20min）【教師】通過學(xué)生的回答引入新知，介紹修改和刪除存儲過程和函數(shù)的相關(guān)知識13.5修改和刪除存儲過程和函數(shù)13.5.1修改存儲過程和函數(shù)在MySQL中，使用ALTER關(guān)鍵字可以修改存儲過程和函數(shù)，基本語法形式如下：ALTER{PROCEDURE|FUNCTION}pf_name[characteristic…];上述語句中，pf_name表示存儲過程或函數(shù)名。characteristic表示存儲過程和函數(shù)的特性，其可取值有CONTAINSSQL，NOSQL，READSSQLDATA，MODIFIESSQLDATA，SQLSECURITY{DEFINER|INVOKER}，各值的意義與創(chuàng)建存儲過程和函數(shù)時相同?！咎崾尽啃薷拇鎯^程使用ALTERPROCEDURE語句，修改存儲函數(shù)使用ALTERFUNCTION語句，這兩個語句結(jié)構(gòu)相同，參數(shù)也一樣。并且它們與創(chuàng)建存儲過程和函數(shù)的語句中的參數(shù)也基本一樣。【實例13-12】執(zhí)行SQL語句，修改存儲過程proc的讀寫權(quán)限和安全類型。首先登錄MySQL，并選擇數(shù)據(jù)庫db_shop。SQL語句及其執(zhí)行結(jié)果如下：mysql>ALTERPROCEDUREprocMODIFIESSQLDATASQLSECURITYINVOKER;QueryOK,0rowsaffected(0.09sec)查看存儲過程修改后信息，SQL語句及其執(zhí)行結(jié)果如下：mysql>SELECTSPECIFIC_NAME,SQL_DATA_ACCESS,SECURITY_TYPE->FROMinformation_schema.routines->WHEREROUTINE_NAME='proc'ANDROUTINE_TYPE='PROCEDURE';++++|SPECIFIC_NAME|SQL_DATA_ACCESS|SECURITY_TYPE|++++|proc|MODIFIESSQLDATA|INVOKER|++++1rowinset(0.12sec)由查詢結(jié)果可以看出，存儲過程修改成功?！咎崾尽坎荒苁褂藐P(guān)鍵字ALTER更改存儲過程的參數(shù)或子程序，如果需要修改，必須刪除存儲過程后再重新創(chuàng)建。13.5.2刪除存儲過程和函數(shù)在MySQL中，刪除存儲過程和函數(shù)可以使用DROP語句，語法形式如下：DROP{PROCEDURE|FUNCTION}[IFEXISTS]pf_name;pf_name為要刪除的存儲過程或函數(shù)名。使用IFEXISTS可以在執(zhí)行刪除操作時，先判斷存儲過程和函數(shù)是否存在，避免系統(tǒng)報錯?！緦嵗?3-13】執(zhí)行SQL語句，刪除存儲過程proc。首先登錄MySQL，并選擇數(shù)據(jù)庫db_shop。SQL語句及其執(zhí)行結(jié)果如下：mysql>DROPPROCEDUREIFEXISTSproc;QueryOK,0rowsaffected(0.03sec)13.5.3使用圖形化工具查看和編輯存儲過程和函數(shù)使用NavicatforMySQL可以非常方便地查看、修改和刪除存儲過程和函數(shù)。具體操作如下：步驟

1使用NavicatforMySQL連接MySQL后，雙擊需要操作的數(shù)據(jù)庫，此處為“db_shop”。步驟

2單擊“函數(shù)”按鈕，然后右擊需要查看信息的存儲過程或函數(shù)，選擇“對象信息”項步驟3“常規(guī)”選項卡內(nèi)顯示存儲過程和函數(shù)相關(guān)的參數(shù)信息，“DDL”選項卡內(nèi)顯示存儲過程和函數(shù)的創(chuàng)建語句……（詳見教材）【學(xué)生】聆聽、理解、記錄通過教師的講解和演示，使學(xué)生了解修改和刪除存儲過程和函數(shù)的相關(guān)知識課堂實訓(xùn)（15min）【教師】組織學(xué)生以小組為單位，根據(jù)步驟提示完成以下實訓(xùn)內(nèi)容前面介紹了存儲過程和函數(shù)的相關(guān)概念，以及創(chuàng)建、查看、修改和刪除存儲過程和函數(shù)的方法。下面通過創(chuàng)建并應(yīng)用存儲過程和函數(shù)，讓讀者重溫本章