1、第10章 存儲過程、函數、觸發器和包4本章要點：掌握存儲過程的創建。熟練掌握帶參數的存儲過程的使用。掌握存儲過程的管理。掌握函數的創建與使用。了解觸發器的類型。理解觸發器的作用。熟練掌握各種類型的觸發器。了解程序包的創建與使用。210.1 存 儲 過 程4存儲過程是一組為了完成特定功能的SQL語句集，它大大提高了SQL語句的功能和靈活性。4存儲過程經編譯后存儲在數據庫中，所以執行存儲過程要比執行存儲過程中封裝的SQL語句更有效率。310.1.1 創建與調用存儲過程4創建存儲過程需要使用CREATE PROCEDURE語句，其語法如下：CREATE OR REPLACE PROCEDURE pr

2、ocedure_name ( parameter IN | OUT | IN OUT data_type ) , . IS | AS declaration_section ; BEGIN procedure_body ;END procedure_name ;410.1.1 創建與調用存儲過程4【例例10.1】創建一個簡單的存儲過程update_emp，該過程用于將emp表中empno為6500的員工的姓名修改為CANDY，如下：SQL CREATE PROCEDURE update_emp AS 2 BEGIN 3 UPDATE emp SET ename = CANDY WHERE em

3、pno = 6500 ; 4 END update_emp ;SQL /過程已創建。4【例例10.2】使用EXECUTE語句與CALL語句調用存儲過程update_emp，分別如下：SQL EXECUTE update_emp ;PL/SQL 過程已成功完成。510.1.2 帶參數的存儲過程41IN參數的使用IN參數是指輸入參數，由存儲過程的調用者為其賦值(也可以使用默認值)。如果不為參數指定模式，則其模式默認為IN。在調用上述存儲過程update_emp2時，就需要為該過程的兩個輸入參數賦值，賦值的形式主要有如下兩種。|(1)不指定參數名不指定參數名|(2)指定參數名指定參數名 42OUT參

4、數的使用OUT參數是指輸出參數，由存儲過程中的語句為其賦值，并返回給用戶。使用這種模式的參數，必須在參數后面添加OUT關鍵字。43IN OUT參數的使用IN OUT參數同時擁有IN與OUT參數的特性，它既接受用戶的傳值，又允許在過程體中修改其值，并可以將值返回。使用這種模式的參數需要在參數后面添加IN OUT關鍵字。不過，IN OUT參數不接受常量值，只能使用變量為其傳值。610.1.2 帶參數的存儲過程4【例例10.3】創建帶IN參數的存儲過程update_emp2，為該過程設置兩個IN參數，分別用于接受用戶提供的empno與ename值，如下：SQL CREATE PROCEDURE up

5、date_emp2 2 ( emp_num IN NUMBER , emp_name IN VARCHAR2 ) AS 3 BEGIN 4 UPDATE emp SET ename = emp_name 5 WHERE empno = emp_num ; 6 END update_emp2 ; 7 /過程已創建。710.1.2 帶參數的存儲過程4【例例10.4】調用update_emp2過程，通過該過程將empno為6500的員工的ename修改為XIAOQI，如下：SQL EXEC update_emp2 (6500 , XIAOQI) ;PL/SQL 過程已成功完成。4【例例10.5】使用

6、指定參數名的形式調用update_emp2過程，如下：SQL EXEC update_emp2 (emp_name = XIAOQI , emp_num = 6500) ;PL/SQL 過程已成功完成。810.1.2 帶參數的存儲過程4【例例10.6】創建存儲過程select_emp，為該過程設置一個IN參數和一個OUT參數，其中IN參數接受用戶提供的empno值，然后在過程體中將該empno對應的ename值傳遞給OUT參數，如下：SQL CREATE PROCEDURE select_emp 2 ( emp_num IN NUMBER , emp_name OUT VARCHAR2 ) A

7、S 3 BEGIN 4 SELECT ename INTO emp_name 5 FROM emp WHERE empno = emp_num ; 6 END select_emp ; 7 /過程已創建。910.1.2 帶參數的存儲過程4【例例10.7】調用存儲過程select_emp，為其IN參數賦值為6500，并聲明變量employee_ name接受與輸出其OUT參數的返回值，如下：|SQL VARIABLE employee_name VARCHAR2(10) ;|SQL EXEC select_emp (6500 , :employee_name) ;|PL/SQL 過程已成功完成。

