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

PAGE6PAGE6PAGE5PAGE5

課題存儲過程和函數(shù)（二）課時2課時（90min）教學目標知識技能目標：（1）掌握查看存儲過程和函數(shù)的方法（2）掌握修改及刪除存儲過程和函數(shù)的方法素質目標：（1）培養(yǎng)探究意識（2）了解數(shù)據(jù)庫的發(fā)展及主流國產(chǎn)數(shù)據(jù)庫的應用，增強民族自豪感教學重難點教學重點：掌握查看存儲過程和函數(shù)的方法教學難點：掌握修改及刪除存儲過程和函數(shù)的方法教學方法案例分析法、問答法、討論法、講授法、實踐法教學用具電腦、投影儀、多媒體課件、教材教學設計第1節(jié)課：→→→傳授新知（38min）第2節(jié)課：→傳授新知（20min）→課堂實訓（15min）→課堂小結（3min）→作業(yè)布置（2min）教學過程主要教學內(nèi)容及步驟設計意圖第一節(jié)課課前任務【教師】布置課前任務，和學生負責人取得聯(lián)系，讓其提醒同學通過文旌課堂APP或其他學習軟件，預習本節(jié)課要學習的知識【學生】完成課前任務通過課前任務，使學生預習本節(jié)課要學習的知識，增加學生的學習興趣考勤（2min）【教師】使用文旌課堂APP進行簽到【學生】班干部報請假人員及原因培養(yǎng)學生的組織紀律性，掌握學生的出勤情況問題導入（5min）【教師】提出以下問題：創(chuàng)建完存儲過程和函數(shù)后該怎么查看呢？·通過問題導入，引導學生主動思考，激發(fā)學生的學習興趣傳授新知（38min）【教師】通過學生的回答引入要講的知識，講解查看存儲過程和函數(shù)的相關知識13.4查看存儲過程和函數(shù)?【教師】組織學生掃碼觀看“查看與編輯存儲過程和函數(shù)”視頻（詳見教材），讓學生對相關知識有一個大致了解創(chuàng)建完存儲過程和函數(shù)后，MySQL存儲了其狀態(tài)信息和定義語句，用戶可以分別使用SHOWSTATUS和SHOWCREATE語句進行查看，也可以在系統(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í)行結果如下：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)建用戶的權限來執(zhí)行，還是以調用者的權限來執(zhí)行。13.4.2查看存儲過程和函數(shù)的定義使用SHOWCREATE語句可以查看存儲過程和函數(shù)的定義語句，語法形式如下：SHOWCREATE{PROCEDURE|FUNCTION}pf_name;PROCEDURE或FUNCTION指定查看的是存儲過程還是函數(shù)，pf_name指定存儲過程或函數(shù)名。【實例13-10】執(zhí)行SQL語句，查看存儲過程proc的定義語句。首先登錄MySQL，然后執(zhí)行SHOWCREATE語句，執(zhí)行結果如下：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ù)詳細信息的語法形式如下：SELECT*FROMinformation_schema.routinesWHEREROUTINE_NAME='pf_name';上述語句中，ROUTINE_NAME指定存儲過程或函數(shù)名，如果有存儲過程和存儲函數(shù)名稱相同，還可以使用ROUTINE_TYPE指定類型?！緦嵗?3-11】執(zhí)行SQL語句，查看存儲過程proc的詳細信息。登錄MySQL，并執(zhí)行SQL語句，執(zhí)行結果如下：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)建用戶的權限來執(zhí)行，還是以調用者的權限來執(zhí)行?！緦W生】聆聽、思考、記錄通過教師的講解和演示，使學生了解查看存儲過程和函數(shù)的相關知識第二節(jié)課問題導入（5min）【教師】提出以下問題：存儲過程和函數(shù)創(chuàng)建完畢后發(fā)現(xiàn)出錯該怎么進行修改或刪除呢？【學生】思考、舉手回答通過問題導入，引導學生主動思考，激發(fā)學生的學習興趣傳授新知（20min）【教師】通過學生的回答引入新知，介紹修改和刪除存儲過程和函數(shù)的相關知識13.5修改和刪除存儲過程和函數(shù)13.5.1修改存儲過程和函數(shù)在MySQL中，使用ALTER關鍵字可以修改存儲過程和函數(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語句，這兩個語句結構相同，參數(shù)也一樣。并且它們與創(chuàng)建存儲過程和函數(shù)的語句中的參數(shù)也基本一樣。【實例13-12】執(zhí)行SQL語句，修改存儲過程proc的讀寫權限和安全類型。首先登錄MySQL，并選擇數(shù)據(jù)庫db_shop。SQL語句及其執(zhí)行結果如下：mysql>ALTERPROCEDUREprocMODIFIESSQLDATASQLSECURITYINVOKER;QueryOK,0rowsaffected(0.09sec)查看存儲過程修改后信息，SQL語句及其執(zhí)行結果如下：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)由查詢結果可以看出，存儲過程修改成功。【提示】不能使用關鍵字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í)行結果如下：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ù)相關的參數(shù)信息，“DDL”選項卡內(nèi)顯示存儲過程和函數(shù)的創(chuàng)建語句……（詳見教材）【學生】聆聽、理解、記錄通過教師的講解和演示，使學生了解修改和刪除存儲過程和函數(shù)的相關知識課堂實訓（15min）【教師】組織學生以小組為單位，根據(jù)步驟提示完成以下實訓內(nèi)容前面介紹了存儲過程和函數(shù)的相關概念，以及創(chuàng)建、查看、修改和刪除存儲過程和函數(shù)的方法。下面通過創(chuàng)建并應用存儲過程和函數(shù)，讓讀者重溫本章