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

1、第第6章章 子程序和觸發(fā)器子程序和觸發(fā)器理論部分理論部分本章目標(biāo) 學(xué)會(huì)在Oracle中使用過(guò)程 學(xué)會(huì)在Oracle中使用函數(shù) 學(xué)會(huì)在Oracle中使用包 掌握觸發(fā)器的使用子程序 子程序是已命名的PL/SQL塊，它們存儲(chǔ)在數(shù)據(jù)庫(kù)中，可以為它們指定參數(shù)，可以從任何數(shù)據(jù)庫(kù)客戶端和應(yīng)用程序中調(diào)用它們 子程序包括存儲(chǔ)過(guò)程和函數(shù) 使用存儲(chǔ)過(guò)程執(zhí)行操作，使用函數(shù)執(zhí)行操作并返回值，就像應(yīng)用程序中的方法 子程序具有聲明部分、可執(zhí)行部分、異常部分（可選）過(guò)程 過(guò)程是用于完成特定任務(wù)的子程序 例如：前往售票廳詢問(wèn)關(guān)于車票的信息 排隊(duì)等候 在柜臺(tái)購(gòu)買車票 子程序過(guò)程定義CREATE OR REPLACE PROCED

2、URE 過(guò)程名(參數(shù)) IS|AS -聲明部分 BEGIN -可執(zhí)行部分EXCEPTION -異常處理部分END 過(guò)程名; 過(guò)程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 (雇員編號(hào)

3、未找到);END find_emp;/子程序過(guò)程調(diào)用命令行調(diào)用：EXECUTE 過(guò)程名(參數(shù))子程序調(diào)用：過(guò)程名(參數(shù))子程序 調(diào)用函數(shù)SQL SET SERVEROUTPUT ONSQL EXECUTE find_emp (7369);DECLAREvalue1 NUMBER:=7369;BEGIN find_emp(value1);END;子程序參數(shù)類型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過(guò)程 將過(guò)程的執(zhí)行權(quán)限授予其他用戶： 刪除過(guò)程：SQL GRANT EXECUTE ON find_em

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

6、ello FROM DUAL;15函數(shù) 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觸發(fā)器語(yǔ)句為 salary_records 表創(chuàng)建 trig-sal 觸發(fā)器在更新 emp_sal 列之后激活觸發(fā)器觸發(fā)器限制SQL FOR EACH ROWWHEN (NEW.empsalOLD.empsal)DECLARESal_diff NUMBER;只有在WHEN子句中的條件得到滿足時(shí)，才激活trig_sal 觸發(fā)器觸發(fā)器操作SQL BEGIN sal_diff:=:NEW.empsal-:OLD.empsal; DBMS_OUTPUT.PUT_LINE(工資差額：sal_diff);END;如果WHEN子句中的條件得到滿足，將執(zhí)行BEGIN

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

9、觸發(fā)器的類型有：觸發(fā)器類型模式(DDL)觸發(fā)器DML觸發(fā)器數(shù)據(jù)庫(kù)級(jí)觸發(fā)器語(yǔ)句級(jí)觸發(fā)器行級(jí)觸發(fā)器INSTEAD OF觸發(fā)器觸發(fā)器類型q DDL 觸發(fā)器q 數(shù)據(jù)庫(kù)級(jí)觸發(fā)器q DML 觸發(fā)器q 語(yǔ)句級(jí)觸發(fā)器q 行級(jí)觸發(fā)器q INSTEAD OF 觸發(fā)器在模式中執(zhí)行 DDL 語(yǔ)句時(shí)執(zhí)行在發(fā)生打開、關(guān)閉、登錄和退出數(shù)據(jù)庫(kù)等系統(tǒng)事件時(shí)執(zhí)行在對(duì)表或視圖執(zhí)行DML語(yǔ)句時(shí)執(zhí)行無(wú)論受影響的行數(shù)是多少，都只執(zhí)行一次對(duì)DML語(yǔ)句修改的每個(gè)行執(zhí)行一次用于用戶不能直接使用 DML 語(yǔ)句修改的視圖觸發(fā)器類型 行級(jí)觸發(fā)器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;/觸發(fā)器類型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 中的數(shù)據(jù)); ELSIF DELETING THEN DBMS_OUTPUT.PUT_LINE(已刪除 ORDER_MASTER 中的數(shù)據(jù)); ELSIF INSERTING THEN DBMS_OUTPUT.PUT_LINE(已在 ORDER_MASTER 中插入數(shù)據(jù)); END IF;END;/ 語(yǔ)句級(jí)觸發(fā)器觸發(fā)器類型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(已激活觸發(fā)器); END; / INSTEAD OF 觸發(fā)器33觸發(fā)器類型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;/ 模式觸發(fā)器觸發(fā)器管理觸發(fā)器 語(yǔ)法：ALTER TRIGGER 觸發(fā)器名稱 ENABLE|DISABLE禁用一張表上的所有觸發(fā)器ALTER TABLE 表名