1、第第6章章 子程序和觸發器子程序和觸發器理論部分理論部分本章目標 學會在Oracle中使用過程 學會在Oracle中使用函數 學會在Oracle中使用包 掌握觸發器的使用子程序 子程序是已命名的PL/SQL塊，它們存儲在數據庫中，可以為它們指定參數，可以從任何數據庫客戶端和應用程序中調用它們 子程序包括存儲過程和函數 使用存儲過程執行操作，使用函數執行操作并返回值，就像應用程序中的方法 子程序具有聲明部分、可執行部分、異常部分（可選）過程 過程是用于完成特定任務的子程序 例如：前往售票廳詢問關于車票的信息 排隊等候 在柜臺購買車票 子程序過程定義CREATE OR REPLACE PROCED

2、URE 過程名(參數) IS|AS -聲明部分 BEGIN -可執行部分EXCEPTION -異常處理部分END 過程名; 過程CREATE OR REPLACE PROCEDURE find_emp(emp_no NUMBER)AS empname VARCHAR2(20);BEGIN SELECT ename INTO empname FROM EMP WHERE empno = emp_no; DBMS_OUTPUT.PUT_LINE(雇員姓名是 | empname);EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE (雇員編號

3、未找到);END find_emp;/子程序過程調用命令行調用：EXECUTE 過程名(參數)子程序調用：過程名(參數)子程序 調用函數SQL SET SERVEROUTPUT ONSQL EXECUTE find_emp (7369);DECLAREvalue1 NUMBER:=7369;BEGIN find_emp(value1);END;子程序參數類型INOUTIN OUT子程序SQL CREATE OR REPLACE PROCEDURE test( value1 IN VARCHAR2, value2 OUT NUMBER )IS identity NUMBER;BEGIN SELE

4、CT ITEMRATE INTO identity FROM itemFile WHERE itemcode = value1; IF identity SET SERVEROUT ONSQL DECLARE num1 NUMBER := 100; num2 NUMBER := 200;BEGIN swap(num1, num2); DBMS_OUTPUT.PUT_LINE(num1 = | num1); DBMS_OUTPUT.PUT_LINE(num2 = | num2);END;/12過程 將過程的執行權限授予其他用戶： 刪除過程：SQL GRANT EXECUTE ON find_em

5、p TO MARTIN;SQL GRANT EXECUTE ON swap TO PUBLIC;SQL DROP PROCEDURE find_emp;子程序函數定義CREATE OR REPLACE FUNCTION 函數名(過程參數列表) RETURN 數據類型 IS|AS -聲明部分 BEGIN -可執行部分EXCEPTION -異常處理部分END 函數名; 函數 創建函數： 從 SQL 語句調用函數：CREATE OR REPLACE FUNCTION fun_hello RETURN VARCHAR2ISBEGIN RETURN 朋友，您好;END;/SQL SELECT fun_h

6、ello FROM DUAL;15函數 CREATE OR REPLACE FUNCTION item_price_range (price NUMBER) RETURN VARCHAR2 AS min_price NUMBER; max_price NUMBER;BEGIN SELECT MAX(ITEMRATE), MIN(ITEMRATE) INTO max_price, min_price FROM itemfile; IF price = min_price AND price CREATE OR REPLACE TRIGGER trig_salAFTER UPDATE OF emp

7、sal ON salary_records觸發器語句為 salary_records 表創建 trig-sal 觸發器在更新 emp_sal 列之后激活觸發器觸發器限制SQL FOR EACH ROWWHEN (NEW.empsalOLD.empsal)DECLARESal_diff NUMBER;只有在WHEN子句中的條件得到滿足時，才激活trig_sal 觸發器觸發器操作SQL BEGIN sal_diff:=:NEW.empsal-:OLD.empsal; DBMS_OUTPUT.PUT_LINE(工資差額：sal_diff);END;如果WHEN子句中的條件得到滿足，將執行BEGIN

8、塊中的代碼觸發器的組成部分 3-3Oracle 數據庫更新表表保存更新激活觸發器觸發器AFTER 觸發器的工作原理BEFORE 觸發器的工作原理更新表表激活觸發器觸發器保存更新Oracle 數據庫創建觸發器CREATE OR REPLACE TRIGGER aiu_itemfileAFTER INSERTON itemfileFOR EACH ROWBEGIN IF (:NEW.qty_hand = 0) THEN DBMS_OUTPUT.PUT_LINE(警告：已插入記錄，但數量為零); ELSE DBMS_OUTPUT.PUT_LINE(已插入記錄); END IF;END;/觸發器類型

9、觸發器的類型有：觸發器類型模式(DDL)觸發器DML觸發器數據庫級觸發器語句級觸發器行級觸發器INSTEAD OF觸發器觸發器類型q DDL 觸發器q 數據庫級觸發器q DML 觸發器q 語句級觸發器q 行級觸發器q INSTEAD OF 觸發器在模式中執行 DDL 語句時執行在發生打開、關閉、登錄和退出數據庫等系統事件時執行在對表或視圖執行DML語句時執行無論受影響的行數是多少，都只執行一次對DML語句修改的每個行執行一次用于用戶不能直接使用 DML 語句修改的視圖觸發器類型 行級觸發器SQL CREATE TABLE TEST_TRG (ID NUMBER, NAME VARCHAR2(2

10、0);SQL CREATE SEQUENCE SEQ_TEST;SQL CREATE OR REPLACE TRIGGER BI_TEST_TRGBEFORE INSERT OR UPDATE OF IDON TEST_TRGFOR EACH ROWBEGIN IF INSERTING THEN SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL; ELSE RAISE_APPLICATION_ERROR(-20020, 不允許更新ID值！); END IF;END;/觸發器類型SQL CREATE OR REPLACE TRIGGER trgdemo

11、 AFTER INSERT OR UPDATE OR DELETE ON order_masterBEGIN IF UPDATING THEN DBMS_OUTPUT.PUT_LINE(已更新 ORDER_MASTER 中的數據); ELSIF DELETING THEN DBMS_OUTPUT.PUT_LINE(已刪除 ORDER_MASTER 中的數據); ELSIF INSERTING THEN DBMS_OUTPUT.PUT_LINE(已在 ORDER_MASTER 中插入數據); END IF;END;/ 語句級觸發器觸發器類型SQL CREATE OR REPLACE TRIGGE

12、R upd_ord_view INSTEAD OF UPDATE ON ord_view FOR EACH ROW BEGIN UPDATE order_master SET vencode=:NEW.vencode WHERE orderno = :NEW.orderno;DBMS_OUTPUT.PUT_LINE(已激活觸發器); END; / INSTEAD OF 觸發器33觸發器類型SQL CREATE TABLE dropped_obj ( obj_name VARCHAR2(30), obj_type VARCHAR2(20), drop_date DATE);SQL CREATE OR REPLACE TRIGGER log_drop_objAFTER DROP ON SCHEMABEGIN INSERT INTO dropped_obj VALUES( ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE);END;/ 模式觸發器觸發器管理觸發器 語法：ALTER TRIGGER 觸發器名稱 ENABLE|DISABLE禁用一張表上的所有觸發器ALTER TABLE 表名