8、過程已成功完成。然后，需要使用PRINT命令查看變量employee_name中的值，如下：|SQL PRINT employee_name ;|EMPLOYEE_NAME|-|XIAOQI也可以使用SELECT語句查看變量employee_name中的值，語句如下：|SQL SELECT :employee_name FROM dual ;1010.1.2 帶參數的存儲過程4【例例10.8】創建存儲過程exchange_value，通過該過程交換兩個變量中的值，過程創建如下：參見教材P227|調用調用exchange_value過程，調用前過程，調用前聲明為聲明為IN OUT參數賦值的變量，

9、調用參數賦值的變量，調用后使用后使用SELECT語句輸出交換值后的結語句輸出交換值后的結果，如下：果，如下：參見教材P2271110.1.3 修改與刪除存儲過程4修改存儲過程是在CREATE PROCEDURE語句中添加OR REPLACE關鍵字，其他內容與創建存儲過程一樣，其實質是刪除原有過程，然后創建一個全新的過程，只不過前后兩個過程的名稱相同而已。4刪除存儲過程需要使用DROP PROCEDURE語句，其語法形式如下：DROP PROCEDURE procedure_name ;1210.1.4 查詢存儲過程的定義信息4對于創建好的存儲過程，如果想要了解其定義信息，可以查詢數據字典use

10、r_source。4【例例10.9】通過數據字典user_source查詢存儲過程select_emp的定義信息，如下：參見教材P2284其中，name表示對象名稱；type表示對象類型；line表示定義信息中文本所在的行數；text表示對應行的文本信息。1310.2 函 數4創建函數需要使用CREATE FUNCTION語句，其語法如下：CREATE OR REPLACE FUNCTION function_name ( parameter IN | OUT | IN OUT data_type ) , . RETURN data_type IS | AS declaration_secti

11、on ; BEGIN function_body ;END function_name ; 1410.3 實驗指導使用存儲過程與函數查詢圖書信息4實驗指導10-1：使用存儲過程和函數查詢圖書信息1創建函數get_prompt|首先創建函數首先創建函數get_prompt，如下：，如下：v參見教材參見教材P2302創建存儲過程get_book_information3調用過程|存儲過程和函數都已經創建好了，需要查詢存儲過程和函數都已經創建好了，需要查詢某圖書的信息時就可以直接調用某圖書的信息時就可以直接調用get_book_information存儲過程。例如獲取存儲過程。例如獲取bookid為

12、為2的圖書的信息，如下：的圖書的信息，如下：v參見教材參見教材P2311510.4 觸 發 器4觸發器是一種特殊的存儲過程，它在發生某種數據庫事件時由Oracle系統自動觸發。4觸發器通常用于加強數據的完整性約束和業務規則等，對于表來說，觸發器可以實現比CHECK約束更為復雜的約束。1610.4.1 觸發器的類型4DML觸發器：DML觸發器由DML語句觸發，例如INSERT、UPDATE和DELETE語句。 4INSTEAD OF觸發器：INSTEAD OF觸發器又稱替代觸發器，用于執行一個替代操作來代替觸發事件的操作。 4系統事件觸發器：系統事件觸發器在發生如數據庫啟動或關閉等系統事件時觸發

13、。4DDL觸發器：DDL觸發器由DDL語句觸發，例如CREATE、ALTER和DROP語句。DDL觸發器同樣可以分為BEFORE觸發器與AFTER觸發器。1710.4.2 創建觸發器4創建觸發器需要使用CREATE TRIGGER語句，其語法如下：CREATE OR REPLACE TRIGGER trigger_name BEFORE | AFTER | INSTEAD OF trigger_event ON table_name | view_name | DATABASE FOR EACH ROW ENABLE | DISABLE WHEN trigger_condition DECLA

14、RE declaration_statements ; BEGIN trigger_body ;END trigger_name ;1810.4.3 DML觸發器4DML觸發器由DML語句觸發，其對應的trigger_event具體內容如下： INSERT | DELETE | UPDATE OF column , . 4關于DML觸發器的說明如下：DML操作主要包括INSERT、DELETE和UPDATE操作，通常根據觸發器所針對的具體事件將DML觸發器分為INSERT觸發器、UPDATE觸發器和DELETE觸發器。可以將DML操作細化到列，即針對某列進行DML操作時激活觸發器。任何DML觸

15、發器都可以按觸發時間分為BEFORE觸發器與AFTER觸發器。在行級觸發器中，為了獲取某列在DML操作前后的數據，Oracle提供了兩種特殊的標識符:OLD和:NEW，通過:OLD.column_name的形式可以獲取該列的舊數據，而通過:NEW.column_name則可以獲取該列的新數據。 1910.4.3 DML觸發器4【例例10.11】為了演示觸發器的效果，下面首先創建兩個簡單的示例表：student(學生表)和record(記錄表)，并向student表中添加幾條記錄，如下：參見教材P2334創建AFTER UPDATE觸發器，要求在修改student表中的某行數據后，在record

