oracle11g第10章

1、2第10章 存儲過程、函數(shù)、觸發(fā)器和包4本章要點(diǎn)：掌握存儲過程的創(chuàng)建。熟練掌握帶參數(shù)的存儲過程的使用。掌握存儲過程的管理。掌握函數(shù)的創(chuàng)建與使用。了解觸發(fā)器的類型。理解觸發(fā)器的作用。熟練掌握各種類型的觸發(fā)器。了解程序包的創(chuàng)建與使用。310.1 存 儲 過 程4存儲過程是一組為了完成特定功能的SQL語句集，它大大提高了SQL語句的功能和靈活性。4存儲過程經(jīng)編譯后存儲在數(shù)據(jù)庫中，所以執(zhí)行存儲過程要比執(zhí)行存儲過程中封裝的SQL語句更有效率。410.1.1 創(chuàng)建與調(diào)用存儲過程4創(chuàng)建存儲過程需要使用CREATE PROCEDURE語句，其語法如下：CREATE OR REPLACE PROCEDURE p

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

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

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

5、E update_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 /過程已創(chuàng)建。810.1.2 帶參數(shù)的存儲過程4【例【例10.4】調(diào)用update_emp2過程，通過該過程將empno為6500的員工的ename修改為XIAOQI，如下：SQL EXEC update_emp2 (6500 , XIAOQI) ;PL/SQL 過程已成功完成。4【例【例1

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

7、AR2 ) AS 3 BEGIN 4 SELECT ename INTO emp_name 5 FROM emp WHERE empno = emp_num ; 6 END select_emp ; 7 /過程已創(chuàng)建。1010.1.2 帶參數(shù)的存儲過程4【例【例10.7】調(diào)用存儲過程select_emp，為其IN參數(shù)賦值為6500，并聲明變量employee_ name接受與輸出其OUT參數(shù)的返回值，如下：|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 ;1110.1.2 帶參數(shù)的存儲過程4【例【例10.8】創(chuàng)建存儲過程exchange_value，通過該過程交換兩個(gè)變量中的值，過程創(chuàng)建如下：參見教材P227|調(diào)用調(diào)用exchange_value過程，調(diào)用前過程，調(diào)用前聲明為聲明為IN O

9、UT參數(shù)賦值的變量，調(diào)用參數(shù)賦值的變量，調(diào)用后使用后使用SELECT語句輸出交換值后的結(jié)語句輸出交換值后的結(jié)果，如下：果，如下：參見教材P2271210.1.3 修改與刪除存儲過程4修改存儲過程是在CREATE PROCEDURE語句中添加OR REPLACE關(guān)鍵字，其他內(nèi)容與創(chuàng)建存儲過程一樣，其實(shí)質(zhì)是刪除原有過程，然后創(chuàng)建一個(gè)全新的過程，只不過前后兩個(gè)過程的名稱相同而已。4刪除存儲過程需要使用DROP PROCEDURE語句，其語法形式如下：DROP PROCEDURE procedure_name ;1310.1.4 查詢存儲過程的定義信息4對于創(chuàng)建好的存儲過程，如果想要了解其定義信息，可

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

11、ation_section ; BEGIN function_body ;END function_name ; 1510.3 實(shí)驗(yàn)指導(dǎo)使用存儲過程與函數(shù)查詢圖書信息4實(shí)驗(yàn)指導(dǎo)10-1：使用存儲過程和函數(shù)查詢圖書信息1創(chuàng)建函數(shù)get_prompt|首先創(chuàng)建函數(shù)首先創(chuàng)建函數(shù)get_prompt，如下：，如下：v參見教材參見教材P2302創(chuàng)建存儲過程get_book_information3調(diào)用過程|存儲過程和函數(shù)都已經(jīng)創(chuàng)建好了，需要查詢存儲過程和函數(shù)都已經(jīng)創(chuàng)建好了，需要查詢某圖書的信息時(shí)就可以直接調(diào)用某圖書的信息時(shí)就可以直接調(diào)用get_book_information存儲過程。例如獲取存儲過程。

