前提:对于简单的视图,可以直接进行DML操作,但是对于复杂视图,不允许直接执行DML操作,当视图符合以下任何一种情况都不可以:
具有集合操作符(UNION,UNION ALL,INTERSECT,MINUS);
具有分组函数(MIN,MAX,SUM,AVG,COUNT);
具有GROUP BY,CONNECT BY 或START WITH子句;
具有DISTINCT关键字
具有连接查询
在具有以上情况的复杂视图执行DML操作,必须要基于视图建立INSTEAD OF触发器。
建立之后,就可以基于复杂视图执行DML语句
注意事项:
INSTEAD OF选项只适用于视图
当基于视图建立触发器时,不能指定BEFORE和AFTER选项
在建立视图时没有指定WITH CHECK OPTION选项
当建立INSTEAD OF触发器时,必须指定FOR ECH ROW选项
——又小又大型的例子:
1.建立复杂视图dept_emp
视图时逻辑表,本身没有任何数据。
视图只是对应一条SELECT语句。
当查询视图时,其数据实际是从视图基表上取得。
为了简化部门及其雇员信息的查询,应建立复杂视图dept_emp
CREATE OR REPLACE VIEW dept_emp AS
SELECT a.deptno,a.dname,b.empno,b.ename
FROM dept a,emp b
WHERE a.deptno=b.deptno;
当执行以上语句之后,直接查询视图会显示相关信息,但不允许DML操作。
2.建立INSTEAD OF触发器
为了可以在复杂视图上执行DML操作,必须要基于复杂视图来建立INSTEAD OF触发器。
下面以复杂视图dept_emp上执行INSERT 操作为例:
CREATE OR REPLACE TRIGGER tr_instead_of_dept_emp
INSTEAD OF INSERT ON dept_emp
FOR EACH ROW
DELARE
v_temp INT;
BEGIN
SELECT count(*) INTO v_temp FROM dept
WHERE deptno=:new.deptno;
IF v_temp=0 THEN
INSERT INTO dept(deptno,dname)
VALUES(:new.deptno,:new.dname);
END IF;
SELECT count(*) INTO v_temp FROM emp
WHERE empno=:new.empno;
IF v_temp=0 THEN
INSERT INTO emp(empno,ename,deptno)
VALUES(:new.empno,:new.ename,:new.deptno);
EEND IF;
END;
/
当建立了此触发器后,就可以在复杂视图上进行INSERT操作。
INSERT INTO dept_emp VALUES(50,'ADMIN','1223','MARY');
INSERT INTO dept_emp VALUES(10,'ADMIN','1224','BARK');
...
执行了以上两条数据之后,为DEPT表插入一条数据,为EMP表插入两条数据。。。