第7章-子程序和觸發(fā)器

第7章存儲過程、函數(shù)和觸發(fā)器第二單元本章相關(guān)學(xué)習(xí)資源《存儲過程、函數(shù)和觸發(fā)器》專題《Web前端基礎(chǔ)及數(shù)據(jù)庫開發(fā)》學(xué)習(xí)平臺“Oracle數(shù)據(jù)庫”課程學(xué)生用書預(yù)習(xí)檢查簡述存儲過程和函數(shù)在語法上的區(qū)別簡述存儲過程傳遞參數(shù)的三種模式簡述程序包的組成什么是觸發(fā)條件？提問本章任務(wù)任務(wù)1：運用子程序完成與雇員有關(guān)的增刪改查業(yè)務(wù)需求任務(wù)2：運用程序包完成與雇員有關(guān)的增刪改查業(yè)務(wù)需求任務(wù)3：運用觸發(fā)器完成與雇員有關(guān)的增刪改查業(yè)務(wù)需求能夠熟練運用存儲過程完成業(yè)務(wù)需求會運用函數(shù)完成業(yè)務(wù)需求能夠熟練運用程序包進行編程了解觸發(fā)器的執(zhí)行原理本章目標點重點難點重串講：子程序根據(jù)你的理解，說一說什么是子程序？子程序有幾個組成部分？哪些部分是可選的？子程序有哪些優(yōu)點？提問串講：創(chuàng)建存儲過程CREATE[OR

REPLACE]PROCEDURE

<procedurename>[(<parameterlist>)]IS|AS

<localvariabledeclaration>BEGIN<executablestatements>[EXCEPTION<exceptionhandlers>]END;創(chuàng)建存儲過程，可指定運行過程需傳遞的參數(shù)語法處理異常包括在存儲過程中要執(zhí)行的語句演示示例：創(chuàng)建存儲過程串講：調(diào)用存儲過程用命令在SQL提示符下調(diào)用參數(shù)傳遞的三種方式？按位置傳遞參數(shù)按名稱傳遞參數(shù)混合方式傳遞參數(shù)默認值的調(diào)用方法？如何在PL/SQL塊中調(diào)用？語法EXEC[UTE]procedure_name(parameters_list);演示示例：調(diào)用存儲過程問題串講：存儲過程的參數(shù)模式IN用于接受調(diào)用程序的值默認的參數(shù)模式OUT用于向調(diào)用程序返回值INOUT用于接受調(diào)用程序的值，并向調(diào)用程序返回更新的值提問存儲過程傳遞參數(shù)有哪三種模式？串講：存儲過程的訪問權(quán)限和刪除存儲過程如何將過程的執(zhí)行權(quán)限授予其他用戶？刪除存儲過程的語法是什么？SQL>GRANTEXECUTEONfind_empTOMARTIN;SQL>GRANTEXECUTEONswapTOPUBLIC;DROP

PROCEDUREprocedure_name;語法問題上機練習(xí)需求說明編寫存儲過程。根據(jù)雇員編號，查詢該雇員的姓名和薪水，并通過輸出參數(shù)輸出分析考慮如果雇員編號不存在，查詢到空記錄，如何進行異常處理完成時間：10分鐘共性問題集中講解練習(xí)講解：存儲過程和游標的用法如果需要存儲過程返回所有員工薪水該怎么辦？（1）將存儲過程的參數(shù)定義為游標類型，且該游標類型為輸出類型參數(shù)（2）把查詢到的結(jié)果集賦值給參數(shù)游標（3）調(diào)用這個存儲過程時就可以得到這個結(jié)果集問題實現(xiàn)步驟演示示例：存儲過程和參數(shù)為輸出類型游標上機練習(xí)需求說明創(chuàng)建顯示員工薪水的存儲過程要求將薪水集合作為參數(shù)傳入存儲過程完成時間：10分鐘共性問題集中講解練習(xí)串講：創(chuàng)建和調(diào)用函數(shù)CREATE[OR

REPLACE]FUNCTION

