触发器
范例： 只有在每个月的10日才允许办理新雇员入职与离职，其他时间不允许增加新雇员数据
|
CREATE OR REPLACE TRIGGER forbid_emp_trigger BEFORE INSERT OR DELETE ON emp DECLARE v_currentdate VARCHAR(20) ; BEGIN SELECT TO_CHAR(SYSDATE,'dd') INTO v_currentdate FROM dual ; IF TRIM(v_currentdate)!='10' THEN RAISE_APPLICATION_ERROR(-20008,'在每月的10号才允许办理入职手续！') ; END IF ; END ; / |
范例：向emp表之中增加新雇员信息，当前不是当月10日
|
INSERT INTO emp (empno,ename,job,hiredate,sal,comm,mgr,deptno) VALUES (8998,'MLDN','MANAGER',SYSDATE,2000,500,7369,40) ; |
范例：在星期一、周末以及每天下班时间（每天9:00以前、18:00以后）后不允许更新emp数据表
|
CREATE OR REPLACE TRIGGER forbid_emp_trigger BEFORE INSERT OR DELETE OR UPDATE ON emp DECLARE v_currentweak VARCHAR(20) ; v_currenthour VARCHAR(20) ; BEGIN SELECT TO_CHAR(SYSDATE,'day'),TO_CHAR(SYSDATE,'hh24') INTO v_currentweak, v_currenthour FROM dual ; IF TRIM(v_currentweak)='星期一' OR TRIM(v_currentweak)='星期六' OR TRIM(v_currentweak)='星期日' THEN RAISE_APPLICATION_ERROR(-20008,'在周末及周一不允许更新emp数据表！') ; ELSIF TRIM(v_currenthour)<'9' OR TRIM(v_currenthour)>'18' THEN RAISE_APPLICATION_ERROR(-20009,'在下班时间不能够修改emp表数据！') ; END IF ; END ; / |
范例：假设当前日期时间为周日，发出以下增加数据操作
|
INSERT INTO emp (empno,ename,job,hiredate,sal,comm,mgr,deptno) VALUES (8998,'MLDN','MANAGER',SYSDATE,2000,500,7369,40) ; |
范例：在每天12点以后，不允许修改雇员工资和佣金
|
CREATE OR REPLACE TRIGGER forbid_emp_trigger BEFORE UPDATE OF sal,comm ON emp DECLARE v_currenthour VARCHAR(20) ; BEGIN SELECT TO_CHAR(SYSDATE,'hh24') INTO v_currenthour FROM dual ; IF TRIM(v_currenthour)>'12' THEN RAISE_APPLICATION_ERROR(-20009,'每天12点以后不允许更新雇员工资、佣金。') ; END IF ; END ; / |
范例：当前时间已超过中午12点，更新emp表中的sal和comm字段
|
UPDATE emp SET sal=9000,comm=5000 WHERE empno=7369 ; |
范例：每一位雇员都要根据其收入上缴所得税，假设所得税的上缴原则为：2000以下上缴3%、2000 ~ 5000上缴8%、5000以上上缴10%，现在要求建立一张新的数据表，可以记录出雇员的编号、姓名、工资、佣金、上缴所得税数据，并且在每次修改雇员表中sal和comm字段后可以自动更新记录。
|
DROP TABLE emp_tax PURGE ; CREATE TABLE emp_tax ( empno NUMBER(4) , ename VARCHAR2(10) , sal NUMBER(7,2) , comm NUMBER(7,2) , tax NUMBER(7,2) , CONSTRAINT pk_empno PRIMARY KEY (empno) , CONSTRAINT fk_empno FOREIGN KEY (empno) REFERENCES emp(empno) ON DELETE CASCADE ) ; |
|
CREATE OR REPLACE TRIGGER forbid_emp_trigger AFTER UPDATE OR INSERT OF ename , sal , comm ON emp DECLARE PRAGMA AUTONOMOUS_TRANSACTION ; -- 触发器自治事务 CURSOR cur_emp IS SELECT * FROM emp ; -- 定义游标，找到每行纪录 v_empRow emp%ROWTYPE ; -- 保存emp的每行纪录 v_salary emp.sal%TYPE ; -- 计算总收入 v_empTax emp_tax.tax%TYPE ; -- 保存税收的数值 BEGIN DELETE FROM emp_tax ; -- 清空emp_tax表的纪录 FOR v_empRow IN cur_emp LOOP v_salary := v_empRow.sal + NVL(v_empRow.comm , 0) ; -- 计算总工资 IF v_salary < 2000 THEN v_empTax := v_salary * 0.03 ; -- 上缴3%的税 ELSIF v_salary BETWEEN 2000 AND 5000 THEN v_empTax := v_salary * 0.08 ; -- 上缴8%的税 ELSIF v_salary > 5000 THEN v_empTax := v_salary * 0.1 ; -- 上缴10%的税 END IF ; INSERT INTO emp_tax(empno,ename,sal,comm,tax) VALUES (v_empRow.empno , v_empRow.ename , v_empRow.sal , v_empRow.comm , v_empTax) ; END LOOP ; COMMIT ; -- 提交事务 END ; / |
范例：向emp表中增加一条新的记录，而后查询emp_tax表记录
|
INSERT INTO emp (empno,ename,job,hiredate,sal,comm,mgr,deptno) VALUES (9898,'MLDN','MANAGER',SYSDATE,2000,500,7369,40) ; |
范例：增加完成后查询emp_tax表记录
|
SELECT * FROM emp_tax ; |
范例：增加雇员信息时，其职位必须在已有职位之内选择，并且工资不能超过5000
|
CREATE OR REPLACE TRIGGER forbid_emp_trigger BEFORE INSERT ON EMP FOR EACH ROW DECLARE v_jobCount NUMBER ; BEGIN SELECT COUNT(empno) INTO v_jobCount FROM emp WHERE :new.job IN ( SELECT DISTINCT job FROM emp) ; IF v_jobCount = 0 THEN -- 没有此职位信息 RAISE_APPLICATION_ERROR(-20008,'增加雇员的职位信息名称错误！') ; ELSE IF :new.sal > 5000 THEN RAISE_APPLICATION_ERROR(-20008,'增加雇员的工资不得超过5000！') ; END IF ; END IF ; END ; / |
范例：插入错误的数据
|
INSERT INTO emp (empno,ename,job,hiredate,sal,comm,mgr,deptno) VALUES (8998,'MLDN','经理',SYSDATE,9000,500,7369,40) ; |
范例：修改emp表的基本工资涨幅不能超过10%
|
CREATE OR REPLACE TRIGGER emp_update_trigger BEFORE UPDATE OF sal ON emp FOR EACH ROW BEGIN IF ABS((:new.sal-:old.sal)/:old.sal) > 0.1 THEN RAISE_APPLICATION_ERROR(-20008,'雇员工资修改幅度太大！') ; END IF ; END; / |
范例：将雇员编号是7369的雇员工资增长为5000
|
UPDATE emp SET sal=5000 WHERE empno=7369 ; |
范例：不能删除所有10部门的雇员
|
CREATE OR REPLACE TRIGGER emp_delete_trigger BEFORE DELETE ON emp FOR EACH ROW BEGIN IF :old.deptno=10 THEN RAISE_APPLICATION_ERROR(-20008,:old.empno || '为10部门雇员，无法删除此部门雇员！') ; END IF ; END; / |
范例：删除雇员编号是7839的雇员信息（此雇员在10部门）
|
DELETE FROM emp WHERE empno=7839 ; |
范例：错误的使用标识符“:new”和“:old”
|
CREATE OR REPLACE TRIGGER emp_error_trigger BEFORE UPDATE ON emp FOR EACH ROW DECLARE v_empRow emp%ROWTYPE ; BEGIN v_empRow := :old ; -- 错误 END; / |
范例：错误的程序 —— 在触发器之中无法修改old数据
|
CREATE OR REPLACE TRIGGER emp_update_old_trigger BEFORE UPDATE OF sal ON emp FOR EACH ROW BEGIN :old.sal := 100 ; -- 错误，无法修改old数据 END; / |
范例：数据库创建脚本
|
DROP SEQUENCE member_sequence ; DROP TABLE member PURGE ; DROP TABLE membertemp PURGE ; CREATE SEQUENCE member_sequence ; CREATE TABLE member( mid NUMBER , name VARCHAR2(30) , address VARCHAR2(50) , CONSTRAINT pk_mid PRIMARY KEY(mid) ) ; CREATE TABLE membertemp AS SELECT * FROM member WHERE 1=2 ; |
范例：在触发器之中修改new数据
|
CREATE OR REPLACE TRIGGER member_insert_trigger BEFORE INSERT ON membertemp FOR EACH ROW BEGIN DELETE FROM membertemp ; INSERT INTO member(mid,name,address) VALUES (member_sequence.NEXTVAL,:new.name ,:new.address ) ; END; / |
范例：向membertemp表中执行增加数据操作
|
INSERT INTO membertemp(name,address) VALUES ('李兴华','中国') ; INSERT INTO membertemp(name,address) VALUES ('董 楠','MLDN') ; COMMIT ; |
范例：查询member表数据
|
SELECT * FROM member ; |
范例：换种方式实现本程序的触发器
|
CREATE OR REPLACE TRIGGER member_insert_trigger BEFORE INSERT ON membertemp FOR EACH ROW DECLARE v_memberRow member%ROWTYPE ; BEGIN DELETE FROM membertemp ; SELECT member_sequence.NEXTVAL INTO :new.mid FROM dual ; v_memberRow.mid := :new.mid ; v_memberRow.name := :new.name ; v_memberRow.address := :new.address ; INSERT INTO member VALUES v_memberRow ; END; / |
范例：通过REFERENCING子句设置别名（修改雇员工资涨副触发器）
|
CREATE OR REPLACE TRIGGER emp_insert_trigger BEFORE UPDATE OF sal ON emp REFERENCING old AS emp_old new AS emp_new FOR EACH ROW BEGIN IF ABS((:emp_new.sal-:emp_old.sal)/:emp_old.sal) > 0.1 THEN RAISE_APPLICATION_ERROR(-20008,'雇员工资涨副太大！') ; END IF ; END; / |
范例：在增加雇员时，判断雇员工资是否存在，如果工资为0则报错
|
CREATE OR REPLACE TRIGGER emp_insert_trigger BEFORE INSERT ON emp FOR EACH ROW WHEN (new.sal = 0) BEGIN RAISE_APPLICATION_ERROR(-20008,:new.empno || '的工资为0，不符合工资规定！') ; END; / |
范例：增加新雇员，工资为0 —— 不符合操作要求
|
INSERT INTO emp (empno,ename,job,hiredate,sal,comm,mgr,deptno) VALUES (8998,'MLDN','经理',SYSDATE,0,500,7369,40) ; |
范例：要求工资只能上涨，不能降低
|
CREATE OR REPLACE TRIGGER emp_sal_update_trigger BEFORE UPDATE ON emp FOR EACH ROW WHEN (new.sal<old.sal) BEGIN RAISE_APPLICATION_ERROR(-20008,:old.empno || '的工资少于其原本工资，无法更新！') ; END; / |
范例：将7369的工资修改为300（原本为800，现在修改为300，属于降低工资，满足触发条件）
|
UPDATE emp SET sal=300 WHERE empno=7369 ; |
范例：定义DEPT_LOG表
|
DROP TABLE dept_log PURGE ; DROP SEQUENCE dept_log_seq ; CREATE SEQUENCE dept_log_seq ; CREATE TABLE dept_log ( logid NUMBER , type VARCHAR2(20) NOT NULL , deptno NUMBER(2) , logdate DATE , dname VARCHAR2(14) NOT NULL , loc VARCHAR2(13) NOT NULL , CONSTRAINT pk_logid PRIMARY KEY (logid) ) ; |
范例：定义触发器，针对于不同的DML操作进行日志记录
|
CREATE OR REPLACE TRIGGER dept_update_trigger BEFORE INSERT OR UPDATE OR DELETE ON dept FOR EACH ROW BEGIN IF INSERTING THEN INSERT INTO dept_log (logid,type,logdate,deptno,dname,loc) VALUES (dept_log_seq.nextval,'INSERT',SYSDATE,:new.deptno,:new.dname,:new.loc) ; ELSIF UPDATING THEN INSERT INTO dept_log (logid,type,logdate,deptno,dname,loc) VALUES (dept_log_seq.nextval,'UPDATE',SYSDATE,:new.deptno,:new.dname,:new.loc) ; ELSE -- 相当于DELETING INSERT INTO dept_log (logid,type,logdate,deptno,dname,loc) VALUES (dept_log_seq.nextval,'DELETE',SYSDATE,:old.deptno,:old.dname,:old.loc) ; END IF ; END; / |
范例：向dept表中执行一些DML操作
|
INSERT INTO dept(deptno,dname,loc) VALUES (50,'MLDN','北京') ; INSERT INTO dept(deptno,dname,loc) VALUES (60,'教学部','天津') ; UPDATE dept SET dname='北京' WHERE deptno=60 ; UPDATE dept SET dname='MLDNJAVA' WHERE deptno=50 ; DELETE FROM dept WHERE deptno=60 ; COMMIT ; |
范例：查询dept_log数据表
|
SELECT * FROM dept_log ; |
范例：定义三个针对于增加操作的触发器
|
CREATE OR REPLACE TRIGGER emp_insert_one BEFORE INSERT ON emp FOR EACH ROW BEGIN DBMS_OUTPUT.put_line('执行第1个触发器（emp_insert_one）') ; END ; / |
|
CREATE OR REPLACE TRIGGER emp_insert_two BEFORE INSERT ON emp FOR EACH ROW BEGIN DBMS_OUTPUT.put_line('执行第2个触发器（emp_insert_two）') ; END ; / |
|
CREATE OR REPLACE TRIGGER emp_insert_three BEFORE INSERT ON emp FOR EACH ROW BEGIN DBMS_OUTPUT.put_line('执行第3个触发器（emp_insert_three）') ; END ; / |
范例：编写增加数据操作
|
INSERT INTO emp (empno,ename,job,hiredate,sal,comm,mgr,deptno) VALUES (8998,'MLDN','经理',SYSDATE,0,500,7369,40) ; |
范例：修改触发器创建语法
|
CREATE OR REPLACE TRIGGER emp_insert_one BEFORE INSERT ON emp FOR EACH ROW BEGIN DBMS_OUTPUT.put_line('执行第1个触发器（emp_insert_one）') ; END ; / |
|
CREATE OR REPLACE TRIGGER emp_insert_two BEFORE INSERT ON emp FOR EACH ROW FOLLOWS emp_insert_one BEGIN DBMS_OUTPUT.put_line('执行第2个触发器（emp_insert_two）') ; END ; / |
|
CREATE OR REPLACE TRIGGER emp_insert_three BEFORE INSERT ON emp FOR EACH ROW FOLLOWS emp_insert_two BEGIN DBMS_OUTPUT.put_line('执行第3个触发器（emp_insert_three）') ; END ; / |
范例：定义一张数据表
|
DROP TABLE info PURGE ; CREATE TABLE info( id NUMBER , title VARCHAR2(50) ) ; INSERT INTO info (id,title) VALUES (1,'www.mldnjava.cn') ; |
范例：为info表增加一个触发器
|
CREATE OR REPLACE TRIGGER info_trigger BEFORE INSERT OR UPDATE OR DELETE ON info FOR EACH ROW DECLARE v_infocount NUMBER ; BEGIN SELECT COUNT(id) INTO v_infocount FROM info ; END ; / |
范例：执行更新操作
|
UPDATE info SET id=2 ; |
范例：验证复合触发器
|
CREATE OR REPLACE TRIGGER compound_trigger FOR INSERT OR UPDATE OR DELETE ON dept COMPOUND TRIGGER BEFORE STATEMENT IS-- 语句执行前触发（表级） BEGIN DBMS_OUTPUT.put_line('1、BEFORE STATEMENT .') ; END BEFORE STATEMENT ; BEFORE EACH ROW IS-- 语句执行前触发（行级） BEGIN DBMS_OUTPUT.put_line('2、BEFORE EACH ROW .') ; END BEFORE EACH ROW ; AFTER STATEMENT IS-- 语句执行后触发（表级） BEGIN DBMS_OUTPUT.put_line('3、AFTER STATEMENT .') ; END AFTER STATEMENT ; AFTER EACH ROW IS-- 语句执行后触发（行级） BEGIN DBMS_OUTPUT.put_line('4、AFTER EACH ROW .') ; END AFTER EACH ROW ; END ; / |
范例：向dept表中增加一条新数据
|
INSERT INTO dept(deptno,dname,loc) VALUES (99,'MLDNJAVA','北京') ; |
范例：定义复合触发器
|
CREATE OR REPLACE TRIGGER compound_trigger FOR INSERT OR UPDATE OR DELETE ON dept COMPOUND TRIGGER BEFORE EACH ROW IS -- 语句执行前触发（行级） BEGIN IF INSERTING THEN IF :new.dname IS NULL THEN :new.dname := 'MLDNJAVA' ; END IF ; IF :new.loc IS NULL THEN :new.loc := '中国' ; END IF ; END IF ; END BEFORE EACH ROW ; END ; / |
范例：增加一条部门信息
|
INSERT INTO dept(deptno) VALUES (99) ; COMMIT ; |
范例：查询更新后的dept表记录
|
SELECT * FROM dept ; |
范例：定义触发器
|
CREATE OR REPLACE TRIGGER emp_compound_trigger FOR INSERT OR UPDATE OR DELETE ON emp COMPOUND TRIGGER BEFORE STATEMENT IS -- 周末不允许更新 v_currentweak VARCHAR2(20) ; BEGIN SELECT TO_CHAR(SYSDATE,'day') INTO v_currentweak FROM dual ; IF TRIM(v_currentweak) IN ('星期六' , '星期日') THEN RAISE_APPLICATION_ERROR(-20008,'在周末不允许更新emp数据表！') ; END IF ; END BEFORE STATEMENT ; BEFORE EACH ROW IS v_avgSal emp.sal%TYPE ; BEGIN IF INSERTING OR UPDATING THEN :new.ename := UPPER(:new.ename) ; :new.job := UPPER(:new.job) ; END IF ; IF INSERTING THEN SELECT AVG(sal) INTO v_avgSal FROM emp ; IF :new.sal > v_avgSal THEN RAISE_APPLICATION_ERROR(-20009,'新进雇员工资不得高于公司平均工资！') ; END IF ; END IF ; END BEFORE EACH ROW ; END ; / |
范例：向雇员表中增加一条正确的数据
|
INSERT INTO emp (empno,ename,job,mgr,sal,comm,deptno,hiredate) VALUES (9999,'mldn','manager',7566,1680,null,20,SYSDATE) ; COMMIT ; |
范例：查询emp表中9999雇员信息
|
SELECT * FROM emp WHERE empno=9999 ; |
范例：向雇员表增加一条雇员信息，工资为5000（已经超过了平均工资）
|
INSERT INTO emp (empno,ename,job,mgr,sal,comm,deptno,hiredate) VALUES (8888,'lixinghua','manager',7566,5000,null,20,SYSDATE) ; |
范例：创建一张包含20部门雇员编号、姓名、职位、基本工资、部门编号、部门名称、位置的视图
|
CREATE OR REPLACE VIEW v_myview AS SELECT e.empno , e.ename , e.job , e.sal , d.deptno , d.dname , d.loc FROM emp e,dept d WHERE e.deptno=d.deptno AND d.deptno=20 ; |
范例：向视图插入一条数据
|
INSERT INTO v_myview (empno , ename, job , sal , deptno,dname,loc) VALUES (6688, '魔乐' , 'CLERK' , 2000, 50 , '教学' , '北京') ; |
范例：创建一个INSERT替代触发器，用于执行视图更新操作
|
CREATE OR REPLACE TRIGGER view_trigger INSTEAD OF INSERT ON v_myview FOR EACH ROW DECLARE v_empCount NUMBER ; v_deptCount NUMBER ; BEGIN -- 判断要增加的雇员是否存在 SELECT COUNT(empno) INTO v_empCount FROM emp WHERE empno=:new.empno ; -- 判断要增加的部门是否存在 SELECT COUNT(deptno) INTO v_deptCount FROM dept WHERE deptno=:new.deptno ; IF v_deptCount = 0 THEN -- 部门不存在 INSERT INTO dept(deptno,dname,loc) VALUES (:new.deptno , :new.dname , :new.loc) ; END IF ; IF v_empCount = 0 THEN INSERT INTO emp(empno,ename,job,sal,deptno) VALUES (:new.empno , :new.ename , :new.job , :new.sal , :new.deptno) ; END IF ; END ; / |
范例：执行视图增加操作
|
INSERT INTO v_myview (empno , ename, job , sal , deptno,dname,loc) VALUES (6688, '魔乐' , 'CLERK' , 2000, 50 , '教学' , '北京') ; COMMIT ; |
范例：查询emp表中是否存在了新增的6688记录
|
SELECT * FROM emp WHERE empno=6688 ; |
范例：查询dept表中是否存在了新增的50部门记录
|
SELECT * FROM dept WHERE deptno=50 ; |
范例：创建一个UPDATE替代触发器
|
CREATE OR REPLACE TRIGGER view_trigger INSTEAD OF UPDATE ON v_myview FOR EACH ROW BEGIN UPDATE emp SET ename=:new.empno , job=:new.job , sal=:new.sal WHERE empno=:new.empno ; UPDATE dept SET dname=:new.dname,loc=:new.loc WHERE deptno=:new.deptno ; END ; / |
范例：更新视图信息
|
UPDATE v_myview SET ename='史密思',sal=2000,dname='调研部' WHERE empno=7369 ; COMMIT ; |
范例：查询v_myview视图
|
SELECT * FROM v_myview ; |
范例：查询emp表中7369雇员信息是否被更新
|
SELECT * FROM emp WHERE empno=7369 ; |
范例：查询dept表中20部门的信息是否被更新
|
SELECT * FROM dept WHERE deptno=20 ; |
范例：创建一个DELETE替代触发器
|
CREATE OR REPLACE TRIGGER view_trigger INSTEAD OF DELETE ON v_myview FOR EACH ROW DECLARE v_empCount NUMBER ; BEGIN DELETE FROM emp WHERE empno=:old.empno ; SELECT COUNT(empno) INTO v_empCount FROM emp WHERE deptno=:old.deptno ; IF v_empCount = 0 THEN -- 此部门没有雇员 DELETE FROM dept WHERE deptno=:old.deptno ; END IF ; END ; / |
范例：删除视图之中所有20部门的雇员
|
DELETE FROM v_myview WHERE deptno=20 ; COMMIT ; |
范例：将三个不同功能的替代触发器变为一个替代触发器
|
CREATE OR REPLACE TRIGGER view_trigger INSTEAD OF INSERT OR UPDATE OR DELETE ON v_myview FOR EACH ROW DECLARE v_empCount NUMBER ; v_deptCount NUMBER ; BEGIN IF INSERTING THEN -- 判断要增加的雇员是否存在 SELECT COUNT(empno) INTO v_empCount FROM emp WHERE empno=:new.empno ; -- 判断要增加的部门是否存在 SELECT COUNT(deptno) INTO v_deptCount FROM dept WHERE deptno=:new.deptno ; IF v_deptCount = 0 THEN -- 部门不存在 INSERT INTO dept(deptno,dname,loc) VALUES (:new.deptno , :new.dname , :new.loc) ; END IF ; IF v_empCount = 0 THEN INSERT INTO emp(empno,ename,job,sal,deptno) VALUES (:new.empno , :new.ename , :new.job , :new.sal , :new.deptno) ; END IF ; ELSIF UPDATING THEN UPDATE emp SET ename=:new.empno , job=:new.job , sal=:new.sal WHERE empno=:new.empno ; UPDATE dept SET dname=:new.dname,loc=:new.loc WHERE deptno=:new.deptno ; ELSIF DELETING THEN DELETE FROM emp WHERE empno=:old.empno ; SELECT COUNT(empno) INTO v_empCount FROM emp WHERE deptno=:old.deptno ; IF v_empCount = 0 THEN -- 此部门没有雇员 DELETE FROM dept WHERE deptno=:old.deptno ; END IF ; ELSE NULL ; END IF ; END ; / |
范例：定义嵌套表
|
-- 1、定义复合类型 DROP TYPE project_nested ; DROP TYPE project_type ; CREATE OR REPLACE TYPE project_type AS OBJECT( projectid NUMBER , projectname VARCHAR2(50) , projectfunds NUMBER , pubdate DATE ) ; / -- 2、定义嵌套表类型 CREATE OR REPLACE TYPE project_nested AS TABLE OF project_type NOT NULL ; / -- 3、创建嵌套表类型的数据表 DROP TABLE department PURGE ; CREATE TABLE department ( did NUMBER , deptname VARCHAR2(50) NOT NULL , projects project_nested , CONSTRAINT pk_did PRIMARY KEY(did) ) NESTED TABLE projects STORE AS projects_nsted_table ; -- 4、增加测试数据 INSERT INTO department(did,deptname,projects) VALUES (10,'魔乐科技' , project_nested( project_type(1,'Java实战开发' , 8900 , TO_DATE('2004-09-27','yyyy-mm-dd')), project_type(2,'Android实战开发' , 13900 ,TO_DATE('2010-07-19','yyyy-mm-dd')) )) ; INSERT INTO department(did,deptname,projects) VALUES (20,'MLDN出版部' , project_nested( project_type(10,'《Java开发实战经典》' , 79.8 , TO_DATE('2008-08-13','yyyy-mm-dd')) , project_type(11,'《Java Web开发实战经典》' , 69.8 , TO_DATE('2010-08-27','yyyy-mm-dd')) , project_type(12,'《Android开发实战经典》', 88 , TO_DATE('2012-03-19','yyyy-mm-dd')) )) ; COMMIT ; |
范例：创建一张只包含10部门信息的视图，在此视图之中存在嵌套表类型projects列。
|
CREATE OR REPLACE VIEW v_department10 AS SELECT did,deptname,projects FROM department WHERE did=10 ; |
范例：查看视图中的嵌套表数据
|
SELECT * FROM TABLE (SELECT projects FROM v_department10) ; |
范例：对视图中的嵌套表执行增加数据操作 —— 错误
|
INSERT INTO TABLE (SELECT projects FROM v_department10) VALUES (3,'Java高端人才培养',8000,TO_DATE('2013-09-19','yyyy-mm-dd')) ; |
范例：对视图中的嵌套表执行修改数据操作 —— 错误
|
UPDATE TABLE (SELECT projects FROM v_department10) pro SET VALUE(pro) = project_type(2,'Android高级应用',3000,TO_DATE('2013-06-06','yyyy-mm-dd')) WHERE pro.projectid=2 ; |
范例：对视图中的嵌套表执行删除数据操作 —— 错误
|
DELETE FROM TABLE( SELECT projects FROM v_department10) pro WHERE pro.projectid=2 ; |
范例：定义替代触发器实现对视图中的嵌套表数据更新
|
CREATE OR REPLACE TRIGGER nested_trigger INSTEAD OF INSERT OR UPDATE OR DELETE ON NESTED TABLE projects OF v_department10 DECLARE BEGIN IF INSERTING THEN INSERT INTO TABLE (SELECT projects FROM department WHERE did=:parent.did) VALUES (:new.projectid,:new.projectname,:new.projectfunds,:new.pubdate) ; ELSIF UPDATING THEN UPDATE TABLE (SELECT projects FROM department WHERE did=:parent.did) pro SET VALUE(pro) = project_type(:new.projectid,:new.projectname,:new.projectfunds,:new.pubdate) WHERE pro.projectid=:old.projectid ; ELSIF DELETING THEN DELETE FROM TABLE( SELECT projects FROM department WHERE did=:parent.did) pro WHERE pro.projectid=:old.projectid ; END IF ; END ; / |
范例：禁止c##scott用户的所有DDL操作
|
CREATE OR REPLACE TRIGGER scott_forbid_trigger BEFORE DDL ON SCHEMA BEGIN RAISE_APPLICATION_ERROR(-20007,'scott用户禁止使用任何的DDL操作！') ; END ; / |
范例：创建一个序列
|
CREATE SEQUENCE mldn_seq ; |
范例：数据库对象操作日志记录表创建脚本
|
DROP TABLE object_log PURGE ; DROP SEQUENCE object_log_seq ; CREATE SEQUENCE object_log_seq ; CREATE TABLE object_log ( oid NUMBER CONSTRAINT pk_oid PRIMARY KEY, username VARCHAR2(50) NOT NULL , operatedate DATE NOT NULL , objecttype VARCHAR2(50) NOT NULL , objectowner VARCHAR2(50) NOT NULL ) ; |
范例：编写触发器实现对数据库对象操作的日志记录
|
CREATE OR REPLACE TRIGGER object_trigger AFTER CREATE OR DROP OR ALTER ON DATABASE DECLARE BEGIN INSERT INTO c##scott.object_log(oid,username,operatedate,objecttype,objectowner) VALUES (c##scott.object_log_seq.nextval,ORA_LOGIN_USER, SYSDATE,ORA_DICT_OBJ_TYPE,ORA_DICT_OBJ_OWNER) ; END ; / |
范例：禁止修改emp数据表的empno（主键）列和deptno（外键）列的定义结构
|
SELECT * FROM all_tab_columns WHERE table_name='EMP' AND owner='C##SCOTT'; |
范例：在c##scott用户中定义参数游标
|
CREATE OR REPLACE TRIGGER emp_alter_trigger BEFORE ALTER ON SCHEMA DECLARE -- 操作的所有者以及操作的表名称由外部传递 CURSOR emp_column_cur(p_tableOwner all_tab_columns.owner%TYPE , p_tableName all_tab_columns.table_name%TYPE) IS SELECT column_name FROM all_tab_columns WHERE owner=p_tableOwner AND table_name=p_tableName ; BEGIN IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN -- 如果操作的是数据表 FOR empColumnRow IN emp_column_cur(ORA_DICT_OBJ_OWNER , ORA_DICT_OBJ_NAME) LOOP IF ORA_IS_ALTER_COLUMN(empColumnRow.column_name) THEN -- empno字段要被修改 IF empColumnRow.column_name = 'EMPNO' THEN RAISE_APPLICATION_ERROR(-20007,'empno字段不允许修改！') ; END IF ; -- deptno字段要被修改 IF empColumnRow.column_name = 'DEPTNO' THEN RAISE_APPLICATION_ERROR(-20008,'deptno字段不允许修改！') ; END IF ; END IF ; IF ORA_IS_DROP_COLUMN(empColumnRow.column_name) THEN -- empno字段要被删除 IF empColumnRow.column_name = 'EMPNO' THEN RAISE_APPLICATION_ERROR(-20009,'empno字段不允许删除！') ; END IF ; -- deptno字段要被删除 IF empColumnRow.column_name = 'DEPTNO' THEN RAISE_APPLICATION_ERROR(-20010,'deptno字段不允许删除！') ; END IF ; END IF ; END LOOP ; END IF ; END ; / |
范例：修改empno字段
|
ALTER TABLE emp MODIFY (empno NUMBER(6)) ; |
范例：删除deptno字段
|
ALTER TABLE emp DROP COLUMN deptno ; |
范例：编写user_log数据表创建脚本
|
DROP SEQUENCE user_log_seq ; DROP TABLE user_log PURGE ; CREATE SEQUENCE user_log_seq ; CREATE TABLE user_log ( logid NUMBER CONSTRAINT pk_logid PRIMARY KEY , username VARCHAR2(50) NOT NULL , logondate DATE , logoffdate DATE , ip VARCHAR2(20) , logtype VARCHAR2(20) ) ; |
范例：监控用户登录触发器 —— logon_trigger
|
CREATE OR REPLACE TRIGGER logon_trigger AFTER LOGON ON DATABASE BEGIN INSERT INTO user_log(logid,username,logondate,ip,logtype) VALUES (user_log_seq.nextval,ORA_LOGIN_USER,SYSDATE,ORA_CLIENT_IP_ADDRESS,'LOGON') ; END ; / |
范例：监控用户注销触发器 —— logoff_trigger
|
CREATE OR REPLACE TRIGGER logoff_trigger BEFORE LOGOFF ON DATABASE BEGIN INSERT INTO user_log(logid,username,logoffdate,ip,logtype) VALUES (user_log_seq.nextval,ORA_LOGIN_USER,SYSDATE,ORA_CLIENT_IP_ADDRESS,'LOGOFF') ; END ; / |
范例：回到sys用户下，查询user_log数据表
|
CONN sys/change_on_install AS SYSDBA ; SELECT * FROM user_log ; |
范例：定义数据库事件记录表
|
DROP TABLE db_event_log PURGE ; DROP SEQUENCE db_event_log_seq ; CREATE SEQUENCE db_event_log_seq ; CREATE TABLE db_event_log ( eventId NUMBER CONSTRAINT pk_eventid PRIMARY KEY , eventType VARCHAR2(50) NOT NULL , eventDate DATE NOT NULL , eventUser VARCHAR2(50) NOT NULL ) ; |
范例：编写触发器 —— 启动之后触发
|
CREATE OR REPLACE TRIGGER startup_trigger AFTER STARTUP ON DATABASE BEGIN INSERT INTO db_event_log(eventId,eventType,eventDate,eventUser) VALUES (db_event_log_seq.nextval,'STARTUP',SYSDATE,ORA_LOGIN_USER) ; COMMIT ; END ; / |
范例：编写触发器 —— 关闭之前触发
|
CREATE OR REPLACE TRIGGER shutdown_trigger BEFORE SHUTDOWN ON DATABASE BEGIN INSERT INTO db_event_log(eventId,eventType,eventDate,eventUser) VALUES (db_event_log_seq.nextval,'SHUTDOWN',SYSDATE,ORA_LOGIN_USER) ; COMMIT ; END ; / |
范例：查询db_event_log数据表记录
|
SHUTDOWN ABORT STARTUP SELECT * FROM db_event_log ; |
范例：创建一张记录错误信息的数据表 —— db_error
|
DROP SEQUENCE db_error_seq ; DROP TABLE db_error PURGE ; CREATE SEQUENCE db_error_seq ; CREATE TABLE db_error ( eid NUMBER CONSTRAINT pk_eid PRIMARY KEY , username VARCHAR2(50) , errorDate DATE , dbname VARCHAR2(50) , content CLOB ) ; |
范例：定义数据库错误触发器
|
CREATE OR REPLACE TRIGGER error_trigger AFTER SERVERERROR ON DATABASE BEGIN INSERT INTO db_error(eid,username,errorDate,dbname,content) VALUES (db_error_seq.nextval,ORA_LOGIN_USER,SYSDATE,ORA_DATABASE_NAME, DBMS_UTILITY.format_error_stack) ; END ; / |
范例：使用c##scott用户登录，查看user_triggers数据字典
|
SELECT trigger_name,status,trigger_type,status,table_name,triggering_event,trigger_body FROM user_triggers ; |
范例：将emp_alter_trigger触发器修改为禁用状态
|
ALTER TRIGGER emp_alter_trigger DISABLE ; |
范例：查询数据字典表，确定触发器状态
|
SELECT trigger_name,status FROM user_triggers ; |
范例：禁用触发器代码结构
|
CREATE OR REPLACE TRIGGER emp_update_trigger BEFORE INSERT OR UPDATE OR DELETE ON dept DISABLE FOR EACH ROW BEGIN NULL ; END; / |
范例：启用emp表的全部触发器
|
ALTER TABLE emp ENABLE ALL TRIGGERS; |
范例：删除forbid_emp_trigger触发器
|
DROP TRIGGER emp_alter_trigger ; |
范例：在每月10号允许办理新近人员入职，同时入职的新雇员工资不能够超过公司的平均工资
|
CREATE OR REPLACE PROCEDURE emp_update_date_proc AS v_currentdate VARCHAR2(20) ; BEGIN SELECT TO_CHAR(SYSDATE,'dd') INTO v_currentdate FROM dual ; IF TRIM(v_currentdate)!='10' THEN RAISE_APPLICATION_ERROR(-20008,'在每月的10号才允许办理入职手续！') ; END IF ; END; / |
|
CREATE OR REPLACE FUNCTION emp_avg_sal RETURN NUMBER AS v_avg_salary emp.sal%TYPE ; BEGIN SELECT AVG(sal) INTO v_avg_salary FROM emp ; RETURN v_avg_salary ; END; / |
|
CREATE OR REPLACE TRIGGER forbid_emp_trigger BEFORE INSERT ON emp FOR EACH ROW BEGIN emp_update_date_proc ; -- 调用过程 IF :new.sal>emp_avg_sal() THEN -- 调用函数 RAISE_APPLICATION_ERROR(-20009,'新进雇员工资不得高于公司平均工资！') ; END IF ; END ; / |
