--对简单视图的操作 drop table emp1; create table emp1 as select * from emp; --简单视图 create or replace view v_emp1 as select * from emp1 ; --查询 select * from v_emp1; --更新 update v_emp1 set v_emp1.ENAME='andy' where v_emp1.JOB='CLERK'; --增加 insert into v_emp1 values (7777, 'chy', 'MANAGER', 8888, sysdate, 10000,1111.11,20); --删除 delete from v_emp1 where v_emp1.EMPNO=7777; --复杂视图、仅两基表相连、不包含各种分组函数、group by、distinct命令等。 create or replace view v_complex as select emp1.ename, emp1.job, dept.dname from emp1, dept where emp1.deptno=dept.deptno with check option ; --查询 select * from v_complex; --修改 update v_complex set v_complex.ename='andy' where v_complex.job='MANAGER'; --增加 --报错：ORA-01776：无法通过连接视图修改多个基表 insert into v_complex (v_complex.ename, v_complex.job, v_complex.dname) values ('chy', 'MANAGER', 'SALES'); --删除 delete from v_complex where v_complex.ename='chy'; --复杂视图、包含不能进行DML的元素、一般仅用与查询、可以加上 with read only; create or replace view v_complex_readonly as --对使用聚合函数的列必须使用别名！ select max(emp1.sal) max_sal from emp1, dept where emp1.deptno=dept.deptno group by dept.deptno with read only; --查询 select * from v_complex_readonly; --删除视图 create or replace view v_for_delete as select * from emp with read only; drop view v_for_delete;
版权声明：
本文来源网络，所有图片文章版权属于原作者，如有侵权，联系删除。
本文网址：https://www.mushiming.com/mjsbk/5028.html