16、表中記錄修改操作，并保存修改前的行數據。創建觸發器的語句如下：參見教材P2342010.4.4 INSTEAD OF觸發器4INSTEAD OF觸發器用于執行一個替代操作來代替觸發事件的操作，而觸發事件本身最終不會被執行。 4如果是DML觸發器，則無論是BEFORE觸發器還是AFTER觸發器，觸發事件最終都會被執行。4不過，Oracle中的INSTEAD OF觸發器不能針對表，而只能針對視圖。 2110.4.4 INSTEAD OF觸發器4【例例10.12】首先基于student表創建視圖student_view，該視圖檢索student表中的所有數據，但將student表中的sage列加1。

17、視圖創建如下：SQL CREATE VIEW student_view 2 AS 3 SELECT sid , sname , sage + 1 new_age 4 FROM student 5 WITH CHECK OPTION ;視圖已創建。2210.4.5 系統事件觸發器4系統事件觸發器是指由數據庫系統事件觸發的觸發器，其所支持的系統事件如表10-1所示。系統事件說 明LOGOFF用戶從數據庫注銷LOGON用戶登錄數據庫SERVERERROR服務器發生錯誤SHUTDOWN關閉數據庫實例STARTUP打開數據庫實例2310.4.5 系統事件觸發器4【例例10.13】在system用戶下創建

18、一個系統事件觸發器，該觸發器由LOGON事件觸發，記錄登錄用戶的用戶名(USER)與登錄時間，如下：SQL CONNECT system/admin已連接。SQL CREATE TRIGGER logon_trigger 2 AFTER LOGON 3 ON DATABASE 4 BEGIN 5 INSERT INTO logon_log VALUES ( USER , SYSDATE ) ; 6 END logon_trigger ; 7 /觸發器已創建2410.4.6 DDL觸發器4DDL觸發器由DDL語句觸發，按觸發時間可以分為BEFORE觸發器與AFTER觸發器，其所針對的事件包括CR

19、EATE、ALTER、DROP、ANALYZE、GRANT、COMMENT、REVOKE、RENAME、TRUNCATE、AUDIT、NOTAUDIT、ASSOCIATE STATISTICS和DISASSOCIATE STATISTICS。4創建DDL觸發器需要用戶具有DBA權限。2510.4.7 禁用與啟用觸發器4在創建觸發器時，可以使用ENABLE與DISABLE關鍵字指定觸發器的初始狀態為啟用或禁用，默認情況下為ENABLE。4在需要的時候，也可以使用ALTER TRIGGER語句修改觸發器的狀態，其語法如下：ALTER TRIGGER trigger_name ENABLE | DI

20、SABLE ;4如果需要修改某個表上的所有觸發器的狀態，還可以使用如下形式：ALTER TABLE table_name ENABLE | DISABLE ALL TRIGGERS ;2610.4.8 修改與刪除觸發器4修改觸發器只需要在CREATE TRIGGER語句中添加OR REPLACE關鍵字。4刪除觸發器需要使用DROP TRIGGER語句，其語法如下：DROP TRIGGER trigger_name ;2710.5 程 序 包4使用程序包主要是為了實現程序模塊化，程序包可以將相關的存儲過程、函數、變量、常量和游標等PL/SQL程序組合在一起，通過這種方式可以構建供程序人員重用的代

21、碼庫。4另外，當首次調用程序包中的存儲過程或函數等元素時，Oracle會將整個程序包調入內存，在下次調用包中的元素時，Oracle就可以直接從內存中讀取，從而提高程序的運行效率。2810.5.1 創建程序包41創建包規范創建包規范需要使用CREATE PACKAGE語句，其簡要語法如下：|CREATE OR REPLACE PACKAGE package_name| IS | AS |package_specification ;|END package_name ;語法說明如下。|package_name：創建的包名。：創建的包名。|package_specification：用于列出用戶可

22、以使用的公共存：用于列出用戶可以使用的公共存儲過程、函數、類型和對象。儲過程、函數、類型和對象。2910.5.1 創建程序包42創建包體創建包體需要使用CREATE PACKAGE BODY語句，并且在創建時需要指定已創建的包，其簡要語法如下：|CREATE OR REPLACE PACKAGE BODY package_name| IS | AS |package_body ;|END package_name ;3010.5.2 調用程序包中的元素4DBMS_OUTPUT是系統定義的包，而PUT_LINE是該包中的存儲過程。可見調用程序包中的元素時，是使用如下形式：package_name. element_name ;4其中，