12、例如獲取bookid為為2的圖書的信息，如下：的圖書的信息，如下：v參見教材參見教材P2311610.4 觸 發(fā) 器4觸發(fā)器是一種特殊的存儲過程，它在發(fā)生某種數(shù)據(jù)庫事件時(shí)由Oracle系統(tǒng)自動觸發(fā)。4觸發(fā)器通常用于加強(qiáng)數(shù)據(jù)的完整性約束和業(yè)務(wù)規(guī)則等，對于表來說，觸發(fā)器可以實(shí)現(xiàn)比CHECK約束更為復(fù)雜的約束。1710.4.1 觸發(fā)器的類型4DML觸發(fā)器：DML觸發(fā)器由DML語句觸發(fā)，例如INSERT、UPDATE和DELETE語句。 4INSTEAD OF觸發(fā)器：INSTEAD OF觸發(fā)器又稱替代觸發(fā)器，用于執(zhí)行一個(gè)替代操作來代替觸發(fā)事件的操作。 4系統(tǒng)事件觸發(fā)器：系統(tǒng)事件觸發(fā)器在發(fā)生如數(shù)據(jù)庫啟動

13、或關(guān)閉等系統(tǒng)事件時(shí)觸發(fā)。4DDL觸發(fā)器：DDL觸發(fā)器由DDL語句觸發(fā)，例如CREATE、ALTER和DROP語句。DDL觸發(fā)器同樣可以分為BEFORE觸發(fā)器與AFTER觸發(fā)器。1810.4.2 創(chuàng)建觸發(fā)器4創(chuàng)建觸發(fā)器需要使用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_cond

14、ition DECLARE declaration_statements ; BEGIN trigger_body ;END trigger_name ;1910.4.3 DML觸發(fā)器4DML觸發(fā)器由DML語句觸發(fā)，其對應(yīng)的trigger_event具體內(nèi)容如下： INSERT | DELETE | UPDATE OF column , . 4關(guān)于DML觸發(fā)器的說明如下：DML操作主要包括INSERT、DELETE和UPDATE操作，通常根據(jù)觸發(fā)器所針對的具體事件將DML觸發(fā)器分為INSERT觸發(fā)器、UPDATE觸發(fā)器和DELETE觸發(fā)器。可以將DML操作細(xì)化到列，即針對某列進(jìn)行DML操作時(shí)激

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

16、行數(shù)據(jù)后，在record表中記錄修改操作，并保存修改前的行數(shù)據(jù)。創(chuàng)建觸發(fā)器的語句如下：參見教材P2342110.4.4 INSTEAD OF觸發(fā)器4INSTEAD OF觸發(fā)器用于執(zhí)行一個(gè)替代操作來代替觸發(fā)事件的操作，而觸發(fā)事件本身最終不會被執(zhí)行。 4如果是DML觸發(fā)器，則無論是BEFORE觸發(fā)器還是AFTER觸發(fā)器，觸發(fā)事件最終都會被執(zhí)行。4不過，Oracle中的INSTEAD OF觸發(fā)器不能針對表，而只能針對視圖。 2210.4.4 INSTEAD OF觸發(fā)器4【例【例10.12】首先基于student表創(chuàng)建視圖student_view，該視圖檢索student表中的所有數(shù)據(jù)，但將stude

17、nt表中的sage列加1。視圖創(chuàng)建如下：SQL CREATE VIEW student_view 2 AS 3 SELECT sid , sname , sage + 1 new_age 4 FROM student 5 WITH CHECK OPTION ;視圖已創(chuàng)建。2310.4.5 系統(tǒng)事件觸發(fā)器4系統(tǒng)事件觸發(fā)器是指由數(shù)據(jù)庫系統(tǒng)事件觸發(fā)的觸發(fā)器，其所支持的系統(tǒng)事件如表10-1所示。系統(tǒng)事件說 明LOGOFF用戶從數(shù)據(jù)庫注銷LOGON用戶登錄數(shù)據(jù)庫SERVERERROR服務(wù)器發(fā)生錯(cuò)誤SHUTDOWN關(guān)閉數(shù)據(jù)庫實(shí)例STARTUP打開數(shù)據(jù)庫實(shí)例2410.4.5 系統(tǒng)事件觸發(fā)器4【例【例10.1