<functionname>[(param1,param2)]RETURN<datatype>IS|AS

[localdeclarations]BEGINExecutableStatements;

RETURNresult;EXCEPTIONExceptionhandlers;END;語法函數(shù)是可以返回值的命名的PL/SQL子程序演示示例：

函數(shù)創(chuàng)建和調(diào)用串講：函數(shù)和存儲過程的區(qū)別存儲過程函數(shù)用于在數(shù)據(jù)庫中完成特定操作或者任務(wù)（如插入，刪除等）用于特定數(shù)據(jù)（如查詢返回值）程序頭部聲明用PROCEDURE程序頭部聲明用FUNCTION程序頭部聲明時不需要返回類型程序頭部聲明要描述返回類型，而且PL/SQL塊至少要包括一個有效的RETURN語句可以使用IN/OUT/INOUT三種參數(shù)模式可以使用IN/OUT/INOUT三種參數(shù)模式可以作為獨立的PL/SQL語句執(zhí)行不能獨立執(zhí)行，必須作為表達式的一部分調(diào)用可以通過OUT/INOUT返回零個或多個值通過RETURN語句返回一個值，且該值要與聲明部分一致，也可以通過OUT類型的參數(shù)帶出變量SQL語句（DML或SELECT）中不可以調(diào)用存儲過程SQL語句（DML或SELECT）中可以調(diào)用函數(shù)串講：使用存儲過程與函數(shù)的原則簡述存儲過程與函數(shù)使用場合區(qū)別簡述存儲過程與函數(shù)用途區(qū)別函數(shù)的特色是什么？提問第16頁/共34頁小結(jié)什么是子程序？兩種類型的PL/SQL子程序，即（）和（）函數(shù)存儲過程

提問第17頁/共34頁講解：程序包為什么使用程序包？使程序設(shè)計模塊化提高程序的執(zhí)行效率什么是程序包？程序包是對相關(guān)存儲過程、函數(shù)、變量、游標和異常等對象的封裝程序包由規(guī)范和主體兩部分組成聲明程序包中公共對象。包括類型、變量、常量、異常、游標規(guī)范和子程序規(guī)范等聲明程序包私有對象和實現(xiàn)在包規(guī)范中聲明的子程序和游標程序包規(guī)范主體問題第18頁/共34頁講解：創(chuàng)建程序包CREATE[OR

REPLACE]

PACKAGEpackage_nameIS|AS[Publicitemdeclarations][Subprogramspecification]END[package_name];CREATE[OR

REPLACE]PACKAGE

BODYpackage_nameIS|AS[Privateitemdeclarations][Subprogrambodies][BEGINInitialization]END[package_name];程序包規(guī)范程序包主體語法語法講解：程序包的開發(fā)步驟程序包的開發(fā)需要以下幾個步驟：由于程序包較大，所以一定將每個子程序調(diào)試通過后再寫程序包注意演示示例：

開發(fā)程序包講解：程序包中的游標靜態(tài)游標如何在包中使用？游標的定義分為游標規(guī)范和游標主體兩部分在包規(guī)范中聲明游標規(guī)范時必須使用RETURN子句指定游標的返回類型RETURN子句指定的數(shù)據(jù)類型可以是：用%ROWTYPE屬性引用表定義的記錄類型程序員定義的記錄類型動態(tài)游標如何在包中使用？不可在包中聲明游標變量問題演示示例：

程序包中的靜態(tài)游標注意演示示例：

