第6章 子程序和触发器

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 表名