18、3】在system用戶下創(chuàng)建一個(gè)系統(tǒng)事件觸發(fā)器，該觸發(fā)器由LOGON事件觸發(fā)，記錄登錄用戶的用戶名(USER)與登錄時(shí)間，如下：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 /觸發(fā)器已創(chuàng)建2510.4.6 DDL觸發(fā)器4DDL觸發(fā)器由DDL語句觸發(fā)，按觸發(fā)時(shí)間可以分為BEFORE觸發(fā)器與AFTER觸

19、發(fā)器，其所針對的事件包括CREATE、ALTER、DROP、ANALYZE、GRANT、COMMENT、REVOKE、RENAME、TRUNCATE、AUDIT、NOTAUDIT、ASSOCIATE STATISTICS和DISASSOCIATE STATISTICS。4創(chuàng)建DDL觸發(fā)器需要用戶具有DBA權(quán)限。2610.4.7 禁用與啟用觸發(fā)器4在創(chuàng)建觸發(fā)器時(shí)，可以使用ENABLE與DISABLE關(guān)鍵字指定觸發(fā)器的初始狀態(tài)為啟用或禁用，默認(rèn)情況下為ENABLE。4在需要的時(shí)候，也可以使用ALTER TRIGGER語句修改觸發(fā)器的狀態(tài)，其語法如下：ALTER TRIGGER trigger_na

20、me ENABLE | DISABLE ;4如果需要修改某個(gè)表上的所有觸發(fā)器的狀態(tài)，還可以使用如下形式：ALTER TABLE table_name ENABLE | DISABLE ALL TRIGGERS ;2710.4.8 修改與刪除觸發(fā)器4修改觸發(fā)器只需要在CREATE TRIGGER語句中添加OR REPLACE關(guān)鍵字。4刪除觸發(fā)器需要使用DROP TRIGGER語句，其語法如下：DROP TRIGGER trigger_name ;2810.5 程 序 包4使用程序包主要是為了實(shí)現(xiàn)程序模塊化，程序包可以將相關(guān)的存儲過程、函數(shù)、變量、常量和游標(biāo)等PL/SQL程序組合在一起，通過這種方

21、式可以構(gòu)建供程序人員重用的代碼庫。4另外，當(dāng)首次調(diào)用程序包中的存儲過程或函數(shù)等元素時(shí)，Oracle會將整個(gè)程序包調(diào)入內(nèi)存，在下次調(diào)用包中的元素時(shí)，Oracle就可以直接從內(nèi)存中讀取，從而提高程序的運(yùn)行效率。2910.5.1 創(chuàng)建程序包41創(chuàng)建包規(guī)范創(chuàng)建包規(guī)范需要使用CREATE PACKAGE語句，其簡要語法如下：|CREATE OR REPLACE PACKAGE package_name| IS | AS |package_specification ;|END package_name ;語法說明如下。|package_name：創(chuàng)建的包名。：創(chuàng)建的包名。|package_specifi

22、cation：用于列出用戶可以使用的公共存：用于列出用戶可以使用的公共存儲過程、函數(shù)、類型和對象。儲過程、函數(shù)、類型和對象。3010.5.1 創(chuàng)建程序包42創(chuàng)建包體創(chuàng)建包體需要使用CREATE PACKAGE BODY語句，并且在創(chuàng)建時(shí)需要指定已創(chuàng)建的包，其簡要語法如下：|CREATE OR REPLACE PACKAGE BODY package_name| IS | AS |package_body ;|END package_name ;3110.5.2 調(diào)用程序包中的元素4DBMS_OUTPUT是系統(tǒng)定義的包，而PUT_LINE是該包中的存儲過程?？梢娬{(diào)用程序包中的元素時(shí)，是使用如下形式：package_name. element_name ;4其