程序包中的動態(tài)游標小結(jié)判斷對錯程序包是對相關(guān)類型、變量、常量、游標、異常、存儲過程和函數(shù)的封裝程序包由包規(guī)范和包主體兩部分組成包規(guī)范是包的接口，包含公用對象及其類型提問講解：為什么要使用觸發(fā)器如何實現(xiàn)跟蹤并記錄所有對雇員表的表結(jié)構(gòu)進行改變的操作？只要一變動表結(jié)構(gòu)就要記錄所有操作到一個審計表中不需要顯式調(diào)用來執(zhí)行，而是修改表結(jié)構(gòu)的操作來啟動運行使用觸發(fā)器可以實現(xiàn)問題分析結(jié)論講解：觸發(fā)器特性使用觸發(fā)器能夠滿足哪些需求？不需要顯式調(diào)用來執(zhí)行，而是由一個事件來啟動運行可以通過其他Oracle事件觸發(fā)調(diào)用的程序，因為觸發(fā)器不能接收參數(shù)Oracle

事件包括哪幾種情況？INSERT、UPDATE及DELETE操作或?qū)σ晥D進行類似的操作執(zhí)行DDL操作數(shù)據(jù)庫的啟動與關(guān)閉問題講解：觸發(fā)器語法CREATE[OR

REPLACE]TRIGGERtrigger_nameAFTER|BEFORE|INSTEADOF[INSERT][[OR]UPDATE[OFcolumn_list]][[OR]DELETE]ONtable_or_view_name[REFERENCING{OLD[AS]old/NEW[AS]new}][FOR

EACH

ROW][WHEN(condition)]pl/sql_block;語法演示示例：

創(chuàng)建觸發(fā)器講解：觸發(fā)器的組成部分觸發(fā)器由三部分組成：觸發(fā)器語句（事件）定義激活觸發(fā)器的DML事件和DDL事件觸發(fā)器限制執(zhí)行觸發(fā)器的條件，該條件必須為真才能激活觸發(fā)器觸發(fā)器操作（主體）包含一些SQL語句和代碼，它們在發(fā)出了觸發(fā)器語句且觸發(fā)限制的值為真時運行BEFOREDELETEONempFOREACHROW……WHEN(old.deptno<>10)BEGIN--將修改前數(shù)據(jù)插入到日志記錄表del_emp,以供監(jiān)督使用。INSERTINTOdel_emp(deptno,empno,……)VALUES(:old.deptno,:old.empno,……);END;講解：觸發(fā)器類型觸發(fā)器的類型有：觸發(fā)器類型模式(DDL)觸發(fā)器DML觸發(fā)器數(shù)據(jù)庫級觸發(fā)器語句級觸發(fā)器行級觸發(fā)器INSTEADOF觸發(fā)器講解：觸發(fā)器類型DDL觸發(fā)器數(shù)據(jù)庫級觸發(fā)器DML觸發(fā)器語句級觸發(fā)器行級觸發(fā)器INSTEADOF觸發(fā)器在模式中執(zhí)行DDL語句時執(zhí)行在發(fā)生打開、關(guān)閉、登錄和退出數(shù)據(jù)庫等系統(tǒng)事件時執(zhí)行在對表或視圖執(zhí)行DML語句時執(zhí)行無論受影響的行數(shù)是多少，都只執(zhí)行一次對DML語句修改的每個行執(zhí)行一次用于用戶不能直接使用DML語句修改的視圖演示示例：

行級、語句級觸發(fā)器、INSTEADOF觸發(fā)器如何啟用和禁用觸發(fā)器？如何刪除觸發(fā)器？是否選擇使用觸發(fā)器？能用其他方法替代就不要用觸發(fā)器講解：觸發(fā)器的管理SQL>ALTERTRIGGERaiu_itemfileDISABLE;SQL>ALTERTRIGGERaiu_itemfileENABLE;SQL>DROPTRIGGERaiu_itemfile;問題上機練習(xí)需求說明實現(xiàn)跟蹤并記錄所有對USER1用戶下的雇員表的表結(jié)構(gòu)進行改變的操作分析使用DDL觸發(fā)器完成時間：10分鐘共性問題集中講解練習(xí)答疑時間同學(xué)們請就以下方面的問題請教老師